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

Hi
I have an excel sheet from which i would like to import the values into a csv..I want this to be automated(it should happen at an end of day basis)..so i just created a csv file and recorded a macro and imported the data from my excel sheet...However csv's dont have macro capability.So now basically my recorded macro is useless ...any suggestions would be really helpful.
Thanks,
Kris
kris_257Asked:
Who is Participating?
 
rowanscottConnect With a Mentor Commented:
Have a look at the attached.
Probably not exactly what you need but should give you the knowledge you need.

The macro to run is called Main

Best Regards

Rowan
WebQandCSVappend.xls
0
 
zorvek (Kevin Jones)ConsultantCommented:
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
0
 
kris_257Author Commented:
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>>??
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
zorvek (Kevin Jones)ConsultantCommented:
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
0
 
kris_257Author Commented:
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
0
 
zorvek (Kevin Jones)Connect With a Mentor ConsultantCommented:
The routine below queries the user for a save as file name and then saves the active worksheet in either tab or comma delimited format depending on the file extension.

[Begin Code Segment]

Public Sub ExportWorksheet()

' Export the active worksheet as either a tab delimited or comma delimited
' file without the Excel warning dialogs and without changing the workbook
' format or saved location.

   Dim FilePath As String
   ActiveSheet.Copy
   FilePath = Application.GetSaveAsFilename(FileFilter:="Text (Tab delimited) (*.txt),*.txt,CSV (Comma delimited) (*.csv),*.csv")
   If FilePath = "False" Then Exit Sub
   ActiveWorkbook.SaveAs FilePath, IIf(UCase(Right(FilePath, 4)) = ".TXT", xlTextWindows, xlCSV)
   ActiveWorkbook.Close False

End Sub

[End Code Segment]

If the file path is already known then change:

   FilePath = Application.GetSaveAsFilename(FileFilter:="Text (Tab delimited) (*.txt),*.txt,CSV (Comma delimited) (*.csv),*.csv")

to:

   FilePath = "c:\full\path\to\file.txt"

Kevin
0
 
OP_ZaharinCommented:
what excel version are you using?
0
 
OP_ZaharinCommented:
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
0
 
kris_257Author Commented:
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.
0
 
rowanscottCommented:
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
0
 
kris_257Author Commented:
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
0
 
rowanscottCommented:
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
0
 
rowanscottCommented:
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
0
 
kris_257Author Commented:
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
0
 
kris_257Author Commented:
thanks to all of you for helping me out
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.