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

 

[ESPN8]

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.

I figured it out with Dante's help. Dante you on the right path. I had to remove the DIM
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.Range("I23").Value
ws.ListObjects(1).DataBodyRange.Copy
wsNew.Range("A1").PasteSpecial Paste:=xlPasteAll
.SaveAs Filename:=wb.Path & "" & wbNewName & ".csv", _
FileFormat:=xlCSVMSDOS, CreateBackup:=False
End With
ActiveWorkbook.Close SaveChanges:=True

End Sub

 

[DanteAmor]

The error is because the cell is blank.


At the time of creating the book you have Two L23 cells, the one with the macro and the new book. You should refer to the book with the macro:
ws.range("L23")

If you don't put ws, then reference the new book and the cell is empty because it is a new book.

What I want to observe with the record macro is if you don't need any parameters, but you sent another code, so I can't check it.


All the tests that I have done with my test files work for me.

 

[DanteAmor]

I figured it out with Dante's help. Dante you on the right path. I had to remove the DIM
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.Range("I23").Value
ws.ListObjects(1).DataBodyRange.Copy
wsNew.Range("A1").PasteSpecial Paste:=xlPasteAll
.SaveAs Filename:=wb.Path & "" & wbNewName & ".csv", _
FileFormat:=xlCSVMSDOS, CreateBackup:=False
End With
ActiveWorkbook.Close SaveChanges:=True

End Sub

I23 or L23.

So it already works for you?

 

[ESPN8]

I23 or L23.So it already works for you?

It works! Thank you.

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.