Creating Excel Workbooks (2K) (2000/SP3)

Topic

New Reply

I am starting to work on a project, and would appreciate some comments on my ‘game plan’ before I start coding.

I have two tables – tblCountry and tblDetails. I want to select all of the records in tblDetails that are associated with each record in tblCountry, and output each country in a separate Excel Workbook.

Is the best way to do this to have two recordsets and use a bookmark to select the record in tblCountry, then take the Country name into the second recordset using WHERE clauses? I would then return to the first recordset, advance by one, update the second recordset, then export to a new Excel Workbook.

Does this make sense, or is there a better way of doing it? I appreciate the help as my programming to date has been with a single recordset.

TIA.

Kiwi44

Reply | Quote

Replies

You might consider importing into Excel instead of exporting from Access.

To export from Access, you can use DoCmd.TransferSpreadsheet or DoCmdOutputTo. Both only work with tables and stored queries; you can’t export a recordset. Excel has a CopyFromRecordset method (of the Range object) that imports a DAO or ADO recordset. You’d loop through a recordset based on tblCountry, and construct an SQL statement that filters tlDetails by country. Open a recordset from this SQL statement and use CopyFromRecordset to import it into Excel.

Reply | Quote

Thanks for the advice. I had found the following code (it took me a while to track down the KB number!) on the MS KB site (Microsoft Knowledge Base Article 210288) that works well on a query – I was thinking of adapting this to use a table and modify the RsSql= line to take the country name from the tblCty list. Is this not going to work?

The code is:

Dim DB As DAO.Database, Rs As DAO.Recordset
Dim i As Integer, j As Integer
Dim RsSql As String
Dim CurrentValue As Variant
Dim CurrentField As Variant
Dim Workbook As Object
Dim xlApp As Object
Dim Sheet As Object

Set DB = DBEngine.Workspaces(0).Databases(0)

RsSql = “SELECT * FROM [Order Details] WHERE [OrderId]< 10249;"

Set Rs = DB.OpenRecordset(RsSql, dbOpenDynaset)
Set xlApp = CreateObject("Excel.Application")
xlApp.workbooks.Add
Set Sheet = xlApp.activeworkbook.sheets(1)
j = 1

' Loop through the Microsoft Access field names and create
' the Microsoft Excel labels.
For i = 0 To Rs.Fields.Count – 1
CurrentValue = Rs.Fields(i).Name
Sheet.cells(j, i + 1).Value = CurrentValue
Next i

j = 2

' Loop through the Microsoft Access records and copy the records
' to the Microsoft Excel spreadsheet.
Do Until Rs.EOF
For i = 0 To Rs.Fields.Count – 1
CurrentField = Rs(i)
Sheet.cells(j, i + 1).Value = CurrentField
Next i
Rs.MoveNext
j = j + 1
Loop

' Print the Microsoft Excel spreadsheet.
Sheet.PrintOut

' Close workbook without saving.
xlApp.activeworkbook.saved = True
Set Sheet = Nothing
xlApp.Quit
Set xlApp = Nothing

Many, many TIA!

Kiwi44

Reply | Quote

Yes, you can adapt this code to your needs. You will have to write a double loop. The example from Microsoft loops through the records of a recordset to fill a worksheet; you would have to nest this inside a loop that processes each country. The code will be relatively slow, though, in particular if you have many records per country. Using TransferSpreadsheet in Access or CopyFromRecordset in Excel is much faster than looping through the records of a recordset.

Reply | Quote

Dear Hans,

Many thanks for this guidance. I ran the code, but found difficulties in determining how to save and close the Excel spreadsheets that I created. Can you tell me how to do this?

I then took a look at DoCmd.OutputTo, and had all sorts of problems with error messages, but resolved this by creating an empty query in my database. Then the following lines do the trick, where strLOC is the name of the country that I am filtering on:

