Results 1 to 5 of 5

Thread: Save button giving error when file extensions are active on PC

  1. #1
    New Member
    Join Date
    Aug 2016
    Posts
    29
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Save button giving error when file extensions are active on PC

    Hi all,

    I've got a few spreadsheets where I've added buttons to make it easier for people to save files to a specified location. All seemed to be going well, however...

    Last week I enabled file extensions on my PC, as I needed to swap some files from .txt to .csv (it was some file splitting I was doing for someone). I left it as it was, as I kind of prefer them there anyway. But now when I try to save, I'm getting an error and given it doesn't cause an error on other PCs, the only explanation I can see is that the file extension thing must be the issue. So assuming I've identified the problem correctly (?), is there some code I can add which can identify if the PC being used is showing file extensions? My code is below. Any help would be much appreciated

    Thanks,
    Sam

    Code:
    Sub SaveFiles1()'
    ' SaveFiles1 Macro
    ' Saves files in correct formats
    '
        'Check user wants to continue
        If MsgBox("This will save all data and exit the spreadsheet - are you sure?", vbOKCancel) = vbCancel Then Exit Sub
        
        ActiveWorkbook.Unprotect Password:="CREATOR"
        Application.ScreenUpdating = False
        
        'Gets file name for Product Importer Template
        ProductImporterName = ActiveWorkbook.Name
        
    
    
    'Sets out file names
        Dim myMonth As String
        myMonth = Format(Sheets("Importer Template").Range("R1"), "mmmm")
        
        Dim myTicket As String
        myTicket = Sheets("Importer Template").Range("B1")
    
    
                    Dim myFilePathOriginal As String
                    myFilePathOriginal = Sheets("Importer Template").Range("B4")
    
    
                    Dim myFilePathImport As String
                    myFilePathImport = Sheets("Importer Template").Range("B4")
    
    
                    Dim myFileName As String
                    myFileName = myMonth & "_" & myTicket
    
    
    Application.DisplayAlerts = False
    
    
    '   Adds workbook for 'Importer Template' to be copied to
    
    
        Workbooks.Add
        ActiveWorkbook.SaveAs Filename:="Import Data.xls"
        Windows("Import Data").Activate
        Windows(ProductImporterName).Activate
        Sheets("Load File").Select
        Cells.Select
        Selection.Copy
        Windows("Import Data").Activate
        Cells.Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
    
    
        'Formats columns
        Columns("A:Q").EntireColumn.AutoFit
        Range("C:C,L:L").Select
        Selection.NumberFormat = "m/d/yyyy"
        Range("A1").Select
    
    
    Application.DisplayAlerts = True
    
    
        Sheets("Sheet1").Select
        ActiveWorkbook.SaveAs Filename:= _
            myFilePathImport & "Evonex Import Data_" & myFileName & ".xlsx", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
        ActiveWindow.Close
        
        
    'Clear out old data
        Windows(ProductImporterName).Activate
        Sheets("Importer Template").Select
        Application.Run "ResetSheets2"
    
    
        Application.ScreenUpdating = True
    
    
        'Close and save
        ActiveWindow.Close True
    '
    End Sub

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,078
    Post Thanks / Like
    Mentioned
    469 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Save button giving error when file extensions are active on PC

    Simply add the extension to the file names & it will work regardless of settings.
    Code:
    Windows("Import Data.xls")
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  3. #3
    Board Regular jkpieterse's Avatar
    Join Date
    Dec 2007
    Location
    Weert
    Posts
    894
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Save button giving error when file extensions are active on PC

    If I recall correctly you can add the extension to your code and it will keep working whether or not file extensions are visible.

    So change
    Code:
    Windows("Import Data").Activate
    to
    Code:
    Windows("Import Data.xlsx").Activate
    Best to try on your own system first though
    Regards,

    Jan Karel Pieterse
    Excel MVP jkp-ads.com

  4. #4
    New Member
    Join Date
    Aug 2016
    Posts
    29
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Save button giving error when file extensions are active on PC

    Thank you both for your help, that's brilliant

  5. #5
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,078
    Post Thanks / Like
    Mentioned
    469 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Save button giving error when file extensions are active on PC

    You're welcome & thanks for the feedback
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •