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

    Insert Sheet From Access to Existing Excel file (03)

    This may be answered else where, but I am unable to locate it, so I will ask here and maybe someone can send me to the right location.
    I have an excel spreadsheet and I want to insert a new page and populate the page with data from an existing query in Access. How do you insert a new page into excel, and then put the data into it, in general terms. Or is there a sample some place that I could use as a starting point?

    Thanks!
    Ken

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

    Re: Insert Sheet From Access to Existing Excel file (03)

    The easiest way is to use DoCmd.TransferSpreadsheet:

    DoCmd.TransferSpreadsheet acExport,acSpreadsheetTypeExcel8, "qryTest", "C:ExcelTest.xls"

    will do the following:
    - If the workbook C:ExcelTest.xls doesn't exist yet, it will be created. If it already exists, the existing workbook will be used.
    - If a worksheet qryTest doesn't exist yet in that workbook, it will be added to the workbook. If it already exists, the existing worksheet will be used.
    - The results of qryTest will be placed in the worksheet of the same name (updating existing content if necessary).

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

    Re: Insert Sheet From Access to Existing Excel file (03)

    Thanks! Is there a way to control "WHERE" it will be placed among other existing worksheets?

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

    Re: Insert Sheet From Access to Existing Excel file (03)

    DoCmd.TransferSpreadsheet doesn't offer any control over that. You'd have to use Automation to reorganize the sheets in the workbook.

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

    Re: Insert Sheet From Access to Existing Excel file (03)

    Hans,

    Thanks again for your help. I can alter the order that I export the sheets to excel and get the desired result. I guess I was trying to make it too complicated.

    Once I export the sheets to one Excel file, or as I export them, I would like to format the font and column width, etc. I use the following code to format one sheet in one file, but would like to use the same code repetively to format the individual sheets. Can you tell me what changes I would need to make to the following code to format a specific sheet in the file?

    Function FormatSpreadsheet(strFullPath As String, strFileName As String, strSheetName 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_FormatSpreadsheet

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

    ' Open the spreadsheet with the exported data.
    Set xlWrkbk = xlApp.Workbooks.Open(strFullPath)

    ' Determine the size of the range and store it.
    Set xlSourceRange = xlWrkbk.Worksheets(strSheetName).Range("a1").Curre ntRegion

    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
    ''''''''''''''''''''''''''''''''''''''''''''
    End With
    ''''''''''''''''''''''''''''''''''''''''''''''''

    Exit_FormatSpreadsheet:
    Set xlSourceRange = Nothing
    Set xlColPoint = Nothing
    Set xlChartObj = Nothing
    ' Close and save the workbook
    xlWrkbk.Close SaveChanges:=True
    Set xlWrkbk = Nothing
    ' Quit Excel
    xlApp.Quit
    Set xlApp = Nothing
    Exit Function

    Err_FormatSpreadsheet:
    MsgBox CStr(Err) & " " & Err.Description
    Resume Exit_FormatSpreadsheet

    End Function

    Thanks!

    Ken

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

    Re: Insert Sheet From Access to Existing Excel file (03)

    Instead of xlWrkbk.Worksheets(1), use xlWrkbk.Worksheets(strSheetName)

Posting Permissions

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