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?
ASKER
That's correct. It's a single sheet with about 10 rows and a dozen columns.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I'm getting the following error
Method 'Range' of object '_Worksheet' failed
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
Brad
ASKER
In 2007 I'm getting a further error
Expected function or variable
around ws.Copy
Is that a new function?
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.
I have a copy of Excel 2007 at home, and have tested it successfully.
CSVTest.xlsm
ws.Copy 'Correct approach using two statements
Set wb = ActiveWorkbook
Set wb = ws.Copy 'Incorrect approach using a single statement
I have a copy of Excel 2007 at home, and have tested it successfully.
CSVTest.xlsm
ASKER
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
~bp