Results 1 to 9 of 9
  1. #1
    Join Date
    Aug 2007
    Posts
    88

    Unanswered: getting error when copy Excel worksheet: runtime error 91 - object variable not set

    Hi Gurus,
    I have a excel file (i.e. "Test Excel To Excel.xls" ) with 4 sheets: sheet1 named "graph", sheet2 named "data", sheet3 named rawdata, sheet4 is not important. The sheet1 and sheet2 are heavily depend on the data in sheet3 and are macro enabled.

    I need to use a onclick event of a command button in Access to copy only sheet1 and sheet2 from "Test Excel To Excel.xls" to a nonexisting new Excel workbook. (the VBA program will need to create the new Excel file on the fly)

    I got a VBA program from this forum somewhere and modified it and save in a module. (sorry, I believe the author's name is John .., however, I can no longer find the code in the forum and give credit to John.)
    In a form, the command button click event is like this: and it invoke the function in the module.

    When click the button, I got runtime error 91 - object variable not set
    at the "Range" line in the module as indicated '++++++++++++' below.

    I am new in Access VBA and not familiar with EXCEL at all. Any help will be very much appreciate. Thanks.

    Newguyinaccess

    Private Sub cmdExcelToExcel_Click()
    Dim EOK As Boolean
    EOK = ExportTableOrQueryToExcel4("Test Excel To Excel", "TestExcelCopy", "Graph", "Data")

    End Sub

    '===========================================
    'the following is the code for the module
    '============================================

    Public Function ExportTableOrQueryToExcel4(ByVal sourcename As String, ByVal destname As String, ByVal sheet1name As String, ByVal sheet2name As String) As Boolean

    Dim oXL As Excel.Application
    Dim oSheet As Excel.Worksheet

    Dim oWKSource As Excel.Workbook
    Dim oWKDestination As Excel.Workbook
    Dim oBook As Excel.Workbook
    Dim strSourceBook As String
    Dim strDestinationBook As String
    Dim strDestinationSheet As String
    Dim strSourceSheet1 As String
    Dim strDestinationSheet1 As String
    Dim strSourceSheet2 As String
    Dim strDestinationSheet2 As String
    Dim raSource As Excel.Range
    Dim raDestination As Excel.Range


    strSourceBook = CurrentProject.path & "\" & sourcename & ".xls"

    strSourceSheet1 = sheet1name
    strSourceSheet2 = sheet2name
    strDestinationBook = CurrentProject.path & "\" & destname & ".xls"
    strDestinationSheet1 = sheet1name
    strDestinationSheet2 = sheet2name

    'Get instance of Excel and open the workbooks
    Set oXL = CreateObject("Excel.Application")
    Set oWKSource = oXL.Workbooks.Open(strSourceBook, , ReadOnly = True)

    Set oWKDestination = oXL.Workbooks.Add
    Set oSheet = oWKDestination.Worksheets.Add(, , 2)
    oWKDestination.Sheets(1).Name = sheet1name
    oWKDestination.Sheets(2).Name = sheet2name

    oXL.Visible = False

    oXL.DisplayAlerts = False

    ' delete the extra worksheets
    Dim intX As Integer
    If oXL.Worksheets.Count > 2 Then
    For intX = 3 To oXL.Worksheets.Count
    oXL.Worksheets(3).Delete
    Next
    End If

    'Define the range to copy and the location to paste to
    'Modify this as required!
    'With oWKSource.Sheets(1)
    '
    'raSource = .Range("A1:ET184")
    '
    'End With
    'With oWKDestination.Sheets(1)
    'raDestination = .Range("A1")
    'End With
    raSource = oWKSource.Worksheets(1).Range("A1, ET184") '++++++++++++++++++++++++' ' (Error occurred at this line.)

    'Normally just specify the top left cell of the destination
    raDestination = oWKDestination.Worksheets(1).Range("A1")

    'Copy
    raSource.Copy raDestination
    'oWKSource.Sheets(1).Range("A1:ET184").Copy oWKDestination.Sheets(1).Range("A1")


    'raSource = oWKSource.Worksheets(strSourceSheet2).Range("A1:BP 127")
    'Normally just specify the top left cell of the destination
    'raDestination = oWKDestination.Worksheets(strDestinationSheet2).Ra nge("A1")
    raSource = oWKSource.Sheets(2).Range("A1:BP127")
    'Normally just specify the top left cell of the destination
    raDestination = oWKDestination.Sheets(2).Range("A1")
    raSource.Copy raDestination

    'Finish
    'Save workbook we modified
    oXL.DisplayAlerts = False
    oWKDestination.SaveAs strDestinationBook

    'oXL.Save strDestinationBook
    'strDestinationBook.Save
    'Close all workbooks
    For Each oBook In oXL.Workbooks
    oBook.Close False
    Next
    oXL.Quit
    Set oXL = Nothing
    Set oWKSource = Nothing
    Set oWKDestination = Nothing

    Set oBook = Nothing

    End Function

  2. #2
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    Try this
    Set raSource = oWKSource.Worksheets(1).Range("A1, ET184")

    ??

    MTB

  3. #3
    Join Date
    Aug 2007
    Posts
    88

    Thanks. Still get the same error

    Quote Originally Posted by MikeTheBike
    Hi

    Try this
    Set raSource = oWKSource.Worksheets(1).Range("A1, ET184")

    ??

    MTB
    Mike,

    Thanks for response. I did try that, but still got the same error.

    Newguy

  4. #4
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    You need to use the Set statement in four line as follows:-

    Set raSource = oWKSource.Worksheets(1).Range("A1:ET184") ' NOTE THE COLON??
    Set raDestination = oWKDestination.Worksheets(1).Range("A1")
    Set raSource = oWKSource.Sheets(2).Range("A1:BP127")
    Set raDestination = oWKDestination.Sheets(2).Range("A1")

    The last two do not produce errors if you do not use set, but also do not set the object variable to the new range!!

    I also added
    oXL.DisplayAlerts = True
    before
    oXL.Quit

    Without this Exel stopped asking me to save changes (and didn't save them), even when opening a new application !!??

    With these mods it seem to do what I assume you want it to do ??


    MTB

  5. #5
    Join Date
    Aug 2007
    Posts
    88

    Thumbs up Thank you so much!

    Quote Originally Posted by MikeTheBike
    Hi

    You need to use the Set statement in four line as follows:-

    Set raSource = oWKSource.Worksheets(1).Range("A1:ET184") ' NOTE THE COLON??
    Set raDestination = oWKDestination.Worksheets(1).Range("A1")
    Set raSource = oWKSource.Sheets(2).Range("A1:BP127")
    Set raDestination = oWKDestination.Sheets(2).Range("A1")

    The last two do not produce errors if you do not use set, but also do not set the object variable to the new range!!

    I also added
    oXL.DisplayAlerts = True
    before
    oXL.Quit

    Without this Exel stopped asking me to save changes (and didn't save them), even when opening a new application !!??

    With these mods it seem to do what I assume you want it to do ??


    MTB
    Hi Mike,

    Thank you so much for helping me with this. Very nice of you. I had been researched and tried a week, but can not find a solution. Thanks again for great help. I did not encounter any error this time. However, there is one thing not quite right for the excel. The destination Excel's sheet1 , Graph, is empty, but sheet2, Data, has all of the data I want. Basically, the Graph sheet in the source Excel contains quite a few bar graphs and those graphs all depend on the data in the sheet2.

    When I manually open an empty Excel and manually copy the Graph sheet from the source, then paste it in the empty sheet, I got empty result. I will need to select Edit, paste special, then select "Microsoft Excel worksheet Object", then click OK. It will prompt me a warning " The document you are opening contains macros, some macros many contain harmful viruses. If you are sure this document is from a trusted source, click Yes. If you are not sure and want to prevent any macros from running, click No."
    Then I click either yes or no, it will paste successfully.

    Do you have any ideas how to handle that in Access VBA code? Thanks again!

    NewGuy

  6. #6
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    Difficult to appreciate what your actual requirements are.

    BTW where does Access come into this ??

    In these type of situations I tend to use dedicated workbook (templates) and write the data to the spreadsheet. For often than not I write the code in Excel and retrieve data set via ADO connection. It is such easier, and seems to be faster running code in Excel that access for this type of thing.

    But as I said I don’t know your actual situation.

    MTB

  7. #7
    Join Date
    Aug 2007
    Posts
    88

    Thanks.

    Quote Originally Posted by MikeTheBike
    Hi

    Difficult to appreciate what your actual requirements are.

    BTW where does Access come into this ??

    In these type of situations I tend to use dedicated workbook (templates) and write the data to the spreadsheet. For often than not I write the code in Excel and retrieve data set via ADO connection. It is such easier, and seems to be faster running code in Excel that access for this type of thing.

    But as I said I don’t know your actual situation.

    MTB
    Hi Mike,

    Thanks. First of all, I am new to VBA programming in MS office, the only thing I know is Access. I have no idea how Excel programming works and don't know how to do macros. I only know VBA in Access.
    Second, the project I work on requires one "control interface" to do all of the required functions and need to separate raw data and sorted/organized data. For me, it's not easy to explain the requirement and not a easy project.

    Anyway, I appreciate your help in resolving the big headache for me and providing a right direction for me to keep working on it.

    Thanks a lot.

    With all of the best,

    NewGuy

  8. #8
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi NewGuy

    You are do a good job of using automation via Access considering you have no experience with Excel VBA!!

    I have no experience of copying graphical object (and their data) from book to book so cannot be very helpful there, but a set of templates opened form Access may be an idea?

    Thanks for letting us know it was useful, it nice to know you have helped.

    Cheers

    MTB

  9. #9
    Join Date
    Aug 2007
    Posts
    88
    Quote Originally Posted by MikeTheBike
    Hi NewGuy

    You are do a good job of using automation via Access considering you have no experience with Excel VBA!!

    I have no experience of copying graphical object (and their data) from book to book so cannot be very helpful there, but a set of templates opened form Access may be an idea?

    Thanks for letting us know it was useful, it nice to know you have helped.

    Cheers

    MTB
    Hi Mike,

    Thanks for encouraging and all the helps! I had tried some codes for pastespecial methods, but still can not get it to work for the graph. However, I will keep researching. Thanks again for giving me a right direction!

    NewGuy

Posting Permissions

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