Macro Issue

[DPChristman]

I am trying to rung a 'save as file name' macro, and getting the error:

[h=1]Run-time error '1004': Microsoft Excel cannot access the file c:\program files (x86)\microsoft office\office 14\91cd6000. there are several possible reasons:[/h]
[FONT=&quot]-The file name or path does not exist.[/FONT]
[FONT=&quot]-The file is being used by another program.[/FONT]
[FONT=&quot]-The workbook you are trying to save has the same name as a currently open workbook.

[/FONT]
I had this problem initially last year, but ran into a snag, since it is a format that I send out to a number of people. The (are supposed to) save the format to their computers, and use the format as a blank.

I have included the code below. What can I do to a) prevent the problem going forward and b) make the code portable to whatever laptop it winds up on.

Sub SaveAsExample()


Dim FName           As String
Dim FPath           As String
    
    FPath = "C:"
    FName = Sheets("Form").Range("I4").Text
    ThisWorkbook.SaveAs Filename:=FName


End Sub

In the example above, I4 represents the location of the data that will be used to name the file
I4= store number Store Name - City, State-district

<tbody>
</tbody>

 

[the_hodgee]

Hello, try this and let me know how you get on.

Sub SaveAsExample()


Dim FName           As String
Dim FPath           As String
    
    FPath = "C:"
    FName = Sheets("Form").Range("I4")  & ".xlsx"
    ThisWorkbook.SaveAs Filename:=FName


End Sub

 

[DPChristman]

Why ".xlsx"

 

[DPChristman]

Hello, try this and let me know how you get on.

Sub SaveAsExample()


Dim FName           As String
Dim FPath           As String
    
    FPath = "C:"
    FName = Sheets("Form").Range("I4")  & ".xlsx"
    ThisWorkbook.SaveAs Filename:=FName


End Sub

Got Runtime error 1004 the extension cannot be used with selected file type. Change the file Extension in the file name text box, or select a different file type by changing the Save As type

 

[Logit]

Sub SaveAsExample()


Dim FName           As String
Dim FPath           As String
    
    FPath = "C:"
    FName = Sheets("Form").Range("I4")  & ".xlsx"   '<---- change .xlsx  to .xlsm
    ThisWorkbook.SaveAs Filename:=FName


End Sub

You have to save the workbook as a macro-enabled version because you are using a macro to auto save.

Workbooks with the extension .xlsx do not contain macros.

 

[DPChristman]

Yes, I actually have several buttons set up on the page

Save File - which is the one I had an issue with
Freeze - which takes all the vlookup formulas, and converts them into static data
SpellCheck - because none of my users remember to do it, and they are all very untrained on excel
Finish - This deletes the dropdown tab with all the data validation and lookups, deletes the macro buttons, and converts the format from .xlsm to .xlsx (also greatly reduces the size of the finished document which is then emailed to everyone).

Sub SaveAsExample()


Dim FName           As String
Dim FPath           As String
    
    FPath = "C:"
    FName = Sheets("Form").Range("I4")  & ".xlsx"   '<---- change .xlsx  to .xlsm
    ThisWorkbook.SaveAs Filename:=FName


End Sub

You have to save the workbook as a macro-enabled version because you are using a macro to auto save.

Workbooks with the extension .xlsx do not contain macros.

 

[Logit]

If I correctly understand your situation .. sounds like you need to copy the data to ANOTHER workbook and save the other workbook as .xlsx

This work circumvent Excel telling you it won't save the exiting workbook unless it's in macro form.

 

[DPChristman]

Actually, this brings up another point. My Finish macro automatically saves the file to the same directory as it was originally in (example below)

However, this may not be the same directory that the end user will be running the original from.

How to I customize this to have the file automatically save the completed file to same directory that the end user has the format in?

Sub Finish()
Application.DisplayAlerts = False
'
' Finish Macro
'


'
    ActiveSheet.Shapes.Range(Array("Button 7")).Select
    Selection.Delete
    Selection.Cut
    ActiveSheet.Shapes.Range(Array("Button 9")).Select
    Selection.Delete
    Selection.Cut
    ActiveSheet.Shapes.Range(Array("Button 10")).Select
    Selection.Delete
    Selection.Cut
    ActiveSheet.Shapes.Range(Array("Button 11")).Select
    Selection.Delete
    Selection.Cut
    Sheets("DropDownData").Select
    ActiveWindow.SelectedSheets.Delete
    Sheets("Form").Select
    ChDir _
        "S:\directory\subdirectory1\subdirectory2\subdirectory3"
    ActiveWorkbook.SaveAs Filename:= _
        "S:\directory\subdirectory1\subdirectory2\subdirectory3\filename.xlsx" _
        , FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
Application.DisplayAlerts = True
End Sub

 

[Logit]

Always best to give your user an opportunity to save the file where they desire...

This will open the FileDialog control and give them the opportunity to select where, using the extension .xlsx

Option Explicit


Sub SaveAs()
Dim strFileSaveName As Variant
Dim fileFilter As String
On Error Resume Next


    strFileSaveName = Application.GetSaveAsFilename(fileFilter:="Microsoft Excel Workbook (*.xlsx), *.xlsx")
    
    ActiveWorkbook.SaveAs Filename:=strFileSaveName, CreateBackup:=False


End Sub

 

[DPChristman]

This is where it gets complicated, because not only does it need to save the filename based on the contents of a given cell, but in the finish macro, it needs to be converted from an xlsm to an xlsx, and that is where the macros get tricky. I cannot make it part of the Save File macro I mentioned earlier, because once it is converted to an xlsx, the macros will no longer work, and they will need that capability for other parts of the process.
So the conversion can only​ take place in the last step before distribution.