Problem Excel 2003 VBA SaveAs quirk

Oh man! Ad-blocking software has been detected! :'(

This website is run by the community, for the community... and it needs advertisements in order to keep running. Blocking our ads means your killing our stats!
Please disable your ad-block, or become a premium member to hide all advertisements and this notice.

Hi all. I've got a question for the VBA gurus out there who have experience with using the SaveAs method.

The purpose of the procedure below is to make standard alterations to spreadsheets that are sent to me weekly, then protect and save the file where I want it. It works no probs, but the file displays the unknown file type icon rather than an Excel icon in the destination folder. Again, this isn't a prob as the files still open in Excel when you double-click on them. It's just really annoying me because I want the Excel icon to be displayed with the file as it should!

Has anyone experienced this before?

Code:

Sub AlterFileProcedure()

' make date format file-save friendly :p
    Dim Today As String
    Today = Format(Date, "dd.mm.yy")
    
' select & delete unneeded columns
    ActiveCell.Range("A:A,B:B,E:E,J:J").Select
    ActiveCell.Offset(0, 9).Range("A1").Activate
    Selection.Delete Shift:=xlToLeft
    
' resize columns appropriately
    ActiveCell.Offset(0, -8).Columns("A:A").EntireColumn.Select
    ActiveCell.Columns("A:A").EntireColumn.EntireColumn.AutoFit
    ActiveCell.Offset(0, 3).Columns("A:A").EntireColumn.EntireColumn.AutoFit
    ActiveCell.Offset(0, 4).Columns("A:A").EntireColumn.EntireColumn.AutoFit
    
' resize Notes column & tweek cell contents alignment & behaviour to be "readable"
    ActiveCell.Offset(0, 2).Columns("A:A").EntireColumn.ColumnWidth = 102.43
    ActiveCell.Offset(0, 2).Columns("A:A").EntireColumn.Select
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlTop
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    
    ActiveCell.Offset(0, -3).Range("A1").Select
    
' set password & save doc to relevant folder
    ActiveSheet.Protect Password:="password"
    [B]ActiveWorkbook.SaveAs Filename:="W:\A Folder\A Folder\FileName " _
    & Today, FileFormat:=xlWorkbookNormal, Local:=True[/B]
End Sub

 

Oh man! Ad-blocking software has been detected! :'(

This website is run by the community, for the community... and it needs advertisements in order to keep running. Blocking our ads means your killing our stats!
Please disable your ad-block, or become a premium member to hide all advertisements and this notice.

Try

Code:

' set password & save doc to relevant folder
    ActiveSheet.Protect Password:="password"
    ActiveWorkbook.SaveAs Filename:="W:\A Folder\A Folder\FileName " _
    & Today & ".xls", FileFormat:=xlWorkbookNormal, Local:=True

 

wouldn't you just append the .xls to the end of your filename

"W:\A Folder\A Folder\FileName " _
& Today & ".xls/.xlsx",

[added]
I was to slow lol

 

Brilliant, thanks Fergal & Tom, I'll add that on Monday. You know I'd already tried using the file extension but now I see why it didn't work... I forgot the quotations