How to get the excel file name / path in VBA ? Help

[punnipah]

Hi,

i want to get the excel file name in my path how can i do it ?


Sub Main()
Call initial
' =============================================================================================================================
Dim newDate: newDate = Format(DateAdd("M", 0, Now), "MMMM")

Call wbSetOpen(g_wbMMT, g_dirInputPath, "**.XLSX*")
Call wbSaveReplace(g_wbMMT, g_dirOutputPath + "Output_(I want to get name of file in g_dirInputPath)" & CStr(Format(DateAdd("M", 0, Now), "MMMM_YYYY")) & ".xlsx", False)

 

[6StringJazzer]

g_dirInputPath is a variable and you have not shown enough information for us to answer your question. Is this a full path and you just want to pull out the file name at the end of the path?

Where is g_dirInputPath declared?
Where and how is the value of g_dirInputPath assigned?
What is the value of g_dirInputPath at the point you have highlighted?

 

[punnipah]

g_dirInputPath is a variable and you have not shown enough information for us to answer your question. Is this a full path and you just want to pull out the file name at the end of the path?

Where is g_dirInputPath declared?
Where and how is the value of g_dirInputPath assigned?
What is the value of g_dirInputPath at the point you have highlighted?

d_dirInputPath is :HRD_MMY.XLSX
i want to get this Name to Save A New File On outputpath

 

[offthelip]

if your sub wbsetopen actuallly opens the file you want to use you can find the path and file name very simply, try this code:

Sub test()
tt = ActiveWorkbook.Path
MsgBox tt
tn = ActiveWorkbook.Name
MsgBox tn


End Sub

 

[6StringJazzer]

d_dirInputPath is :HRD_MMY.XLSX

Is that a typo? Because the code refers to g_dirInputPath

The ":" character is illegal in a Windows file name. What is that doing in there? I think you just need to remove that and append it to g_dirOutputPath. But same problem--you haven't told us what that is. Does it end in a "\" character? If so do this:

Sub Main()
Call initial
' =============================================================================================================================
Dim newDate: newDate = Format(DateAdd("M", 0, Now), "MMMM")

Call wbSetOpen(g_wbMMT, g_dirInputPath, "**.XLSX*")
Call wbSaveReplace(g_wbMMT, g_dirOutputPath & Replace(g_dirInputPath, ":", "") & CStr(Format(DateAdd("M", 0, Now), "MMMM_YYYY")) & ".xlsx", False)

If not do this

Call wbSaveReplace(g_wbMMT, g_dirOutputPath & "\" & Replace(g_dirInputPath, ":", "") & CStr(Format(DateAdd("M", 0, Now), "MMMM_YYYY")) & ".xlsx", False)

 

[MARK858]

The ":" character is illegal in a Windows file name

It is separator on a MAC though. @punnipah just to clarify are you on a Windows computer or a MAC?

 

[6StringJazzer]

It is separator on a MAC though.

I saw that and thought he was on a Mac, but then saw Windows in the profile and so thought he was on Windows, and then just ended up thinking WTF.

I do not have a Mac but my understanding is that ":" is the separator for the older OS and currently it's now "\".