Save button giving error when file extensions are active on PC

SammyCRX

New Member
Joined
Aug 15, 2016
Messages
34
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
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Simply add the extension to the file names & it will work regardless of settings.
Code:
Windows("Import Data.xls")
 
Upvote 0
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 :)
 
Upvote 0
You're welcome & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,999
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top