VBA to determine dynamic path of linked workbook file

[Morne1972]

I'm not an expert on the VBA and struggle to build a string for a path and file:

I have a workbook ?Inc(Auto).xls? that is linked to another workbook, ?GLB.xls?, that is saved in the same folder as ?Inc(Auto).xls?

Since these files are copied together to other folders by users, I would like to obtain the date and time of ?GLB.xls? in the ?newly? saved user?s folder by running VBA code to have the result pasted in Inc(Auto).xls in cell C2

My problem is with creating the string name ?MyFullPath?. I?m missing something here? Any help will be valuable.


Sub GetFildeDateTime()
Dim DResult As Date
Dim MyPath, MyFile, MyFullPath As String

MyPath = Application.ActiveWorkbook.Path
MyFile = "\GLB.xls"
MyFullPath = MyPath + MyFile

DResult = FileDateTime(MyFullPath)
Range("C2") = "Last updated: " & DResult
End Sub

[carlmon]

CHANGE

Please Login or Register  to view this content.

TO

Please Login or Register  to view this content.

[Morne1972]

Hi carlmon

Thx for the reply.

I get an error directing at DResult

Run-time error '5':
Invalid procedure call or argument

[carlmon]

CHANGE

Please Login or Register  to view this content.

TO

Please Login or Register  to view this content.

AND SEE IF THAT HELPS

[Morne1972]

Regrettably, the same message appears directing at DResult :-(

[carlmon]

you're getting the procedure argument because it can't find the file you are referencing... you need to look in your locals window and see if MyFullPath matches the file you want to reference

[Morne1972]

Thx for your diligence, carlmon.

Hence my struggle ☹

When I refer to the stagnant file location, I do get the right date and time.
Example
DResult = FileDateTime("E:\OD\My Documents at work\ SBG\Finance\Income Statement\UCE\GLB.xls")
But the problem is building the dynamic MyFullPath for GLB.xls between the brackets ( )

[carlmon]

is your active workbook found here: "E:\OD\My Documents at work\ SBG\Finance\Income Statement\UCE" ??

[Morne1972]

Indeed it is.

[carlmon]

The only thing that I can see going wrong is if you are running the code from a workbook that is not located there. There should be no reason that you are getting an error... what is dynamic about the file location, why not hard code it?

[Morne1972]

Perhaps my initial explanation is a bit wonky.

Inc(Auto).xls is linked to GLB.xls. These two files are saved in the same location. Users copy the two files to their own specific locations. Instead of hard coding the new file locations for each user, it would be preferable if the location could be dynamically updated with VBA.

[carlmon]

brother, just use this code then:

Please Login or Register  to view this content.

assuming you want to update the save date for the workbook that they are in.. ?

[Morne1972]

Thx bro.
The problem is that it will return the 'File Date and Time' for Inc(Auto) instead of GLB.xls.
GLB.xls is a fin-system downloaded file that is replaced, for instance, weekly. Hence, the 'date and time' of the file is displayed in Inc(Auto) as confirmation that the latest downloaded file, GLB.xls was linked.

[carlmon]

is there any commonality of where each user saves this GLB file?

[Morne1972]

Unfortunately not. It would have made life easier though.

[carlmon]

I'm going to re-create your situation on my end and see what I can do to fix it

[carlmon]

it works for me... are you sure it's an .xls file and not the default .xlsx?

[Morne1972]

Hi, carlmon. Yep, my file is .xls
Would you mind sharing your test file? Perhaps I can have a look at it and compare it to my own.
Truly a strange phenomenon indeed.