Link to home
Start Free TrialLog in
Avatar of wordswithfriends
wordswithfriends

asked on

How can I get Excel to programmatically write to CSV?

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?
Avatar of Bill Prew
Bill Prew

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
Avatar of wordswithfriends

ASKER

That's correct.  It's a single sheet with about 10 rows and a dozen columns.
ASKER CERTIFIED SOLUTION
Avatar of byundt
byundt
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I'm getting the following error

Method 'Range' of object '_Worksheet' failed
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
In 2007 I'm getting a further error

Expected function or variable

around ws.Copy

Is that a new function?
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
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?
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