Excel into CSV VBA

Question

I am having trouble saving one sheet from my workbook into a CSV file. I have 18 sheets in the one workbook. Every time I run the macro, it saves a different sheet. I also need it so the display alerts do not pop up. I am a beginner to VBA and running macros, so any help would be appreciated.

   Sub csvfile()
 '
 ' csvfile Macro
 '

 '

   ChDir "C:\Users\RM\Documents"
   ActiveWorkbook.SaveAs Filename:= _
    "C:\Users\RM\Documents\Working_Program\PSSE_Export_Data.csv", FileFormat:= _
    xlCSV, CreateBackup:=False
    Application.DisplayAlerts = False





End Sub

Application.DisplayAlerts = False needs to go before the .SaveAs, not after. — CLR
– CLR, Commented Jul 21, 2017 at 14:18
I believe ActiveWorkbook.SaveAs will save the current active sheet. So make sure you've activated the sheet to save first. — CLR
– CLR, Commented Jul 21, 2017 at 14:20
You can start by putting DisplayAlerts = False before the operation that might display the alerts instead of after. That's common sense. You can't shut the barn door after the horse has run off and expect it to magically put the horse back in the stall, can you? — Ken White
– Ken White, Commented Jul 21, 2017 at 14:22
A CSV file is a plain text file, so it cannot hold data of more than one sheet. Without testing, I would assume that Excel saves only the sheet that is currently visible when saving as CSV — FunThomas
– FunThomas, Commented Jul 21, 2017 at 14:27
Have you looked into copying the active sheet to a new workbook, then saving the new workbook as CSV, as CSV will only have one, unformatted sheet? This would allowyou to run the macro once, for all sheets. — Cyril
– Cyril, Commented Jul 21, 2017 at 14:28

wrslphil · Accepted Answer · 2017-07-21 14:33:02Z

1

This should do the trick, Just specify the sheet that you want to save in place of "Sheet1"

Sub csvfile()

Application.DisplayAlerts = False
Dim ws As Worksheet
Set ws = Worksheets("Sheet1")
ws.SaveAs Filename:= _
"C:\Users\RM\Documents\Working_Program\PSSE_Export_Data.csv", FileFormat:= _
xlCSV, CreateBackup:=False

End Sub

answered Jul 21, 2017 at 14:33

wrslphil

2581 silver badge10 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Mark Over a year ago

actually I receieved an error, runtime error. and when I debugged it, this was highlighted:

ws.SaveAs Filename:= _ "C:\Users\RM\Documents\Working_Program\PSSE_Export_Data.csv", FileFormat:= _ xlCSV, CreateBackup:=False

Mark Over a year ago

never mind, I saved it under wrong directory. Also, I had a question about running a macro while that current CSV is open. I always receive an error. Is there a way to run that program while the CSV file is open. Just curious. The macro code works only when the CSV file you are creating is not open. Let's say I have to update information, instead of creating a new CSV file, is there a way to save that current one.

wrslphil Over a year ago

If the file is open you will always receive a runtime error. The only thing you could really do is to save it as another file name if you receive that particular runtime error. What would be the need for this functionality out of curiosity?... just wondering if there are any other solutions I could suggest

Mark Over a year ago

My goal is to rewrite and replace the csv file. If for example, numbers were to change on that sheet. This VBA would automatically replace and save new data. Instead of me consistently having to save it my self, and having the two messages that pop when saving a CSV file. If there is a more efficient way, I am all ears.

wrslphil Over a year ago

Hi Mark, Just a suggestion and don't know if it will suit your needs, but it sounds like you are having trouble as people can be using the csv file whilst you are trying to edit/ resave the file. You could look to programmatically set the csv file to read only, and then people can only read the data however you can save over it at your will.

|

Collectives™ on Stack Overflow

Excel into CSV VBA

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related