We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

How can I get Excel to programmatically write to CSV?

Medium Priority
418 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?
Comment
Watch Question

Bill PrewTest your restores, not your backups...
CERTIFIED EXPERT
Expert of the Year 2019
Top Expert 2016

Commented:
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

Author

Commented:
That's correct.  It's a single sheet with about 10 rows and a dozen columns.
Mechanical Engineer
CERTIFIED EXPERT
Most Valuable Expert 2013
Top Expert 2013
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview

Author

Commented:
I'm getting the following error

Method 'Range' of object '_Worksheet' failed
byundtMechanical Engineer
CERTIFIED EXPERT
Most Valuable Expert 2013
Top Expert 2013

Commented:
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

Author

Commented:
In 2007 I'm getting a further error

Expected function or variable

around ws.Copy

Is that a new function?
byundtMechanical Engineer
CERTIFIED EXPERT
Most Valuable Expert 2013
Top Expert 2013

Commented:
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

Author

Commented:
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?
byundtMechanical Engineer
CERTIFIED EXPERT
Most Valuable Expert 2013
Top Expert 2013

Commented:
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

Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.