Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2012
    Posts
    126

    Unanswered: Exporting to excel. Opening workbook and then editing it...failing

    Like the title says I am trying to push a table out to excel. Then open the excel workbook and edit it. I used an excel macro to create the editing part in excel and think I converted it right.

    Code:
    Private Sub cmdtest_Click()
    
    Transfer the table
        DoCmd.TransferSpreadsheet acExport, 8, "tbltest", "C:\users\student\desktop\book1.xls"
        
        Dim oXL As Object
        Dim oExcel As Object
        Dim sFullPath As String
        Dim sPath As String
      
        
         
         '   Create a new Excel instance
        Set oXL = CreateObject("Excel.Application")
        ' point of failure
        Set Worksheet = oXL.Worksheets
        
         
         '   Full path of excel file to open
    
        sFullPath = "C:\users\student\desktop\book1.xls"
         
         
         '  Open it
        With oXL
            .Visible = True
            .Workbooks.Open (sFullPath)
        End With
        ' edit the data
        With Worksheet
        .Range(Selection, Selection.End(xlToRight)).Select
        .Range(Selection, Selection.End(xlDown)).Select
        .ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$H$5"), , xlYes).Name = _
            "Table1"
        .Range("A1:H5").Select
        .ActiveSheet.ListObjects("Table1").TableStyle = "TableStyleMedium2"
        .Range("J1").Select
        .ActiveCell.FormulaR1C1 = "Total"
        .Range("K1").Select
        .ActiveCell.FormulaR1C1 = "=SUM(C[-3])"
        .Range("J2").Select
        End With
         
    End Sub

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Relevant lines from a db I have open:

    Dim xlSheet As Object
    Set xlSheet = xl.Worksheets("Limo")
    xlSheet.Select

    "Limo" is the name of the sheet I want the code working on. "xl" is my Excel instance, your "oXL".
    Paul

  3. #3
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    994
    Provided Answers: 2
    Have you included Excel in your References? That would allow you to declare oXL as an Excel application object.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  4. #4
    Join Date
    Aug 2012
    Posts
    126
    Thank you for the replies. I have included excel in my references. I've been slammed at work and haven't tried adjusting the code yet pbaldy. As soon as I do I will let you know, hopefully tomorrow

  5. #5
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Your code as written uses late binding, which does not require the reference.

    Using early binding and late binding in Automation
    Paul

  6. #6
    Join Date
    Aug 2012
    Posts
    126
    Printed the link. Thank you

Posting Permissions

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