Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


export from vb 6.0 to excel with a timer

Posted on 2003-04-01
Medium Priority
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?

Question by:grimaenriqueznaomi
  • 8
  • 6
  • 3
  • +3

Expert Comment

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. . .

Expert Comment

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

Author Comment

ID: 8245634
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.


Expert Comment

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

Expert Comment

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.
LVL 27

Expert Comment

ID: 8245787
Hi grimaenriqueznaomi

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


Author Comment

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

Expert Comment

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.
LVL 27

Expert Comment

ID: 8245841

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


Author Comment

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 :)

Expert Comment

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.

Author Comment

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

Expert Comment

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
do while not rs.EOF    
     If ders_kodu = 0 Then
        xlSheet.cells(i, j).Value = ""
        xlSheet.cells(i, j).Value = rs(0)
     End If

set rs=nothing
set cnn1=nothing
LVL 27

Expert Comment

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)



Author Comment

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
If Not rs.EOF Then
  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
 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

Author Comment

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

Expert Comment

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.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"

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")  

   i = i +1
set rssorgu = nothing
set cnn1 = nothing



Author Comment

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

Accepted Solution

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.

Author Comment

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

Expert Comment

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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Suggested Courses
Course of the Month14 days, 18 hours left to enroll

577 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