problem opening file thru code

  • Thread starter Thread starter KenCowen
  • Start date Start date
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
 
Pascal

It doesn't say much, probably should just be another line in the listbox code I already posted.


Sub OpenFile(s)
Workbooks.Open Filename:=s
End Sub

Ken
 
Back
Top