fname = “C:Access” & strLOC & “.xls”
CurrentDb.QueryDefs(“qryTest”).SQL = RsSql_2
DoCmd.OutputTo acOutputQuery, “qryTest”, acFormatXLS, fname, True

I probably should reset the QueryDefs(“qryTest”).SQL to “” after running the DoCmd?

Works brilliantly, and now all I have to do is get the second SQL string right, and deal with the fact that several fields are truncated (an issue for Microsoft!!!) – which is why I need to do the longer method.

Many thanks – I have learned a lot already from this exercise!

Kiwi44

Reply | Quote

>> how to save and close the Excel spreadsheets that I created. Can you tell me how to do this?

This is about the sample code from Microsoft, I suppose? If so, replace

‘ Print the Microsoft Excel spreadsheet.
Sheet.PrintOut

‘ Close workbook without saving.
xlApp.activeworkbook.saved = True

by

fName = “C:Access” & strLOC & “.xls”
xlApp.ActiveWorkbook.Close SaveChanges:=True, FileName:=fName

Reply | Quote

>> how to save and close the Excel spreadsheets that I created. Can you tell me how to do this?

This is about the sample code from Microsoft, I suppose? If so, replace

‘ Print the Microsoft Excel spreadsheet.
Sheet.PrintOut

‘ Close workbook without saving.
xlApp.activeworkbook.saved = True

by

fName = “C:Access” & strLOC & “.xls”
xlApp.ActiveWorkbook.Close SaveChanges:=True, FileName:=fName

Reply | Quote

One note to add re: “deal with the fact that several fields are truncated (an issue for Microsoft!!!) – which is why I need to do the longer method.” If you are exporting any memo fields with text greater than 255 characters in length, any text in excess of 255 character will be truncated when using OutputTo command (equivalent of “Analyze It With MS Excel” menu command). As noted in MSKB Article 208801 – ACC2000: Memo Field Truncated When Report Is Output to Excel, this is because “In Excel, the maximum length of text-cell contents is 32,000 characters. However, Access outputs a report to Excel 5.0/95 format, in which the maximum length of text-cell contents is 255.” The article provides a kludgey, cumbersome workaround. The best way to get around this limitation is to use the Excel CopyFromRecordset method, as previously noted by HansV. This would be far more efficient than looping thru recordset & copying data one cell at time in Excel. Simple example of how to do this in Access:

Public Sub TestCopyFromRecordset()
On Error GoTo Err_Handler

Dim xl As Excel.Application
Dim wb As Excel.Workbook
Dim ws As Excel.Worksheet
Dim rst As ADODB.Recordset
Dim strSQL As String
Dim strMsg As String
Dim intCol As Integer

Set xl = New Excel.Application
With xl
.Visible = False
.Application.ScreenUpdating = False
.DisplayAlerts = False
End With

Set wb = xl.Workbooks.Add
Set ws = wb.Worksheets(1)

strSQL = “SELECT FIELD1, FIELD2, FIELD3 FROM TABLE1 ORDER BY FIELD1;”

Set rst = New ADODB.Recordset
rst.Open strSQL, CurrentProject.Connection, adOpenStatic, adLockOptimistic

‘ Copy field names to the first row of the worksheet:
For intCol = 1 To rst.Fields.Count
ws.Cells(1, intCol).Value = rst.Fields(intCol – 1).Name
Next

With ws
With .Range(“A1”).CurrentRegion
.Font.Bold = True
.HorizontalAlignment = xlCenter
End With
.Range(“A2”).CopyFromRecordset rst
End With
rst.Close
wb.SaveAs “C:AccessTestCopyFromRecordset.xls”

With xl
.Visible = True
.Application.ScreenUpdating = True
.DisplayAlerts = True
End With

