Exporting worksheet to CSV Runtime Error

[tj_g_88]

Hi all,

As a newbie to macros I am having trouble trying to save a worksheet to CSV file. The macro worked to .txt but when I change it to .xlCSV it comes up with runtime 1004 error, method 'save as' of object '_workbook' failed.

Any help would be amazing.

Cheers

Sub Export7400_setup_Click()

'
' Exportsub Macro
'
Dim sPath As String
Dim FName As String
FName = Range("rng7400Filename")
sPath = Range("strWorksheetPath")

Sheets("Caliper Sub").Select
Range("A1:E111").Select
Selection.Copy
Workbooks.Add
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
ActiveWorkbook.SaveAs filename:=(sPath) & FName & ".csv", _
FileFormat:=xlCSV
MsgBox "Click OK and Yes to save as Tab Delimited file format"
ActiveWorkbook.Close


End Sub

 

[Teeroy]

Hi and welcome to the forum.

Does sPath include the trailing"\" to join to the filename?

 

[tj_g_88]

Hi and welcome to the forum.

Does sPath include the trailing"\" to join to the filename?

It does.

Any other ideas?

Could it be an issue due to changing from 2007 to 2010?

 

[Teeroy]

Try the following and see if it works for you.

Sub Export7400_setup_Click()
Dim NewWB As Workbook
Dim sPath As String
Dim FName As String

Application.DisplayAlerts = False
FName = Range("rng7400Filename")
sPath = Range("strWorksheetPath")
ThisWorkbook.Sheets("Caliper Sub").Copy
Set NewWB = ActiveWorkbook
NewWB.SaveAs Filename:=(sPath) & FName & ".csv", _
 FileFormat:=xlCSV
Application.DisplayAlerts = True
End Sub