?
Solved

export from vb 6.0 to excel with a timer

Posted on 2003-04-01
21
Medium Priority
?
3,150 Views
Last Modified: 2010-05-18
i am trying to set an archive of my database. i know that i have to include the timer so that every say 30days ill have all the data in the MsAcess will go into excel and save the folder in a folder in the whole system automatically.

is there a way i can do that?

regards
Naomi
0
Comment
Question by:grimaenriqueznaomi
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 6
  • 3
  • +3
21 Comments
 
LVL 2

Expert Comment

by:WintersVine
ID: 8245537
perhaps you could assign a program to your start up routine that checks to see if its been more than 30 days since you last did your back up (i.e. you store a date value) that way the program doesn't need to be in memory all of the time. As long as you are rebooting your computer at least once a month this would work fine.

If you need a code example let me know. . .
-David
0
 
LVL 3

Expert Comment

by:aflat362
ID: 8245544
Why not write a program to do this and just schedule it on windows scheduler?
0
 

Author Comment

by:grimaenriqueznaomi
ID: 8245634
wintersVine,
10x for your comment, this system has to run 24/7 since it is a telephone system that records all the data in the database via a pabx.....i would appreciate some examples though since i am pretty new to vb...
thanking you for your kind co-operation
regards Naomi
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 4

Expert Comment

by:iozturk
ID: 8245697
Dont write a program that consumes memory and CPU as  aflat362  says just schedule it by windows scheduler.
0
 
LVL 4

Expert Comment

by:iozturk
ID: 8245728
Running the program when starting up I think is not a good solution. Windows does not run for 30 days without rebooting:) So every time you restart the comp. Your program runs.
0
 
LVL 27

Expert Comment

by:Dabas
ID: 8245787
Hi grimaenriqueznaomi

I join my voice to those that recommend the Task Scheduler that comes with Windows

Dabas
0
 

Author Comment

by:grimaenriqueznaomi
ID: 8245806
okie sure, has anyone got a clue how to do it since im new to vb 6.0.....i would appreciate some examples re task scheduler :) i thank you all for your nice co-operation
regards
Naomi
0
 
LVL 3

Expert Comment

by:aflat362
ID: 8245832
Here's why I suggested Windows Scheduler (and why iozturk, Dabas back me up)

1) why put all the effort into coding / debugging a timer when its built in to windows already?

2) You don't need to have this functionality built in to your other program that runs 24 / 7.  That's the beauty of databases.  Multiple programs can access them simultaneously unless you choose to lock the database by one app.

3) Resource Hog?  Depending on how efficient your timer is it could be a big resource hog on your system - cpu / memory

4) Code instability.  You said you are a novice programmer.  Timers aren't cut and dry.  It is an advanced programming technique and will be prone to difficulties.
0
 
LVL 27

Expert Comment

by:Dabas
ID: 8245841
Naomi:

This is a two step process:

1) Writing a program that will do your archiving.

2) Setting up Task scheduler to run 1) once a month.

You do not need VB for 2). It is a Windows utility.

Please let us know which version of Windows you are using, so that we can tell you how to set it up

Dabas
0
 

Author Comment

by:grimaenriqueznaomi
ID: 8245873
i use windows 98 but this program will be run on xp too! what shall i do?
i thank you once again guys :)
0
 
LVL 4

Expert Comment

by:iozturk
ID: 8245962
No problem if you can sit on XP computer.
Start->programs->Accessories->System tools->Scheduled Tasks
When you start there is a easy to use GUI. Even first users cannot make mistake:)

I did not use 98 much but I know it has a scheduler. Most porbably it was in my computer or in mycomputer tool bars. I dont remember if you should choose to install it when installation.
0
 

Author Comment

by:grimaenriqueznaomi
ID: 8246604
yeah okie, i think i figured how to do the scheduler, but im sure to run the program you have to find a way in coding (vb)to tell it to put the data in access in excel and to use different sheets for every month....is there a way to do that?
have u got an example if so please? regards Naomi
0
 
LVL 4

Expert Comment

by:iozturk
ID: 8246920
Sure, add a sheet in every time of your program run. Since it runs one per mounth for each mounth you will have unique sheet.

I think you have also problem with exporting data from Access to Excel.
Look at the code below there may be typos in it and reference Microsoft Excel Object library.



Dim strcnn As String
Dim cnn1 As ADODB.Connection
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet

Set xlApp = New Excel.Application
Set xlBook = xlApp.Workbooks.Add
Set xlSheet = xlBook.Worksheets(1)
   
strcnn = "data source=argon_web1; user id=proje; password=rop"  'dont forget to change this

Set cnn1 = New ADODB.Connection
cnn1.Open strcnn