Exit_Sub:
Set xl = Nothing
Set wb = Nothing
Set ws = Nothing
Set rst = Nothing
Exit Sub
Err_Handler:
Select Case Err.Number
Case 0
Resume Next
Case Else
strMsg = “Error No ” & Err.Number & “: ” & Err.Description
MsgBox strMsg, vbExclamation, “EXPORT TO EXCEL – UNEXPECTED ERROR”
If Not xl Is Nothing Then xl.Quit
Resume Exit_Sub
End Select

End Sub

Note that when testing code above with table with numerous large memo fields, the memo text was NOT truncated when it exceeded 255 characters. For more information on CopyFromRecordset method, look up in Excel VBA Help. Also see MSKB 247412 – INFO: Methods for Transferring Data to Excel from Visual Basic

HTH

Reply | Quote

Wonderful! That will save an enormous amount of time!

Just one final question, if I may. I have set up a macro to run in Excel – is it better to run the macro (if so, how do I get the code to find the Personal worksheet and run the macro), or execute the same code from within Access?

Thanks again,

Kiwi44

Reply | Quote

When exporting data to Excel from Access, I always run all code from Access. Among other reasons, the users using program are highly unlikely to have the same macros in their copy of PERSONAL.XLS as I have in mine. Also, when creating new instance of Excel as shown in example, neither PERSONAL.XLS or any add-ins that normally load when you start Excel “manually” will be open unless you specify otherwise. If you are running this code on your machine only, and want to run a macro that’s defined in your PERSONAL.XLS file, you can add code like this to export procedure. For example, I have a macro that applies some default formatting to a range of data exported from Access. Modified code:

With ws
‘ Use macro to apply formatting, comment this out:
‘ With .Range(“A1”).CurrentRegion
‘ .Font.Bold = True
‘ .HorizontalAlignment = xlCenter
‘ .Columns.AutoFit
‘ End With
.Range(“A2”).CopyFromRecordset rst
End With
rst.Close

strPath = “C:WINDOWSApplication DataMicrosoftExcelXLSTARTPERSONAL.XLS”
xl.Workbooks.Open strPath
xl.Run (“PERSONAL.XLS!modFormat.AccessExportReformat”)
‘ etc

Note that full path of PERSONAL.XLS needs to be specified – there’s probably more than one copy of this file floating around on your pc, be sure to specify correct one. The path above is typical for Excel 2K on WIN 98. After opening file, you can use Excel Application Run method to run macro. Note syntax – to be safe I specified module name (modFormat) as well as name of macro (AccessExportReformat) – if you have more than one macro with same name in different modules you need to specify module name. As noted, I do not normally use this approach. My preference is to create an Excel template file (.XLT extension) with predefined formatting, headers, etc and export the data to a new workbook based on the template. You do this by specifying path and name of template file when opening new workbook:

Set wb = xl.Workbooks.Add(“C:ExcelMyTemplate.xlt”)

This opens new workbook based on template (it does not open template file directly) to export data to.

HTH

Reply | Quote

When exporting data to Excel from Access, I always run all code from Access. Among other reasons, the users using program are highly unlikely to have the same macros in their copy of PERSONAL.XLS as I have in mine. Also, when creating new instance of Excel as shown in example, neither PERSONAL.XLS or any add-ins that normally load when you start Excel “manually” will be open unless you specify otherwise. If you are running this code on your machine only, and want to run a macro that’s defined in your PERSONAL.XLS file, you can add code like this to export procedure. For example, I have a macro that applies some default formatting to a range of data exported from Access. Modified code:

With ws
‘ Use macro to apply formatting, comment this out:
‘ With .Range(“A1”).CurrentRegion
‘ .Font.Bold = True
‘ .HorizontalAlignment = xlCenter
‘ .Columns.AutoFit
‘ End With
.Range(“A2”).CopyFromRecordset rst
End With
rst.Close

strPath = “C:WINDOWSApplication DataMicrosoftExcelXLSTARTPERSONAL.XLS”
xl.Workbooks.Open strPath
xl.Run (“PERSONAL.XLS!modFormat.AccessExportReformat”)
‘ etc

