K
KenCowen
I use the following code to open a file when a button on a userform is clicked:
'open template
Dim fn As String
Dim loc As String
Dim str As String
loc = Range("loc_template").Value
fn = Range("template").Value
On Error GoTo 200
str = loc & "\" & fn
Call OpenFile(str)
On Error GoTo 0
Unload Me
Exit Sub
200
MsgBox "Could not find " & str
End Sub
The code usually works fine; my problem is when I have modified and saved the "template"; which I do quite often. After the file has been saved and renamed, it will no longer open through the code. I adjust the range (range("template").value) to reflect the new name, but when I try to open it, thescreen turns white and I have to restart. I know it is identifying the file, as it shows in the Task Manager as an application (actually two applications) but they are "not responding" and my code does not trip the file not found error; it finds the file, it just can't open it.
If I open the file manually, save, and close it with the same new name, it will then open through the code. I can't figure out what keeps it from opening after it has been modified. It is not really a template file, but, it is a .xlsm file that I use as a template, and it has a lot going on it; including database connections to a historical data source. Those connections are made through code, and are always closed at the end of the code. It does not appear to have any connections open, and when I open it and close it manually, I do not need to do anything to the file to get it to once again be re-openable through the code.
Any ideas?
Thanks
Ken
'open template
Dim fn As String
Dim loc As String
Dim str As String
loc = Range("loc_template").Value
fn = Range("template").Value
On Error GoTo 200
str = loc & "\" & fn
Call OpenFile(str)
On Error GoTo 0
Unload Me
Exit Sub
200
MsgBox "Could not find " & str
End Sub
The code usually works fine; my problem is when I have modified and saved the "template"; which I do quite often. After the file has been saved and renamed, it will no longer open through the code. I adjust the range (range("template").value) to reflect the new name, but when I try to open it, thescreen turns white and I have to restart. I know it is identifying the file, as it shows in the Task Manager as an application (actually two applications) but they are "not responding" and my code does not trip the file not found error; it finds the file, it just can't open it.
If I open the file manually, save, and close it with the same new name, it will then open through the code. I can't figure out what keeps it from opening after it has been modified. It is not really a template file, but, it is a .xlsm file that I use as a template, and it has a lot going on it; including database connections to a historical data source. Those connections are made through code, and are always closed at the end of the code. It does not appear to have any connections open, and when I open it and close it manually, I do not need to do anything to the file to get it to once again be re-openable through the code.
Any ideas?
Thanks
Ken