sql1 = "select * from sinif_ders order by 1,2" ''dont forget to change this


Set rs = New ADODB.Recordset
rs.Open sql1, cnn1, , , adCmdText

xlApp.Visible = True
i=1
j=1
do while not rs.EOF    
     If ders_kodu = 0 Then
        xlSheet.cells(i, j).Value = ""
     Else
        xlSheet.cells(i, j).Value = rs(0)
     End If
     i=i+1
     rs.movenext
loop

rs.close
set rs=nothing
cnn1.close
set cnn1=nothing
0
 
LVL 27

Expert Comment

by:Dabas
ID: 8249414
An other alternative, which would require no programming at all is to follow these steps (assuming your Access is 2000 or later.)
Open up a new workbook in Excel, then click on Data, Get External Data, New Database query.
You are prompted for a new data source. Choose MS Access Database, and click OK.
Select your .mdb and then the table within that mdb, and the columns you want to import.
Follow the prompts of the wizard and Return the data to Excel.
Save the workbook twice:
Once to save the current months information.
Second time as a template to call from task scheduler.\
All you need now is to write a macro that will refresh the data in the spreadsheet and save it under the new months name.
(Recording the macro will do most of the coding for you)

Dabas


0
 

Author Comment

by:grimaenriqueznaomi
ID: 8251926
Dear iozturk

I did something like that before, but i need something to trigger the deed....i want this excel operation to be done automatically.  i know there is the scheduler, but i think you need an  .exe to select task right?

thanx for your good support!:)

Private Sub cmdExportO_Click()
Dim rs As ADODB.Recordset
Dim con As ADODB.connection
Dim xl As Excel.Application
Dim wb As Excel.Workbook
Dim ws As Excel.Worksheet
Dim sql As String
Dim FreeNumber As Integer
Dim Choice As String

'Choice = "data source = Telephone;user id = password"

   Set con = New ADODB.connection
   con.Open "provider=Microsoft.Jet.OLEDB.4.0; Data Source = " & AppPath & "telephone.mdb"


sql = "SELECT * FROM telephoneO" & _
      " WHERE index   "
     
   FreeNumber = 0
     
   
   Set rs = New ADODB.Recordset
   rs.Open sql, con, adOpenDynamic, adLockPessimistic
       
       
   Set xl = CreateObject("Excel.Application")
 

FreeNumber = FreeFile

xl.Visible = True
lvwresults2.ListItems.Add
If Not rs.EOF Then
 rs.MoveFirst
  Open "C:\results1.xls" For Output As #FreeNumber
  Print #FreeNumber, "CallType" & vbTab & "Date" & vbTab & "Time" & vbTab & "Duration" & vbTab & "ExtCode" & vbTab & "DialoutNumber" & vbTab & "TelephoneNumber" & vbTab & "Pulses" & vbTab & "TCode" & vbTab & "EOR"
 With rs
  If Not rs.EOF Then
  While Not rs.EOF
 Print #FreeNumber, rs("OCallType") & vbTab & rs("ODate") & vbTab & rs("OTime") & vbTab & rs("ODuration") & vbTab & rs("OExtCode") & vbTab & rs("ODialoutNumber") & vbTab & rs("OTelephoneNumber") & vbTab & rs("OPulses") & vbTab & rs("OTCode") & vbTab & rs("OEOR")
 Row = Row + 1
 rs.MoveNext
 Wend
 Close #FreeNumber
 xl.Workbooks.Open "C:\results1.xls"
End If
    Set ws = Nothing
    Set wb = Nothing
    Set xl = Nothing
    Set rs = Nothing
    Set con = Nothing
End With
End If
End Sub
0
 

Author Comment

by:grimaenriqueznaomi
ID: 8251936
Dear Dabas,

i also thank you for your kind reply...but unfortunately i cannot find office 2000 to install the new database query!
is there another way to go around it?
thanx and regards
Naomi
0
 
LVL 4

Expert Comment

by:iozturk
ID: 8252236
Dear Naomi,
Arent you using VB? Make it exe and schedule the exe.
If you are not using VB the you are in somehow trouble. Try making it in VBS (VB script) and schedule VBS. Here is another code sample for vbs ofcourse you should change it to fit you. Save it in a text file and make its extention vbs. (i.e sample.vbs)


Dim objXL
Dim rssorgu
Set objXL = WScript.CreateObject("Excel.Application")

Set cnn1 = CreateObject("ADODB.Connection")
strcnn = "data source=TARKOP; user id=HELPDESK; password=ORTEGA"
cnn1.Open strcnn

