Saving workbook to CSV with Custom Name

[ESPN8]

Hi Everyone,

I have figured out some code to copy all the data from a table, create a new workbook, paste the data and then save the workbook as a CSV as the name of the original table the data came from. I now am trying to figure out how to save the file as a custom name from cell L23 from the original workbook. Any help would be appreciated.

Thank you in Advance

Sub ExportTableToCSVFile()
Dim wb As Workbook, wbNew As Workbook
Dim ws As Worksheet, wsNew As Worksheet
Dim wbNewName As String


Set wb = ThisWorkbook
Set ws = ActiveSheet
Set wbNew = Workbooks.Add
With wbNew
Set wsNew = wbNew.Sheets("Sheet1")
wbNewName = ws.ListObjects(1).Name
ws.ListObjects(1).DataBodyRange.Copy
wsNew.Range("A1").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
.SaveAs Filename:=wb.Path & "" & wbNewName & ".csv", _
FileFormat:=xlCSVMSDOS, CreateBackup:=False
End With
ActiveWorkbook.Close SaveChanges:=True

End Sub

 

[DanteAmor]

"Journal Entry Upload 8.26.19"

What else does the error message say?
You literally have quotes in the cell?

If so, try:

Sub ExportTableToCSVFile()
  Dim wb As Workbook, wbNew As Workbook
  Dim ws As Worksheet, wsNew As Worksheet
  Dim wbNewName As String
  
  Application.ScreenUpdating = False
  Application.DisplayAlerts = False
  Set wb = ThisWorkbook
  Set ws = ActiveSheet
  Set wbNew = Workbooks.Add
  With wbNew
    Set wsNew = .Sheets(1)
[COLOR=#0000ff]    wbNewName = Replace(ws.Range("L23").Value, """", "")[/COLOR]
    ws.ListObjects(1).DataBodyRange.Copy
    wsNew.Range("A1").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
    .SaveAs Filename:=wb.Path & "\" & wbNewName & ".csv", FileFormat:=xlCSVMSDOS
    .Close SaveChanges:=False
  End With
  MsgBox "End"
End Sub

 

[ESPN8]

What else does the error message say?
You literally have quotes in the cell?

There's nothing else in the error message and those quotes are just my way of saying that's exactly whats in the cell. There no quotes in the actual cell just text and numbers.

 

[DanteAmor]

There's nothing else in the error message and those quotes are just my way of saying that's exactly whats in the cell. There no quotes in the actual cell just text and numbers.

Did your macro work before switching to Range("L23")?


You could activate the macro recorder and save a file with csv format. Stop the recorder and the generated code you paste it here.

 

[ESPN8]

Did your macro work before switching to Range("L23")?


You could activate the macro recorder and save a file with csv format. Stop the recorder and the generated code you paste it here.

Yes my macro has worked just fine for a few weeks. Just need to be able to choose name for the new file now

 

[DanteAmor]

Yes my macro has worked just fine for a few weeks. Just need to be able to choose name for the new file now

You could activate the macro recorder and save a file with csv format. Stop the recorder and the generated code you paste it here.

You can change the data of your cell L23 for something like "test"
And try again

You tried the macro of post #11

 

[ESPN8]

You could activate the macro recorder and save a file with csv format. Stop the recorder and the generated code you paste it here.

You can change the data of your cell L23 for something like "test"
And try again

You tried the macro of post #11

Yes, Same error

 

[DanteAmor]

Yes, Same error

Could you answer the other notes, please.

- You could activate the macro recorder and save a file with csv format. Stop the recorder and the generated code you paste it here.

- You can change the data of your cell L23 for something like "test"
And try again

 

[ESPN8]

Could you answer the other notes, please.

- You could activate the macro recorder and save a file with csv format. Stop the recorder and the generated code you paste it here.

- You can change the data of your cell L23 for something like "test"
And try again

Recorder
Sub Macro1()
'
' Macro1 Macro
'
'
Range("A2").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Workbooks.Add
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
ActiveWorkbook.SaveAs Filename:= _
"C:\Users\ESPN8\Documents\JE Upload 8.26.19.xls", FileFormat:=xlExcel8, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
ActiveWindow.Close
End Sub

Same error when I use Test in cell L23

 

[DanteAmor]

But you are asking to save as csv and in your macro you saved it as excel.
You could try again.

Recorder
Sub Macro1()
'
' Macro1 Macro
'
'
Range("A2").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Workbooks.Add
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
ActiveWorkbook.SaveAs Filename:= _
"C:\Users\ESPN8\Documents\JE Upload 8.26.19.xls", FileFormat:=xlExcel8, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
ActiveWindow.Close
End Sub

Same error when I use Test in cell L23

--------------------------------------------------------------------------
I also consider that you have not made the sheet reference change.
Also try this:

Sub ExportTableToCSVFile()
  Dim wb As Workbook, wbNew As Workbook
  Dim ws As Worksheet, wsNew As Worksheet
  Dim wbNewName As String
  
  Application.ScreenUpdating = False
  Application.DisplayAlerts = False
  Set wb = ThisWorkbook
  Set ws = ActiveSheet
[COLOR=#0000ff][B]  wbNewName = ws.Range("L23").Value[/B][/COLOR]
  Set wbNew = Workbooks.Add
  With wbNew
    Set wsNew = .Sheets(1)
    ws.ListObjects(1).DataBodyRange.Copy
    wsNew.Range("A1").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
    .SaveAs Filename:=wb.Path & "\" & wbNewName & ".csv", FileFormat:=xlCSVMSDOS
    .Close SaveChanges:=False
  End With
  MsgBox "End"
End Sub

 

[ESPN8]

But you are asking to save as csv and in your macro you saved it as excel.
You could try again.




--------------------------------------------------------------------------
I also consider that you have not made the sheet reference change.
Also try this:

Sub ExportTableToCSVFile()
  Dim wb As Workbook, wbNew As Workbook
  Dim ws As Worksheet, wsNew As Worksheet
  Dim wbNewName As String
  
  Application.ScreenUpdating = False
  Application.DisplayAlerts = False
  Set wb = ThisWorkbook
  Set ws = ActiveSheet
[COLOR=#0000ff][B]  wbNewName = ws.Range("L23").Value[/B][/COLOR]
  Set wbNew = Workbooks.Add
  With wbNew
    Set wsNew = .Sheets(1)
    ws.ListObjects(1).DataBodyRange.Copy
    wsNew.Range("A1").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
    .SaveAs Filename:=wb.Path & "\" & wbNewName & ".csv", FileFormat:=xlCSVMSDOS
    .Close SaveChanges:=False
  End With
  MsgBox "End"
End Sub

Dante,
I don’t understand your line of thinking. How will recordingthe macro help with this problem solving? There is also no difference betweennaming the excel vs csv.

The other code you asked about I have already tried. I referenced the sheet. The error code is notreferencing that. Its referencing the object (which is the table). There issomething about the way the parameters are set up for the code that doesn’tlike trying to reference the cell.