Data Export Macro works in Excel 2013 but not Excel 2007

[margentieri]

I have a macro that creates a new workbook, copies some data from the source book, pastes into the newly created book, then saves that new book and closes it. This code works PERFECTLY in Excel 2013, but does not function in Excel 2007. It breaks on the line indicated in my code below with the 1004 error: Method 'SaveAs' of object '_Workbook' failed

The code in question:

'File Save As Dialog
Dim FileSaveName As Variant
FileSaveName = Application.GetSaveAsFilename(InitialFileName:=SavePath & InitFileName, FileFilter:="CSV (Comma Delimited) (*.csv), *.csv, Text (Tab Delimited) (*.txt), *.txt", Title:="Save Results As")
If FileSaveName = False Then
    MsgBox "Operation Cancelled", vbOKOnly
    Exit Sub

ElseIf Dir(FileSaveName) <> "" Then
    MsgBox "File already exists! Please choose a different name and try again.", vbCritical
    Exit Sub

Else
'Create new workbook
Dim ResultsBook As Workbook
Set ResultsBook = Workbooks.Add
'
'
'
'copy some stuff from active workbook and paste into newly created workbook
'
'
'
'File Save
Dim FileSaveType As String
Dim FileSaveFormat As Long
FileSaveType = Right(FileSaveName, Len(FileSaveName) - InStr(FileSaveName, "."))
If FileSaveType = "csv" Then
FileSaveFormat = xlCSV
ElseIf FileSaveType = "txt" Then
FileSaveFormat = xlText
End If
ResultsBook.SaveAs FileName:=FileSaveName, FileFormat:=FileSaveFormat  'code breaks on this line
ResultsBook.Saved = True
ResultsBook.Close

My guess is that it has to do with the fact that I have a variable file format, but this is required given that the user needs to be able to save as either a CSV or tab delimited text, depending on the application.

Like I said, this code runs perfectly in Excel 2013. Some computers at my place of business however use older versions of office, 2007 being common, and this doesn't want to function on those older versions. Any idea how I might be able to modify this code to enable it to run on older versions of Excel (at least as old as 2007) as well?

Many thanks!

[mrice]

Try testing for which version is being used with

Application.Version

and have two versions of the saveas line depending on the application version.

Try recording a saveas in 2007 to get the correct syntax.

[Kevin#]

I have not been able to test with 2007.

I suggest you try using numeric values instead of the names for the file formats. Numeric values are less flaky across versions. And they also work with later versions of Excel.

Your variable now needs to be numeric not a string
so

Dim FileSaveFormat as Integer

then

swap this line
FileSaveFormat = xlCSV
to
FileSaveFormat = 6

swap this line
FileSaveFormat = xlText
to
FileSaveFormat = -4158

[margentieri]

I'm such an idiot. The problem actually has nothing to do with Excel 2013 vs 2007. The computers that are running Excel 2007 have no user login as they are a shared resource, and the name given to the windows account has a "." in it! That was throwing off the InStr calculation in this step:

FileSaveType = Right(FileSaveName, Len(FileSaveName) - InStr(FileSaveName, "."))

Once I recognized that, simply changing the function from InStr to InStrRev fixed the problem!

I appreciate all your input though. Thanks!