sql = "select substr(x.kurumno,4) KURUMNO, x.kurumadi, " & _
         "substr(y.kurumno,2,2) ustkurum, y.kurumadi USTKURUMADI, " & _
         "z.iladi, t.ilceadi, z.telkodu, " & _  
         "x.telno1, x.telno2, x.faxno, x.modem " & _
      "from kurumlar x, kurumlar y, iller z, ilceler t " & _
      "where x.ustkurum = y.kurumno and x.ilkodu=z.ilkodu and " & _
          "x.ilkodu=t.ilkodu and x.ilcekodu=t.ilcekodu"

set rssorgu = CreateObject("ADODB.Recordset")
rssorgu.Open sql, cnn1, 0, 1, &H0001

objXL.Visible = TRUE

objXL.WorkBooks.Add

objXL.Columns(1).ColumnWidth = 5
objXL.Columns(2).ColumnWidth = 20
objXL.Columns(3).ColumnWidth = 5
objXL.Columns(4).ColumnWidth = 20
objXL.Columns(5).ColumnWidth = 20
objXL.Columns(6).ColumnWidth = 20
objXL.Columns(7).ColumnWidth = 4
objXL.Columns(8).ColumnWidth = 8
objXL.Columns(9).ColumnWidth = 8
objXL.Columns(10).ColumnWidth = 8
objXL.Columns(11).ColumnWidth = 8


objXL.Cells(1, 1).Value = "KURUMNO"
objXL.Cells(1, 2).Value = "KURUMADI"
objXL.Cells(1, 3).Value = "USTKURUM"
objXL.Cells(1, 4).Value = "USTKURUMADI"
objXL.Cells(1, 5).Value = "ÝL"
objXL.Cells(1, 6).Value = "ÝLÇE"
objXL.Cells(1, 7).Value = "TELEFONKODU"
objXL.Cells(1, 8).Value = "TEL1"
objXL.Cells(1, 9).Value = "TEL2"
objXL.Cells(1, 10).Value = "FAX"
objXL.Cells(1, 11).Value = "MODEM"


i=2
do while not rssorgu.Eof
   objXL.Cells(i, 1).Value = rssorgu("KURUMNO")
   objXL.Cells(i, 2).Value = rssorgu("KURUMADI")  
   objXL.Cells(i, 3).Value = rssorgu("USTKURUM")
   objXL.Cells(i, 4).Value = rssorgu("USTKURUMADI")      
   objXL.Cells(i, 5).Value = rssorgu("ILADI")  
   objXL.Cells(i, 6).Value = rssorgu("ILCEADI")
   objXL.Cells(i, 7).Value = rssorgu("TELKODU")
   objXL.Cells(i, 8).Value = rssorgu("TELNO1")  
   objXL.Cells(i, 9).Value = rssorgu("TELNO2")  
   objXL.Cells(i, 10).Value = rssorgu("FAXNO")  
   objXL.Cells(i, 11).Value = rssorgu("MODEM")  

   rssorgu.movenext
   i = i +1
loop
   
rssorgu.close
set rssorgu = nothing
cnn1.close
set cnn1 = nothing

msgbox("bitti")


0
 

Author Comment

by:grimaenriqueznaomi
ID: 8252495
yeah i am using visual baisc 6.0....but if i run this program, i run the whole system....i want it that when the scheduler indicates the task, it will automatically put the data in excel....i am sure that there has to be some kind of coding to trigger this act....and to change it to exe i won't change just the excel, i got to do the whole program....not to forget this will be on for 24/7,there has to be some coding saying that if the scheduler triggers the act every 31days it will have to export the stuff to excel....there is where i am stuck as such....am sorry for asking lots of questions but ....ive only been practicing vb for the past 2wks so i need extra help and i don't know any other language...thanking you for your kind help
regards
Naomi
0
 
LVL 4

Accepted Solution

by:
iozturk earned 240 total points
ID: 8252643
Hi Naomi,

1. Delete forms from your application (Dont forget to make a copy of your code.)
2. Add a module to your app, add a procedure named Main in your module. Write your code in Main proc.
3. From project properties select start object as Sub Main
4. Make you application exe with VB6.
6. Schedule yoour exe file.

Now Scheduler triggers your application to start running. And when application starts it invokes the main procedure and your main proc. does what you want.
0
 

Author Comment

by:grimaenriqueznaomi
ID: 8253154
i thank you all for such a support
wish you all a great day
regards Naomi
0
 

Expert Comment

by:computer2007
ID: 13743621
i think u can do this in easy way put a value in registry and also that value in a file now. initilised it to0 when u create  insert the reacord in database now use  now in every day u incrase the value with 1. if it comes to 30 then  transfer it to excel
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Suggested Courses
Course of the Month13 days, 16 hours left to enroll

801 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question