[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

How can I get Excel to programmatically write to CSV?

Posted on 2011-04-19
9
Medium Priority
?
410 Views
Last Modified: 2012-05-11
At the moment my spreadsheet reads financial data. I would like to programmatically dump this to CSV every second. How can I do this in VBA?
0
Comment
Question by:wordswithfriends
  • 4
  • 4
9 Comments
 
LVL 59

Expert Comment

by:Bill Prew
ID: 35429071
Do you mean you want to rewrite the entire CSV file with a fresh copy from the Excel data every second, that seems pretty rapid?  How big is the data to be written, and where is it stored (single sheet, what format, etc).

~bp
0
 

Author Comment

by:wordswithfriends
ID: 35429082
That's correct.  It's a single sheet with about 10 rows and a dozen columns.
0
 
LVL 81

Accepted Solution

by:
byundt earned 2000 total points
ID: 35429193
Here is a macro that will export the active worksheet to a new workbook and save it with a predefined name as a .CSV file. It will do this every second until a named range SaveCSV becomes False. This precaution is important because you can't move fast enough otherwise to stop the macro should you need to.
Sub SaveToCSV()
Dim ws As Worksheet
Dim wb As Workbook
Set ws = ActiveSheet
Application.DisplayAlerts = False
Application.ScreenUpdating = False
ws.Copy
Set wb = ActiveWorkbook
wb.SaveAs "C:\VBA\Sample '11\Test.CSV", FileFormat:=xlCSV
wb.Close SaveChanges:=False
Application.ScreenUpdating = True
Application.DisplayAlerts = True
If ws.Range("SaveCSV") = True Then Application.OnTime Now + 1 / 86400, "SaveToCSV"
End Sub

Open in new window


You may find it convenient to set SaveCSV to False using a Workbook_BeforeClose event sub.

Brad
CSVTest.xlsm
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:wordswithfriends
ID: 35429802
I'm getting the following error

Method 'Range' of object '_Worksheet' failed
0
 
LVL 81

Expert Comment

by:byundt
ID: 35429835
Did you create a named range SaveCSV in the workbook containing the macro? If not, then statement 13 will fail with the type of error you describe. In the sample workbook, SaveCSV is the cell containing the TRUE or FALSE dropdown.

Brad
0
 

Author Comment

by:wordswithfriends
ID: 35429861
In 2007 I'm getting a further error

Expected function or variable

around ws.Copy

Is that a new function?
0
 
LVL 81

Expert Comment

by:byundt
ID: 35429936
The code in the sample workbook was an earlier version--I must not have saved the file after making the last debugging change. The code I pasted in the Comment is correct. The difference is splitting the statement setting the workbook variable from the one that copied the worksheet.

ws.Copy           'Correct approach using two statements
Set wb = ActiveWorkbook

Open in new window


Set wb = ws.Copy       'Incorrect approach using a single statement

Open in new window


I have a copy of Excel 2007 at home, and have tested it successfully.

CSVTest.xlsm
0
 

Author Comment

by:wordswithfriends
ID: 35432176
Thanks for that it works.  However it does this crazy flickering in the taskbar as it continually opens and closes the worksheet.  Is there any way to stop this from happening?
0
 
LVL 81

Expert Comment

by:byundt
ID: 35432233
Try turning off the ShowWindowsInTaskbar setting when opening the workbook, then restoring it before closing the workbook.

'This code must be installed in ThisWorkbook code pane. It will not work at all if installed anywhere else.
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.ShowWindowsInTaskbar = True
End Sub

Private Sub Workbook_Open()
Application.ShowWindowsInTaskbar = False
End Sub

Open in new window

0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

With User Account Control (UAC) enabled in Windows 7, one needs to open an elevated Command Prompt in order to run scripts under administrative privileges. Although the elevated Command Prompt accomplishes the task, the question How to run as script…
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

834 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