Export hidden sheet to .csv file with dynamic name and date via macro.

Hello,

I am trying to create a macro that will export a hidden sheet to a *.csv file. I am also trying to have the macro skip any lines where the column A is blank (have not worked on this part of the code yet)

Can anyone see any underlying issues with the below? I keep having an exception on the "Sheets("DataImport").Copy" line with run-time error '1004' - Copy Method of worksheet class failed.

Sub CSVExport()

Dim MyPath As String
Dim MyFileName As String

'The path and file names:
MyPath = ("Settings!B2")
MyFileName = ("R1!E34") & Format(("R1!E35"), "DD.MM.YYYY") & ".CSV"

'Makes sure the path name ends with "\":
If Not Right(MyPath, 1) = "\" Then MyPath = MyPath & "\"

'Makes sure the filename ends with ".csv"
If Not Right(MyFileName, 4) = ".csv" Then MyFileName = MyFileName & ".csv"


'Copies the sheet to a new workbook:
Sheets("DataImport").Copy
'The new workbook becomes Activeworkbook:

With ActiveWorkbook
'Saves the new workbook to given folder / filename:
.SaveAs Filename:= _
MyPath & MyFileName, _
FileFormat:=xlCSV, _
CreateBackup:=False
'Closes the file
.Close False
End With

End Sub


Kind Regards

David

Can not find whats wrong without the file, please attach the file which you have been using (remove sensitive data)

Hi kiran,

I have made some progress. see below I am having trouble with the variable "MyPath". How can i call a value which is located on another sheet and assign it to this variable?

Sub CSVExport()

Dim MyPath As String
Dim MyFileName As String
Dim Track As String

'The path and file names:
Track = Range("E34")
MDate = Range("E35")
MyPath = "\\dsmainprd\DS Data Import"
MyFileName = Track & Format(MDate, "ddmmyy")

'Makes sure the path name ends with "\":
If Not Right(MyPath, 1) = "\" Then MyPath = MyPath & "\"

'Makes sure the filename ends with ".csv"
If Not Right(MyFileName, 4) = ".csv" Then MyFileName = MyFileName & ".csv"


'Copies the sheet to a new workbook:
Sheets("DataImport").Visible = True
Sheets("DataImport").Copy
'The new workbook becomes Activeworkbook:
Application.DisplayAlerts = False
With ActiveWorkbook
'Saves the new workbook to given folder / filename:
.SaveAs Filename:= _
MyPath & MyFileName, _
FileFormat:=xlCSV, _
CreateBackup:=False
'Closes the file
.Close False
End With

MsgBox "Data file has been successfully exported to csv", vbInformation

Application.DisplayAlerts = True
Sheets("DataImport").Visible = False
End Sub

For example if path is in "G10" cell of "Sheet5" or "My Data Sheet", use
MyPath =Sheet5.Range("G10")
MyPath =Sheets("My Data Sheet").Range("G10")

Thanks kiran, worked great.

kind regards

David

You are welcome.
Happy to support anytime.