Note that full path of PERSONAL.XLS needs to be specified – there’s probably more than one copy of this file floating around on your pc, be sure to specify correct one. The path above is typical for Excel 2K on WIN 98. After opening file, you can use Excel Application Run method to run macro. Note syntax – to be safe I specified module name (modFormat) as well as name of macro (AccessExportReformat) – if you have more than one macro with same name in different modules you need to specify module name. As noted, I do not normally use this approach. My preference is to create an Excel template file (.XLT extension) with predefined formatting, headers, etc and export the data to a new workbook based on the template. You do this by specifying path and name of template file when opening new workbook:

Set wb = xl.Workbooks.Add(“C:ExcelMyTemplate.xlt”)

This opens new workbook based on template (it does not open template file directly) to export data to.

HTH

Reply | Quote

Wonderful! That will save an enormous amount of time!

Just one final question, if I may. I have set up a macro to run in Excel – is it better to run the macro (if so, how do I get the code to find the Personal worksheet and run the macro), or execute the same code from within Access?

Thanks again,

Kiwi44

Reply | Quote

One note to add re: “deal with the fact that several fields are truncated (an issue for Microsoft!!!) – which is why I need to do the longer method.” If you are exporting any memo fields with text greater than 255 characters in length, any text in excess of 255 character will be truncated when using OutputTo command (equivalent of “Analyze It With MS Excel” menu command). As noted in MSKB Article 208801 – ACC2000: Memo Field Truncated When Report Is Output to Excel, this is because “In Excel, the maximum length of text-cell contents is 32,000 characters. However, Access outputs a report to Excel 5.0/95 format, in which the maximum length of text-cell contents is 255.” The article provides a kludgey, cumbersome workaround. The best way to get around this limitation is to use the Excel CopyFromRecordset method, as previously noted by HansV. This would be far more efficient than looping thru recordset & copying data one cell at time in Excel. Simple example of how to do this in Access:

Public Sub TestCopyFromRecordset()
On Error GoTo Err_Handler

Dim xl As Excel.Application
Dim wb As Excel.Workbook
Dim ws As Excel.Worksheet
Dim rst As ADODB.Recordset
Dim strSQL As String
Dim strMsg As String
Dim intCol As Integer

Set xl = New Excel.Application
With xl
.Visible = False
.Application.ScreenUpdating = False
.DisplayAlerts = False
End With

Set wb = xl.Workbooks.Add
Set ws = wb.Worksheets(1)

strSQL = “SELECT FIELD1, FIELD2, FIELD3 FROM TABLE1 ORDER BY FIELD1;”

Set rst = New ADODB.Recordset
rst.Open strSQL, CurrentProject.Connection, adOpenStatic, adLockOptimistic

‘ Copy field names to the first row of the worksheet:
For intCol = 1 To rst.Fields.Count
ws.Cells(1, intCol).Value = rst.Fields(intCol – 1).Name
Next

With ws
With .Range(“A1”).CurrentRegion
.Font.Bold = True
.HorizontalAlignment = xlCenter
End With
.Range(“A2”).CopyFromRecordset rst
End With
rst.Close
wb.SaveAs “C:AccessTestCopyFromRecordset.xls”

With xl
.Visible = True
.Application.ScreenUpdating = True
.DisplayAlerts = True
End With

Exit_Sub:
Set xl = Nothing
Set wb = Nothing
Set ws = Nothing
Set rst = Nothing
Exit Sub
Err_Handler:
Select Case Err.Number
Case 0
Resume Next
Case Else
strMsg = “Error No ” & Err.Number & “: ” & Err.Description
MsgBox strMsg, vbExclamation, “EXPORT TO EXCEL – UNEXPECTED ERROR”
If Not xl Is Nothing Then xl.Quit
Resume Exit_Sub
End Select

End Sub

