Excel Templates and databases
 

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

Excel Templates and databases
 

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

Excel Templates and databases
 

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

Excel Templates and databases
 

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