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:

Some videos you may like

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
16,732
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
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
11,762
Office Version
2013
Platform
Windows
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.
 

jaik22

Board Regular
Joined
Sep 23, 2016
Messages
102
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
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
11,762
Office Version
2013
Platform
Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,095,360
Messages
5,444,010
Members
405,260
Latest member
Khauff

This Week's Hot Topics

Top