Note that when testing code above with table with numerous large memo fields, the memo text was NOT truncated when it exceeded 255 characters. For more information on CopyFromRecordset method, look up in Excel VBA Help. Also see MSKB 247412 – INFO: Methods for Transferring Data to Excel from Visual Basic

HTH

Reply | Quote

Dear Hans,

Many thanks for this guidance. I ran the code, but found difficulties in determining how to save and close the Excel spreadsheets that I created. Can you tell me how to do this?

I then took a look at DoCmd.OutputTo, and had all sorts of problems with error messages, but resolved this by creating an empty query in my database. Then the following lines do the trick, where strLOC is the name of the country that I am filtering on:

fname = “C:Access” & strLOC & “.xls”
CurrentDb.QueryDefs(“qryTest”).SQL = RsSql_2
DoCmd.OutputTo acOutputQuery, “qryTest”, acFormatXLS, fname, True

I probably should reset the QueryDefs(“qryTest”).SQL to “” after running the DoCmd?

Works brilliantly, and now all I have to do is get the second SQL string right, and deal with the fact that several fields are truncated (an issue for Microsoft!!!) – which is why I need to do the longer method.

Many thanks – I have learned a lot already from this exercise!

Kiwi44

Reply | Quote

Yes, you can adapt this code to your needs. You will have to write a double loop. The example from Microsoft loops through the records of a recordset to fill a worksheet; you would have to nest this inside a loop that processes each country. The code will be relatively slow, though, in particular if you have many records per country. Using TransferSpreadsheet in Access or CopyFromRecordset in Excel is much faster than looping through the records of a recordset.

Reply | Quote

Thanks for the advice. I had found the following code (it took me a while to track down the KB number!) on the MS KB site (Microsoft Knowledge Base Article 210288) that works well on a query – I was thinking of adapting this to use a table and modify the RsSql= line to take the country name from the tblCty list. Is this not going to work?

The code is:

Dim DB As DAO.Database, Rs As DAO.Recordset
Dim i As Integer, j As Integer
Dim RsSql As String
Dim CurrentValue As Variant
Dim CurrentField As Variant
Dim Workbook As Object
Dim xlApp As Object
Dim Sheet As Object

Set DB = DBEngine.Workspaces(0).Databases(0)

RsSql = “SELECT * FROM [Order Details] WHERE [OrderId]< 10249;"

Set Rs = DB.OpenRecordset(RsSql, dbOpenDynaset)
Set xlApp = CreateObject("Excel.Application")
xlApp.workbooks.Add
Set Sheet = xlApp.activeworkbook.sheets(1)
j = 1

' Loop through the Microsoft Access field names and create
' the Microsoft Excel labels.
For i = 0 To Rs.Fields.Count – 1
CurrentValue = Rs.Fields(i).Name
Sheet.cells(j, i + 1).Value = CurrentValue
Next i

j = 2

' Loop through the Microsoft Access records and copy the records
' to the Microsoft Excel spreadsheet.
Do Until Rs.EOF
For i = 0 To Rs.Fields.Count – 1
CurrentField = Rs(i)
Sheet.cells(j, i + 1).Value = CurrentField
Next i
Rs.MoveNext
j = j + 1
Loop

' Print the Microsoft Excel spreadsheet.
Sheet.PrintOut

' Close workbook without saving.
xlApp.activeworkbook.saved = True
Set Sheet = Nothing
xlApp.Quit
Set xlApp = Nothing

Many, many TIA!

Kiwi44

Reply | Quote

You might consider importing into Excel instead of exporting from Access.

To export from Access, you can use DoCmd.TransferSpreadsheet or DoCmdOutputTo. Both only work with tables and stored queries; you can’t export a recordset. Excel has a CopyFromRecordset method (of the Range object) that imports a DAO or ADO recordset. You’d loop through a recordset based on tblCountry, and construct an SQL statement that filters tlDetails by country. Open a recordset from this SQL statement and use CopyFromRecordset to import it into Excel.

Reply | Quote