Determining the filename of a *.xltm file for a copy paste macro.

[dougmarkham]

Hi Folks,

I have a few examples of how to check if a 'saved workbook' of any kind is 'open' or 'exists' via checking the filepath; however, what can be done if there is no saved file but an open template?
What I'm wondering: Is there a method using VBA to check if a *.xltm file has been opened by double-click?

For instance, ambiguity would probably arise if the user went through the following process:
Workbook.xltm-->double click-->Workbook1-->use wb-->Close Workbook1
Re-open workbook.xltm-->Workbook2-->etc

My worry is that the user will use the Workbook twice or more in one day and any VBA references I use to point to the opened template file will error (in particular, copy/paste macros).
What I'm looking to do is check what the filename is first before forming a reference towards it:
e.g.,
Check if filename = "Workbook" & x
i.e., were x loops from 1 to 5

I'm wondering if it is possible to check each potential workbook name to see if it's open, find the value of x and use that to reference the open template file?
Would anyone be willing to advise of a way of doing this please?

Kind regards,

Doug.

Public Function FileInUse(sFileName) As Boolean
On Error Resume Next
Open sFileName For Binary Access Read Lock Read As #1
Close #1
FileInUse = IIf(Err.Number > 0, True, False)
On Error GoTo 0
End Function


Sub Test_Sub()
myFilePath = "C:\Users\UserName\Desktop\example.xlsx"
If FileInUse(myFilePath) Then
MsgBox "File is Opened"
Else
MsgBox "File is Closed"
End If
End Sub

 

[JLGWhiz]

If just checking for any template file then the following would probably work, Of course there is more than one way to do it. If you want to check for a specific template then just use the workbook name in a statement and see if it errors. If it does not error, the workbook is open.

For Each wb In Workbooks
    If Right(wb.Name, 4) = "xltm" then
        MsgBox "Template open"
   End If
Next

 

[dougmarkham]

If just checking for any template file then the following would probably work, Of course there is more than one way to do it. If you want to check for a specific template then just use the workbook name in a statement and see if it errors. If it does not error, the workbook is open.

For Each wb In Workbooks
    If Right(wb.Name, 4) = "xltm" then
        MsgBox "Template open"
   End If
Next

Hi JLGWhiz,

Thanks for replying.

I tried the code with my the following file: DX Address Cleanup.xltm
When I double-click the file icon to open it, the filename of the open file becomes: "DX Address Cleanup1"

I ran your code and it didn't detect the open template file.

If instead, I open the template file like this from Excel...
File-->Open-->Browse and then opening DX Address Cleanup.xltm whilst holding down the shift key and clicking open.
When I run your code, this time it detects DX Address Cleanup.xltm.

So the issue I'm seeking an answer for is: how do I detect whether DX Address Cleanup1 is open.
Everytime I open DX Address Cleanup.xltm by double-click, the filename inceases by 1 e.g.,
First open: DX Address Cleanup1
2nd open: DX Address Cleanup2
3rd open: DX Address Cleanup3

I am looking to find a way of determining using VBA what the filename is.

Kind regards,

Doug.

 

[JLGWhiz]

Apparently you have some code that automatically saves the Template file with the numerical suffix to avoid user error of saving the template with data in it as a template. Sounds ridiculous, but that would be the only reason I know for having an automatic saveas macro. So you should not have to worry about the template itself being open. The macro that does the saveas takes care of that, because when the saveas occurs, the .xltm file estension should be removed and the file with the numerical suffix should have a xlsx or other file extension. The original template would be safely tucked away in its directory.