Tom, I answered my own question. Yes, doing an ActiveWorkbook.Save right in
the beginning solved the problem.

Appreciate your advice. (Was something I'd never considered before.)

What makes it a little more hairy - hard to debug since this is both a
template *and* a workbook_open event ...

Thanks again,
Chad


"ChadF" wrote:

Would something like this at the beginning of the workbook_open event correct
for this ? ...

before the call to initializeTableFill(),
ActiveWorkbook.Save
(force the save to something ?)




"Tom Ogilvy" wrote:

At least part of your problem is probably in this line

MyDatabaseFilePathAndName = ThisWorkbook.Path

when a new workbook is created from a template, it doesn't have a path since
it has never been saved.

--
Regards,
Tom Ogilvy


"ChadF" wrote:

I have a VBA / Excel application that does the the following:
in the Workbook_Open event, I query a MS Access database to
populate a hidden worksheet. (Have a number of named ranges
on my user's visible worksheet that are dependent on values that
I populate from the hidden.)

During the Worksheet_Change event, I have a function that queries
the same database file for lookup information once the user selects
some criterion.

I'd like to set up a blank form for my users and then make this .xls
file into a template. However, when I do this, I get errors in copying
data on the worksheet_open. System behaves as if it doesnt know the
database file is even there.

The initialization code is nothing out of the ordinary... (defined this in
a separate module... body of the Workbook_Open event is
Call InitializeRoutines.InitialTableFill

Public Sub InitialTableFill()

Dim MyConnection As String
Dim MySQL As String
Dim MyDatabase As Object

Dim MyDatabaseFilePathAndName As String
MyDatabaseFilePathAndName = ThisWorkbook.Path & "\Statics.mdb"

'Create connection string

MyConnection = "Provider=Microsoft.Jet.OLEDB.4.0;"
MyConnection = MyConnection & "Data Source=" &
MyDatabaseFilePathAndName & ";"

Sheets("Statics").Range("A:D").ClearContents

MySQL = "Select [Company] FROM Company"

' Open the database and copy the data
On Error GoTo SomethingWrong
Set MyDatabase = CreateObject("adodb.recordset")
MyDatabase.Open MySQL, MyConnection, 0, 1, 1

' Check to make sure we received data and copy the data

If Not MyDatabase.EOF Then

Sheets("Statics").Range("A:A").CopyFromRecordset MyDatabase

End If

MyDatabase.Close
Set MyDatabase = Nothing

'Create connection string

MySQL = "Select [Labor Category] FROM Labor ORDER BY [Labor Category]"

' Open the database and copy the data
On Error GoTo SomethingWrong
Set MyDatabase = CreateObject("adodb.recordset")
MyDatabase.Open MySQL, MyConnection, 0, 1, 1

Debug.Print "Inside the database query function ... connection seems good"

' Check to make sure we received data and copy the data

If Not MyDatabase.EOF Then

Sheets("Statics").Range("B:B").CopyFromRecordset MyDatabase

End If

MyDatabase.Close
Set MyDatabase = Nothing

TTO_Request_Form.Activate

Exit Sub

SomethingWrong:
On Error GoTo 0
Set MyDatabase = Nothing
MsgBox "Error copying data", vbCritical, "Test Access data to Excel"

End Sub

If I do this as an .xlt file, it does *NOT* work. If I do this as an .xls
file it
works fine. Is it possible to make this a template ? I did humor the prompt
message from Microsoft asking to refresh the data every time it's opened.
This
method didnt work.

I'd appreciate any advice.

Thanks in advance,
Chad