VBA (2013) SaveAs Dialog does not work properly with 97-2003/xls workbook

[WUDA1]

Hey all!

So I'm just learning VBA, but I have a decent understanding of basic concepts. I'm having issues with a SaveAs Dialog filling out correctly.

I've noticed that whenever I try to run the following macro with an older .xls workbook it does not automatically populate.

strName, TeamName, and Week are all strings that are being referenced from code above.

Application.Dialogs(xlDialogSaveAs).Show _
    ("K:\users\currentuser\timeshts\" & strName & "-" & TeamName & "-Week" & Week & ".xlsx")

The issue is that all of the timesheets that I'm collecting are sent in as .xls workbooks.

 

[MARK858]

You can't save a file as xlsx in a version before 2007 as it didn't exist then, you need to either save it as xls or test the version, something like...

Dim FileExtStr As String
  
With ActiveWorkbook
        If Val(Application.Version) < 12 Then
            FileExtStr = ".xls"
        Else
                Select Case .FileFormat
                Case 51: FileExtStr = ".xlsx"
                Case 52:
                    If .HasVBProject Then
                        FileExtStr = ".xlsm"
                    Else
                        FileExtStr = ".xlsx"
                    End If
                Case 56: FileExtStr = ".xls"
                Case Else: FileExtStr = ".xlsb"
                End Select
            End If
    End With
   
    Application.Dialogs(xlDialogSaveAs).Show _
    ("K:\users\currentuser\timeshts\" & strName & "-" & TeamName & "-Week" & Week & FileExtStr)

obviously you need to add your variables etc.

 

[WUDA1]

You can't save a file as xlsx in a version before 2007 as it didn't exist then, you need to either save it as xls or test the version, something like...

Dim FileExtStr As String
  
With ActiveWorkbook
        If Val(Application.Version) < 12 Then
            FileExtStr = ".xls"
        Else
                Select Case .FileFormat
                Case 51: FileExtStr = ".xlsx"
                Case 52:
                    If .HasVBProject Then
                        FileExtStr = ".xlsm"
                    Else
                        FileExtStr = ".xlsx"
                    End If
                Case 56: FileExtStr = ".xls"
                Case Else: FileExtStr = ".xlsb"
                End Select
            End If
    End With
   
    Application.Dialogs(xlDialogSaveAs).Show _
    ("K:\users\currentuser\timeshts\" & strName & "-" & TeamName & "-Week" & Week & FileExtStr)

obviously you need to add your variables etc.

Shouldn't the SaveAs Dialog be able to change the save format? Basically what's happening is that I'm opening timesheets in an XLS workbook, running a few macros to clean up the data, then attempting to save the document using the above code into xlxs. The problem is that the timesheets are in xls and not xlxs.

 

[WUDA1]

Basically, when I use a XLXS as the source, I get the top result, what I want.

But, when I use an XLS as the source, I get the bottom, which is what I DO NOT want.

Edit: also, it's not letting me edit the first two posts, so my apologies for the this post.

 

[MARK858]

What do you get with the code below?

Sub xcv()

Application.Dialogs(xlDialogSaveAs).Show _
    Arg1:="K:\users\currentuser\timeshts\" & strName & "-" & TeamName & "-Week" & Week & ".xlsx", Arg2:=xlOpenXMLWorkbook
End Sub

P.S.
You can only edit your posts for a certain amount of time, I think its 10 minutes but might be 15.

 

[WUDA1]

What do you get with the code below?

Sub xcv()

Application.Dialogs(xlDialogSaveAs).Show _
    Arg1:="K:\users\currentuser\timeshts\" & strName & "-" & TeamName & "-Week" & Week & ".xlsx", Arg2:=xlOpenXMLWorkbook
End Sub

P.S.
You can only edit your posts for a certain amount of time, I think its 10 minutes but might be 15.

This seems to work. What does the second argument do?

 

[MARK858]

It sets the file format to xlsx (if I was doing a straightforward saveas without the dialog box I'd use FileFormat:=51).

 

[WUDA1]

I would most likely do it without the prompt box as well, but this is for multiple users who all store files in different locations. Unfortunately it's not policy to keep files in a central location.

Is there any reason to use FF=51 instead of xlOpenXMLWorkbook in the arguments?

 

[MARK858]

Just I have never had any problems and someone much more knowledgeable than me swears by it.