how to import data from excel to a csv automatically via a macro

I would maintain an xls workbook that does the work and generates the CSV file.

Can you give some more information as to the process and who uses/consumes the data?

Kevin

actually im pulling data in my xls via a web query.This gets updated on a daily basis.so basically i want to move  all this data to a csv and as and when my xls pulls the daily data off the web i want it to be reflected in a csv...so how should i go about this>>??

Keep everything in the xls workbook with the macros. When the daily pull is done, export the CSV file using VBA at that time.

Kevin

hey kevin...the main problem im facing here is that the export to the csv should be automated....So how do i go about that..could you help me out with a few snippets of code...
thanks,
kris

what excel version are you using?

this is a vbs script that runs the conversion from xls to csv from command prompt. however this doesn't work with the new .xlsx format but if you found other command line method that works as desired, you can set a timer to run automatically everyday by using Windows Task Scheduler.

1- open a notepad
2- copy-paste the following code:
Dim oExcel
Set oExcel = CreateObject("Excel.Application")
Dim oBook
Set oBook = oExcel.Workbooks.Open(Wscript.Arguments.Item(0))
oBook.SaveAs WScript.Arguments.Item(1), 6
oBook.Close False
oExcel.Quit
WScript.Echo "Excel Document Successfully Converted"

3- save the file as convxls.vbs (choose Save as type = All Files (*.*)
4- try the script manually - go to command prompt and to the foler you created the convxls.vbs files
- assuming the convxls.vbs and a sample excel file called book1.xls is in C:\Temp folder
- run C:\TEMP> convxls c:\temp\book1.xls c:\temp\book1.csv
5- if this script works, you can create a Task Scheduler (Start Menu-All Programs-Accessories-System Tools-Task Scheduler) and set to run this script automatically at your desired time everyday

hi kevin,
Thanks a lot..your solution works..not too sure about how well it will get automated though..will figure that out...will get back to you if i have any other problems.thanks a ton.

Hi

Not sure if you still need help on this.

The web query gets updated on a daily basis you say. Whatever triggers this update you want to carry on and save the csv as far as i can understand.

I have attached a sample which runs as the workbook opens. I have put a debug.assert line in so that the code will break as you open it and you can step through though its very simple.

Best regards

Rowan




demo.xlsm

hi everyone thanks for ur help...i just need a slight modification though..my xls sheet currently has historical data which gets updated on a daily basis...now i import all this data to my csv using the routines u guys gave me...now since i have all the historical data on my csv i would like to clear my xls .the xls now will get updated daily with the data tht im pulling of the web query...i would like to append this daily data to the existing data in my csv...this way my csv becomes the database and i dont have to store all the data in  my xls and csv...basically i want to append data to my csv..hope you guys got my question...
Regards,
Kris

the crux of what you need is this i think

Dim FileNum As Integer, strMessage as String

    FileNum = FreeFile ' next file number
    Open LogFileName For Append As #FileNum ' creates the file if it doesn't exist
    Print #FileNum, strMessage ' write information at the end of the text file
    Close #FileNum ' close the file
End Sub

I use something similar in a multi-user system at work to give a log of anything i want to watch in my code to see if i can improve performance.

but you need to create the string to write first.

best regards

Hi Kris

If you post a sample workbook i could do a demo of creating the csv string as well.
You would not have to do the whole sheet. You could export just what you want.

Best Regards

hi scott,
I just attached a sample workbook ..it contains historical data which gets updated on a daily basis...basically all the values in my xls should get copied to the csv....once that happens..ill clear my xls...my xls will keep pulling data(one row of data) on a daily basis..so at the end of each day my xls will have one row of data which should get appended to the existing data in the csv...hope im clear in describing the problem.
Thanks.
Kris
Book8.xls

thanks to all of you for helping me out