Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2005
    Posts
    313

    Unanswered: move and copy worksheet from one Excel to New Excel file

    Hi,

    I placed many Excel files in the share folder. And then, I create a MS Access form, and one button, the button will copy the worksheets of one Excel file, and then create the new workbook, and paste it over there.

    Dim obj As Object
    Dim objXL As Object
    Dim objActiveWrb As Object

    Set obj = GetObject("c:\Test.xls")
    obj.Worksheets(1).Copy

    Set objXL = CreateObject("Excel.Application")
    objXL.Application.Workbooks.Add
    Set objActiveWkb = objXL.Application.ActiveWorkBook

    With objActiveWkb
    .Worksheets(1).Cells(1, 1) = obj.Worksheets(1).Paste
    End With

    objXL.Visible = True

    What's wrong with it?

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

    I don't know if this is what you want, but it does copy the contents of one sheet to the sheet of a new workbook

    Code:
    Sub test()
    Dim objXL As Object
    Dim objActiveWkb As Object
    
    Set objXL = CreateObject("Excel.Application")
    With objXL
        .Workbooks.Open "C:\Test.xls"
        .Worksheets(1).Cells.Copy
        .Application.Workbooks.Add
        Set objActiveWkb = .Application.ActiveWorkbook
        objActiveWkb.ActiveSheet.Paste
        .DisplayAlerts = False
        .Workbooks("Test.xls").Close
        .Visible = True
        .DisplayAlerts = False
    End With
    End Sub
    ??

    MTB

Posting Permissions

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