Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2007

    Unanswered: exporting tables to one excel sheet

    hello freinds !!

    i have 2 tables to export to excelsheet
    here is the brief summary/procedure i followed

    1. export table to excel using
    Docmd.outputto,..... "queryname",Makefilename()

    i am taking mmddyy out of this function for naming the excel file i open this excel file in access through excel automation and do some formating of the worksheet

    i want to export another table from access to the same worksheet above.
    i make use of another outputo

    and i am getting the error as file coudnt not be open .. or if i try the other way around then its over writing on the sheet1.

    ** rembr evrything is being done on a single button click from access****

    is there any possible way to export another access table to the same worksheet which i created.

    the purpose of creating another sheet is to perform excel automation from access on the workbook again.

    this is eating my brain and i am clueless !!
    any help would be appreciated..


  2. #2
    Join Date
    Apr 2004
    metro Detroit
    It sounds like you are not closing the xls file after formatting and trying to open it with the second outputto. If you post your code we will be better able to assist you.

  3. #3
    Join Date
    Nov 2007

    excel automation

    thanks for the reply !!!

    well i have solved the problem of creating a new sheet in the same workbook but lead into a new problem now i.e i am unable to export to the sheet1

    here is my code :

    Function GetChanges()
    path = MakeFileName()
    'Create the Excel Object

    Dim FName As String
    Dim PArray() As String
    Dim appXL As Excel.Application
    Dim wkbXL As Excel.workBook
    Dim wksXL As Excel.workSheet
    Dim wksPivot As Excel.workSheet
    Dim objXLRange As Excel.Range

    Set appXL = New Excel.Application
    Set wkbXL = appXL.Workbooks.Open(path)
    ''Set wkbXL = GetObject(path)
    Set wksXL = wkbXL.Worksheets.Add

    With wksXL

    wksXL.DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "tbl_cross_compare1", "path", False, "Sheet1"

    End With

    Set objXLRange = Nothing
    Set wksPivot = Nothing
    Set wksXL = Nothing
    Set wkbXL = Nothing
    Set appXL = Nothing
    End Function


    the error lies in the line
    with wksXL
    ... ??


Posting Permissions

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