Problem Excel 2003 VBA SaveAs quirk

Discussion in 'Scripting & Programming' started by quuuaid, Mar 25, 2011.

  1. quuuaid

    quuuaid Nibble Poster

    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! :x

    Has anyone experienced this before?

    Sub AlterFileProcedure()
    ' make date format file-save friendly :p
        Dim Today As String
        Today = Format(Date, "")
    ' select & delete unneeded columns
        ActiveCell.Offset(0, 9).Range("A1").Activate
        Selection.Delete Shift:=xlToLeft
    ' resize columns appropriately
        ActiveCell.Offset(0, -8).Columns("A:A").EntireColumn.Select
        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
    Certifications: A+, N+, MCDST
    WIP: Drupal, PHP
  2. Fergal1982

    Fergal1982 Petabyte Poster


    ' 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
    Certifications: ITIL Foundation; MCTS: Visual Studio Team Foundation Server 2010, Administration
    WIP: None at present
  3. ThomasMc

    ThomasMc Gigabyte Poster

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

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

    I was to slow lol
    Certifications: MCDST|FtOCC
    WIP: MCSA(70-270|70-290|70-291)
  4. quuuaid

    quuuaid Nibble Poster

    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 :oops:
    Certifications: A+, N+, MCDST
    WIP: Drupal, PHP

Share This Page

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.