vba save as using file path and file name variables

jaik22

Board Regular
Joined
Sep 23, 2016
Messages
102
Code:
Dim HINTE As String: HINTE = Sheets("Temp").Range("T18").Value
Dim Pathf As String: Pathf = Sheets("Temp").Range("T15").Value    
ChDir _
        "filep"
    ActiveWorkbook.SaveAs Filename:= _
        "Pathf " & HINTE& ".xlsm" _
        , FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False

Hi, I am trying to do make a file path as a variable. Before I tried to make the file path as a variable, and just hard code the file path, it had no problem and worked fine. However, somehow after I defined file path as a Pathf variable, codes stopped working. Below is the file path, and Is there any way to resolve this issue?
C:\Users\Jaik\Desktop\RO

Thank you
 
Last edited:

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
Looks like you may have some unwanted spaces after Pathf and unless HINTE begins with a path separator, you are missing that too.
Does this work?
Code:
ActiveWorkbook.SaveAs Filename:= _
        "Pathf" & Application.PathSeparator & HINTE & ".xlsm" _
        , FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
 
Upvote 0
remove the quote marks from around Pathf in the SaveAs statement. Variables do not use quote marks. I note that you had a space after the path name. Was that intentional? The value in your cell T15 reference should have a closing backslash (\). If it does not, then your file will be saved with part of the path name in that cell as the leading part of the file name. To avoid that, Use a statement after you initialize the varible like:
Code:
If Right(Pathf, 1) <> "\" Then Pathf = Pathf & "\"
That will ensure that your file name does not eat on your path.
 
Upvote 0
Thanks guys for the help. However, I am getting the "Method 'Save as' of object_workbook failed" Below is code that I used

Code:
  ActiveWorkbook.SaveAs Filename:= _
        [COLOR=#333333]Pathf [/COLOR]& Application.PathSeparator & [COLOR=#333333]HINTE [/COLOR]& ".xlsm" _
        , FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
 
Upvote 0
Thanks guys for the help. However, I am getting the "Method 'Save as' of object_workbook failed" Below is code that I used

Code:
  ActiveWorkbook.SaveAs Filename:= _
        [COLOR=#333333]Pathf [/COLOR]& Application.PathSeparator & [COLOR=#333333]HINTE [/COLOR]& ".xlsm" _
        , FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False

Try this
Code:
If Right(Pathf, 1) <> "\" Then Pathf = Pathf & "\"
ActiveWorkbook.SaveAs Pathf & HINTE & ".xlsm", 52
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,167
Members
448,554
Latest member
Gleisner2

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