1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Problem Excel 2003 VBA SaveAs quirk

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

  1. quuuaid

    quuuaid Nibble Poster

    65
    2
    34
    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?

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

    Fergal1982 Petabyte Poster

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

    ThomasMc Gigabyte Poster

    1,507
    49
    111
    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
     
    Certifications: MCDST|FtOCC
    WIP: MCSA(70-270|70-290|70-291)
  4. quuuaid

    quuuaid Nibble Poster

    65
    2
    34
    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

Loading...