Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    Join Date
    Jul 2003
    Location
    Newtown, Pennsylvania, USA
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    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.

  3. #3
    2 Star Lounger
    Join Date
    Jul 2003
    Location
    Newtown, Pennsylvania, USA
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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 (<!mskb=210288>Microsoft Knowledge Base Article 210288<!/mskb>) 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

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    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.

  5. #5
    2 Star Lounger
    Join Date
    Jul 2003
    Location
    Newtown, Pennsylvania, USA
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    >> 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

  7. #7
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

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

    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

  8. #8
    2 Star Lounger
    Join Date
    Jul 2003
    Location
    Newtown, Pennsylvania, USA
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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

  9. #9
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

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

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •