Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Report/Data Exchange Access/Excel (Office 2003)

    Do you want to do this using VBA?

  2. #2
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Report/Data Exchange Access/Excel (Office 2003)

    I would LOVE to do it using VBA.

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

    Re: Report/Data Exchange Access/Excel (Office 2003)

    A good way to learn the basics of Excel VBA is to record a macro (Tools | Macro | Record New Macro...) with some of the actions you need, then to look at the generated code.
    You can click in any keyword and press F1 to get help on the keyword, usually with an example and with "See Also" links.

    A recorded macro always acts on the selected cells. In a production macro, you will often change it to act on a specific range. A useful property is CurrentRegion. This expands a cell to the entire contiguous area that contains the cell.

    For example, to turn off word wrap, the macro recorder will generate
    <code>
    Selection.WrapText = False
    </code>
    You might change this to
    <code>Range("A1").CurrentRegion.WrapText = False
    </code>
    This will turn off word wrap for the entire contiguous area containing A1.

  4. #4
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Report/Data Exchange Access/Excel (Office 2003)

    Hans,

    I may have found a partial fix. I can "Export" a version of report in Access to Excel. It leaves less clean up, though I have yet to determine how to export it in VBA. Once exported, I think I can use your idea to create VBA code by recording a macro and then using the resulting code in Access using automation. However, this is pure speculation on my part for now.

    Thanks for all your help.

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

    Re: Report/Data Exchange Access/Excel (Office 2003)

    To export from Access, you can use DoCmd.TransferSpreadsheet for tables and queries:
    <code>
    DoCmd.TransferSpreadsheet TransferType:=acExport, SpreadsheetType:=acSpreadsheetTypeExcel9, _
    TableName:="tblTest", FileName:="C:ExcelTest.xls", HasFieldNames:=True
    </code>
    You can also use DoCmd.OutputTo; this works for forms and reports too, but it results in an Excel 95 type workbook.
    It is entirely possible to use an Excel macro to format the resulting worksheet.

    To import into Excel, you can use Data | Get External Data | New Database Query...
    Once you have done that, the data can be refreshed manually or automatically.

  6. #6
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Report/Data Exchange Access/Excel (Office 2003)

    Hans,

    I have used the docmd.transferspreadsheet before. However, in this case I want to export/transfer a report, instead of a table or query. Is there a way to automate that using a similar command?

    Thanks in advance for your help.

    Ken

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

    Re: Report/Data Exchange Access/Excel (Office 2003)

    As I mentioned, you can use DoCmd.OutputTo for that, but that command leaves a lot to be desired. Only the text of the report will be exported, none of the graphics elements, and the formatting of the generated worksheet can be a bit odd. But you can correct that using an Excel macro, of course.

  8. #8
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Report/Data Exchange Access/Excel (Office 2003)

    Hans,

    That was my preferred approach (exporting the existing report and doing the clean up in Excel). However, I get "There are too many rows to output, based on the limitation specified by the output format or Microsoft Office Access" when I run the following code in vb.

    DoCmd.OutputTo acOutputReport, "rptDetailExport", acFormatXLS, "C:SpreadsheetsMay_Details.xls"

    I can export it manually via the menu structure, and it works and it looks pretty good, only needing some minor adjustments.
    If you see nothing wrong with the above syntax, I'll assume the only way to accomplish the formatting will be in Excel.

    Thanks for your thoughts.

    Ken

  9. #9
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Report/Data Exchg Access/Excel (Office 2003)

    This is my first post to the Excel forum. If this is in the wrong place, please accept my apology.

    My challenge relates to getting data from Microsoft Access into a formated Excel spreadsheet, AND include subtotals based on a change in the data in the first column. I have several thousand records in an Access query that succesfully exports to an Excel spreadsheet. Where do I start to find information on how to:
    format the fonts
    format the cell properties to not wrap and to autofit
    insert a row after a change in the first column (for example column 1 changes from Blue to Brown, I need row inserted and formula inserted in correct columns to sum or count the data)

    Any help in pointing me in the right direction is greatly appreciated.

    Ken

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

    Re: Report/Data Exchange Access/Excel (Office 2003)

    The problem is that DoCmd.OutputTo uses the Excel 5.0/95 format that only allows 16,384 rows in a sheet. Interactively, you can specify Excel 97-2003 as format (max 65,536 rows), but this option is not available in VBA.
    Try exporting fewer records.

  11. #11
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Report/Data Exchange Access/Excel (Office 2003)

    Hans,

    Exporting fewer records was not an option. I am going with your idea of exporting the query instead of the report and doing all the cleanup and summary in Excel. So far I have been able to reformat the cells, the fonts, and the column widths.

    Being brand new to vba in Excel, I am at a loss at comparing values within two contiguous cells. Column A is sorted alphabetically. When it finds the a change in the first column, I need to insert a row, then continue down until it finds the next change and insert a row until the last record. How would I loop through through the records in the column and identify the change?
    I think I can use the Do Until loop, but don't know how to refer to the two contiguous cells to make the comparison. Does that make sense?

    Thanks,
    Ken

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

    Re: Report/Data Exchange Access/Excel (Office 2003)

    Actually, you loop backwards, because if you're inserting or deleting rows while moving downwards, you're changing your "target". Here is some sample code, with comments:

    ' Row number
    Dim r As Long
    ' Max row number
    Dim m As Long
    ' Don't update screen while running
    Application.ScreenUpdating = False
    ' Last row
    m = Range("A65536").End(xlUp).Row
    ' Loop backwards
    For r = m To 3 Step -1
    ' Compare cell to cell above it
    If Not Range("A" & (r - 1)) = Range("A" & r) Then
    ' If not equal, insert blank row
    Range("A" & r).EntireRow.Insert
    End If
    Next r
    ' Resume screen updating
    Application.ScreenUpdating = True
    End Sub

  13. #13
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Report/Data Exchange Access/Excel (Office 2003)

    Hans,

    Thanks for code. I took the code and pasted it into the Access module that I am using to edit the spreadsheet. When stepping through your loop, I put a break at the line where the insert takes place, and it definitely it finds the correct two consecutive cells. .) However, it is not inserting a row. Is there something wrong in the syntax? (NOTE: I am reusing some code from a year ago when I was working on creating a chart in Excel through Access

    Function CreateChart(strSourceName As String, strFileName As String, strChartTitle As String, strGraphName As String)
    Dim xlWrkbk As Excel.Workbook
    Dim xlChartObj As Excel.Chart
    Dim xlSourceRange As Excel.Range
    Dim xlColPoint As Excel.Point
    Dim xlApp As Excel.Application
    On Error GoTo Err_CreateChart

    ' Create an Excel workbook file based on the
    ' object specified in the second argument.
    'THIS CREATES THE SPREADSHEET
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
    strSourceName, strFileName, False

    ' Create a Microsoft Excel object.
    Set xlApp = CreateObject("Excel.Application")

    ' Open the spreadsheet to which you exported the data.
    Set xlWrkbk = xlApp.Workbooks.Open(strFileName)

    ' Determine the size of the range and store it.
    Set xlSourceRange = xlWrkbk.Worksheets(strSourceName).Range("a1").Curr entRegion
    ' Added to format the font type and size and the column width 5/29/07
    Cells.Select
    Selection.Columns.AutoFit
    With Selection.Font
    .Name = "Arial"
    .FontStyle = "Regular"
    .Size = 8
    .Strikethrough = False
    .Superscript = False
    .Subscript = False
    .OutlineFont = False
    .Shadow = False
    .Underline = xlUnderlineStyleNone
    .ColorIndex = xlAutomatic
    End With

    ''''''''''''''''''''''''''''''''''''''''''''''''''
    'Thanks to Hans V for following
    ' Row number
    Dim r As Long
    ' Max row number
    Dim m As Long
    ' Don't update screen while running
    'Application.ScreenUpdating = False '(Removed this line. Error when compiling)
    ' Last row
    m = Range("A65536").End(xlUp).Row
    ' Loop backwards
    For r = m To 3 Step -1
    ' Compare cell to cell above it
    If Not Range("A" & (r - 1)) = Range("A" & r) Then
    ' If not equal, insert blank row
    Range("A" & r).EntireRow.Insert
    End If
    Next r
    ' Resume screen updating
    'Application.ScreenUpdating = True '(Removed this line. Error when compiling)

    ''''''''''''''''''''''''''''''''''''''''''''''''''


    Exit_CreateChart:
    Set xlSourceRange = Nothing
    Set xlColPoint = Nothing
    Set xlChartObj = Nothing
    Set xlWrkbk = Nothing
    Exit Function

    Err_CreateChart:
    MsgBox CStr(Err) & " " & Err.Description
    Resume Exit_CreateChart
    End Function

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

    Re: Report/Data Exchange Access/Excel (Office 2003)

    You are using Automation to control Excel from Access. When using Automation, it is <big><big>absolutely essential</big></big> to qualify all Excel objects explicitly as belonging to the Excel application object or an object derived from it. The line
    <code>
    Set xlWrkbk = xlApp.Workbooks.Open(strFileName)
    </code>
    is correct, since you explicitly qualify Workbooks as belonging to the xlApp object. But a line such as
    <code>
    Cells.Select
    </code>
    will cause problems, since you don't specify what it belongs too. In all probability, you will create a new instance of Excel that will remain in memory after you quit xlApp. You should use code like this:
    <code>
    xlWrkbk.Worksheets(1).Cells.Select
    </code>
    and
    <code>
    xlApp.Selection.Columns.AutoFit
    </code>
    etc.

    As an additional note, it is rarely necessary to select cells - it is more efficient to work with ranges. For example:
    <code>
    With xlWrkbk.Worksheets(1)
    .Columns.AutoFit
    With .Cells.Font
    .Name = "Arial"
    ...
    End With
    End With
    </code>
    Note that the . before various objects connects them to the object mentioned in With ...

    So make sure <big><big>never</big></big> to use Range, Cells, Selection, etc. without something before it. And instead of Application (if it refers to Excel), use your Excel Application variable, in your code xlApp:
    <code>
    xlApp.ScreenUpdating = False
    </code>
    If you use the word Application, VBA assumes that it is the Access Application.

  15. #15
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Report/Data Exchange Access/Excel (Office 2003)

    Hans,

    Thanks again for the code suggestions. You are dead on when you said it opened an instance of Exel and not close it. Before I modified the code, I was getting two instances of excel (hidden) which I had to shut down using Windows Task Manager. Now I get one instance, and it too must be manually shut down. What am I doing wrong?

    Dim xlWrkbk As Excel.Workbook
    Dim xlChartObj As Excel.Chart
    Dim xlSourceRange As Excel.Range
    Dim xlColPoint As Excel.Point
    Dim xlApp As Excel.Application
    On Error GoTo Err_CreateChart

    ' Create an Excel workbook file based on the
    ' object specified in the second argument.
    'THIS CREATES THE SPREADSHEET
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
    strSourceName, strFileName, False

    ' Create a Microsoft Excel object.
    Set xlApp = CreateObject("Excel.Application")

    ' Open the spreadsheet to which you exported the data.

    Set xlWrkbk = xlApp.Workbooks.Open(strFileName)
    'OK per Hans to here


    ' Determine the size of the range and store it.
    Set xlSourceRange = xlWrkbk.Worksheets(strSourceName).Range("a1").Curr entRegion

    xlWrkbk.Worksheets(1).Cells.Select
    ''''''''''''''''''''''''
    With xlWrkbk.Worksheets(1)
    .Columns.AutoFit
    With .Cells.Font
    .Name = "Arial"
    .FontStyle = "Regular"
    .Size = 8
    .Strikethrough = False
    .Superscript = False
    .Subscript = False
    .OutlineFont = False
    .Shadow = False
    .Underline = xlUnderlineStyleNone
    .ColorIndex = xlAutomatic

    End With
    xlApp.Selection.Columns.AutoFit
    xlApp.Selection.Rows.AutoFit

    ''''''''''''''''''''''''''''''''''''''''''''''''''
    'Thanks to Hans V
    ' Row number
    Dim r As Long
    ' Max row number
    Dim m As Long

    ' Don't update screen while running
    xlApp.ScreenUpdating = False
    ' Last row
    m = Range("A65536").End(xlUp).Row
    ' Loop backwards
    For r = m To 3 Step -1
    ' Compare cell to cell above it
    If Not Range("A" & (r - 1)) = Range("A" & r) Then
    ' If not equal, insert blank row
    Range("A" & r).EntireRow.Insert
    End If
    Next r
    ' Resume screen updating
    xlApp.ScreenUpdating = True
    End With

    ''''''''''''''''''''''''''''''''''''''''''''''''
    Thanks in advance for your consideration.

    Ken

Page 1 of 2 12 LastLast

Posting Permissions

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