VBA - Saving as a .CSV Help

[sanantonio]

Hi.

Have a code I've been using for a while. But 2 issues:

Sub save()

Application.DisplayAlerts = False
On Error Resume Next
 
     Sheets("CSV").Visible = True
Sheets("CSV").Select
    Range("B34").Select
    Sheets("Order Upload").Select
    Range("Table3[Copy all lines as of A3]").Select
    Selection.Copy
    Sheets("CSV").Select
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

Dim theNewWorkbook As Workbook
Dim currentWorkbook As Workbook

Application.EnableEvents = False

'currentWorkbook is the source workbook, create a new workbook referencing to it with theNewWorkbook
Set currentWorkbook = ActiveWorkbook
Set theNewWorkbook = Workbooks.Add

'do the copy (it's better to check if there is already a 'Worksheet 1' in the new workbook. It it exists delete it or rename it
currentWorkbook.Worksheets("CSV").Copy before:=theNewWorkbook.Sheets(1)

'Remove default sheets in order to have only the copied sheet inside the new workbook
Application.DisplayAlerts = False
Dim i As Integer
For i = theNewWorkbook.Sheets.Count To 2 Step -1
    theNewWorkbook.Sheets(i).Delete
Next i
Application.DisplayAlerts = True

'Save File as XLSM
saveLocation = "K:\filepath\test" & ".csv"
theNewWorkbook.SaveAs Filename:=saveLocation, FileFormat:=xlCSV
theNewWorkbook.Close
     Sheets("CSV").Visible = False
     Sheets("Control").Select
End Sub

It basically copies some data from 1 location to a fresh tab, and then saves that fresh tab as a .csv.

Two parts don't work:

Application.DisplayAlerts = False

When saving as the same file the "Are you sure as this file already exists" error will still appear.

And

saveLocation = "K:\filepath\test" & ".csv"

I want to save it as a cell reference, rather than as test.csv

So if cell A1= "=concat("Order Upload ",text(today(),"MMDDYY")"

Then I would want the file to save as A1 rather than test. I've tried

& Range.("A1")

but this doesn't work. Any ideas team?

 

[Joe4]

For the first item, you are turning Alerts back on BEFORE you get to that line, i.e.

Application.DisplayAlerts = False
Dim i As Integer
For i = theNewWorkbook.Sheets.Count To 2 Step -1
    theNewWorkbook.Sheets(i).Delete
Next i
Application.DisplayAlerts = True

'Save File as XLSM
saveLocation = "K:\filepath\test" & ".csv"
theNewWorkbook.SaveAs Filename:=saveLocation, FileFormat:=xlCSV

and for the second, why do you have a period between "Range" and the cell address?
That is not valid syntax.
Instead of:

& Range.("A1")

it should be:

& Range("A1")

 

[sanantonio]

haha. How did I not spot I was turning the notifications back on! Thanks!

As for the range reference, my code now looks like:

saveLocation = "K:\Reformatter\Output\" & Range("A1") & ".csv"

The code in the A1 means the text reads: US05_032423

But it doesn't save?

 

[Joe4]

You may want to enable all events for debugging purposes while you try to figure out what is going on.
Then step through your code one line at a time using the F8 key while you watch what happens to your files.
Many times, the issue becomes obvious when you can see what is happening each step along the way.

 

[sanantonio]

It was a good shout, but I go thru step by step and when on that line it just does nothing, then executes the close instruction. It works fine when using a name based on the static name listed in the code, but refuses to say as anything that is a cell reference.

Anything else I can try? Is there a way to get it to call it something from the VBA directly rather than using a cell reference?

 

[Joe4]

You may not have actually be on the sheet you think you are on.

Right after this line:

saveLocation = "K:\Reformatter\Output\" & Range("A1") & ".csv"

add this line:

MsgBox saveLocation

This will show you the EXACT file path and name you are trying to save to. Is it returning what you expect?

Also, remove/comment out ALL line of code regarding "DisplayAlerts" when testing. When debugging, you WANT to see all warnings and errors.

 

[sanantonio]

Great suggestion. So what its doing is trying to save it as Range A1 of the export, rather than the host file. So it's trying to save as a line of gibberish that contains invalid characters.

In this instance what's the code here to make it refer back to the book with the macro in it?

 

[Joe4]

There are a few different ways to do it.
I think the easiest is to capture the value in a variable while you are on/in that workbook in your code. You can do that before you create the new file in your code.
Then use that variable in your file name.

 

[sanantonio]

I'm not sure what you mean.

I used a rnd() instead to assign it a random number which works fine and has done the trick! Thanks for your help!

 

[Joe4]

I'm not sure what you mean.

I used a rnd() instead to assign it a random number which works fine and has done the trick! Thanks for your help!

In your VBA code, this is the line of code which is creating the new workbook:

Set theNewWorkbook = Workbooks.Add

So before that, all you have is the Source workbook. So if you were to add some code like this anywhere BEFORE that line of code:

Dim fName as String
fName = Range("A1")

(assuming you only have one sheet in your Source workbook, or you are already on the sheet that contains that value you want in cell A1),

then you can use that later when you save your file:

saveLocation = "K:\Reformatter\Output\" & fName & ".csv"