Query Automation

[dkauf]

I recently implemented a new accounting system at my company that has import automation capabilities. This automated import will be used to retrieve data that comes out of another database and into Excel. I use Excel to query against the DB to pull in the required info and do a few small calculations. The query utilizes input cells (a date) to know what parameters to use when pulling the data.

I'd like to be able to automate the query to update the date parameters and save the queried data results into a new file (CSV) so that I can then point the accounting software to a specified location to pull the data. Ideally, this would run automatically every night and I would load it onto our SQL server.

Is this acheiveable?

 

[Ruddles]

It certainly is but it will require some knowledge of VBA (Visual Basic for Applications).

Typically you would open an Excel workbook at a set time every night using Windows Task Scheduler, a macro would run automatically when the workbook opens and that would execute the database query and save the results in a CSV file.

 

[dkauf]

I have the query set to auto-refresh upon opening the file. I guess I just need some assistance with the VBA code to tell the file to SaveAs .CSV (without all the stupid prompt messages that go long with CSV files). Is there some easy VBA to do this? Ideally, the file name would be constant and overwrite the previous CSV file created on the prior day.

Thanks!

 

[Ruddles]

<deleted>

 

[Ruddles]

The problem is that when you save the file in CSV format, the name of the active file changes. I think you need to do something like this:-

[FONT=Courier New]Sub SaveAsCSV()[/FONT]
 
[FONT=Courier New]  Const sCSVfolder As String = "C:\Temp\"[/FONT]
[FONT=Courier New]  Const sCSVfile As String = "temp.csv"[/FONT]
 
[FONT=Courier New]  Dim sFilename As String[/FONT]
 
[FONT=Courier New]  sFilename = ThisWorkbook.FullName [COLOR=green]' save workbook name so we can re-open it later[/COLOR][/FONT]
 
[FONT=Courier New]  If Dir(sCSVfolder & sCSVfile) <> "" Then Kill sCSVfolder & sCSVfile[/FONT]
[FONT=Courier New]  ActiveWorkbook.SaveAs Filename:=sCSVfolder & sCSVfile, FileFormat:=xlCSV, CreateBackup:=False[/FONT]
 
[FONT=Courier New]  Workbooks.Open sFilename [COLOR=green]' re-open xlsm workbook[/COLOR][/FONT]
[FONT=Courier New]  MsgBox "Done!"[/FONT]
 
[FONT=Courier New]  Workbooks(sCSVfile).Close SaveChanges:=False [COLOR=green]' this will terminate script    [/COLOR][/FONT]
 
[FONT=Courier New]End Sub[/FONT]

Alternatively you could write code to carry out the 'saving as CSV' using file i/o commands but try the above code first and see if it will do.