Excel Templates and databases

G

Guest

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
 
G

Guest

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 said:
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
Click to expand...
 
G

Guest

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 said:
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 said:
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
Click to expand...
Click to expand...
 
G

Guest

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 said:
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 said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Create Hyperlink to URL 4
database exclusive use by Admin 5
Syntax to reconnect a disconnected recordset? 4
SQL update statement in excel vba 4
UDF XLA and SQL queries 1
VBA Excel - Select a value from one cell in Excel to update a SQL table 1
Insert into SQl table 1
Return ADO recordset to worksheet 3

Top