?
Solved

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

Posted on 2011-03-17
15
Medium Priority
?
676 Views
Last Modified: 2013-11-25
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
0
Comment
Question by:kris_257
[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
  • 6
  • 4
  • 3
  • +1
15 Comments
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 35163147
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
 

Author Comment

by:kris_257
ID: 35163305
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
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 35163973
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:kris_257
ID: 35163996
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
 
LVL 81

Assisted Solution

by:zorvek (Kevin Jones)
zorvek (Kevin Jones) earned 600 total points
ID: 35164005
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
 
LVL 23

Expert Comment

by:OP_Zaharin
ID: 35164032
what excel version are you using?
0
 
LVL 23

Expert Comment

by:OP_Zaharin
ID: 35164125
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
 

Author Comment

by:kris_257
ID: 35164297
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
 
LVL 4

Expert Comment

by:rowanscott
ID: 35164676
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
 

Author Comment

by:kris_257
ID: 35178253
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
 
LVL 4

Expert Comment

by:rowanscott
ID: 35178450
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
 
LVL 4

Expert Comment

by:rowanscott
ID: 35178507
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
 

Author Comment

by:kris_257
ID: 35178651
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
 
LVL 4

Accepted Solution

by:
rowanscott earned 900 total points
ID: 35179294
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
 

Author Closing Comment

by:kris_257
ID: 35196173
thanks to all of you for helping me out
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This article describes a serious pitfall that can happen when deleting shapes using VBA.
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…
Simple Linear Regression

764 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