Results 1 to 15 of 15
  1. #1
    Join Date
    May 2009
    Posts
    104

    Unanswered: Populating Data From Access to Excel

    After combing through the forums i have yet to find an solution to this. I have a button on a form that opens up a specific excel spread sheet that is named the same as a the data in the access database field. IE the field name is PartNumber and the data is 195-2020, when i click the button it opens up the excel spread sheet named 195-2020.xls. What i would like to do now is when i open the spread sheet with that button have it copy sheet1 and rename the copied sheet with the value from another field in that same record. For instance, when i click on the record with the field [PartNumber] 195-2020 and field [WorkOrder] 123456-1, it will open up excel spreadsheet 195-2020.xls, copy sheet1, and rename the copied "sheet1(2)" to "123456-1". The reason for copying sheet1 is that it's a template sheet with empty cells that need to be filled in by the operator. sheet1 can always be named sheet1, but all other sheets need to have the name of the work order.If this is confusing i will elaborate in more detail.

    Any help will be greatly appreciated.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Basically, you can create an instance of Excel in Access, use this instance to open a workbook (.xls file) and manipulate the worksheet(s) it contains.
    See: http://www.dbforums.com/microsoft-ac...xcel-file.html
    Have a nice day!

  3. #3
    Join Date
    May 2009
    Posts
    104
    It looks good but what code do i use to copy a sheet and rename it?

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Once you have the object variable (the handle) to the Excel application, you can issue any Excel command you want to it. If you don't know them, open Excel normally, start the Macro recorder, manually perform the desired operations, stop the Macro recorder, open the VBA Editor and use the code of the macro you just recorded. For simple actions, such as saving the workbook under a new name, the solution is given in my sample code.
    Have a nice day!

  5. #5
    Join Date
    May 2009
    Posts
    104
    Here is what i have so far, i keep getting an error when it tries to rename the sheet by the [FO#] field.

    Private Sub Command57_Click()
    '
    ' This needs a reference to Excel (here for Office 2003):
    ' Tools --> References --> Microsoft Excel 11.0 Object Library.
    '
    Dim xlapp As Excel.Application
    Set xlapp = New Excel.Application
    '
    ' It's also possible to use Late Binding.
    ' (This does not need a reference to Excel but is less efficient).
    '
    ' Dim xlApp As Object
    ' xlapp = CreateObject("Excel.Application")

    With xlapp

    ' Useful when you're debugging.
    '
    .Visible = True

    ' Open the Excel file you want to work with.
    '
    .Workbooks.Open "U:\QC\FinalInspection\InspectionSheets\" & [Part Number] & ".xlsx"
    '
    ' Do something with the open workbook.
    '
    Sheets("Sheet1").Select
    Sheets("Sheet1").Copy After:=Sheets(1)
    Sheets("Sheet1 (2)").Select
    Sheets("Sheet1 (2)").Name = "[FO#]" <--------------------Debug window errors here
    ' End of process.
    '
    ' Save the file (if necessary).
    '
    .ActiveWorkbook.Save

    ' Then close it.
    '
    .Workbooks.Close

    ' Close Excel. !!! This is very important !!!
    ' If you do not property close the instance of Excel it will remain
    ' locked in memory with no visible interface (only the Task Manager to kill it).
    '
    .Quit
    End With

    ' Clean up
    '
    Set xlapp = Nothing

    End Sub

    If i change the line
    Sheets("Sheet1 (2)").Name = "[FO#]" to
    Sheets("Sheet1 (2)").Name = "123456-1" everything works as it should.
    Last edited by dbshaft; 08-22-11 at 13:49.

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You can't use the following characters in the name of an Excel sheet: : / \ * [ ]
    Have a nice day!

  7. #7
    Join Date
    May 2009
    Posts
    104
    Then now do i use the Field [FO#] to name the worksheet. The same record has the field [FO#] with the data 123456-1, this is the name i want for the sheet.

  8. #8
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You need to use a reference to [FO#] --> must be outside the quotes. Now to what object in your code does [FO#] pertain? For a reference you must use something like <Object>.[FO#] where <object> can be the name of a recordset, the name of an open form, etc.
    Have a nice day!

  9. #9
    Join Date
    May 2009
    Posts
    104
    I finally have it. I ended up with

    Private Sub Command57_Click()
    '
    ' This needs a reference to Excel (here for Office 2003):
    ' Tools --> References --> Microsoft Excel 11.0 Object Library.
    '
    Dim xlApp As Excel.Application
    Set xlApp = New Excel.Application
    '
    ' It's also possible to use Late Binding.
    ' (This does not need a reference to Excel but is less efficient).
    '
    ' Dim xlApp As Object
    ' xlApp = CreateObject("Excel.Application")

    With xlApp

    ' Useful when you're debugging.
    '
    .Visible = True

    ' Open the Excel file you want to work with.
    '
    .Workbooks.Open "U:\QC\FinalInspection\InspectionSheets\" & [Part Number] & ".xlsx"
    '
    ' Do something with the open workbook.
    '
    Sheets("Sheet1").Select
    Sheets("Sheet1").Copy After:=Sheets(1)
    Sheets("Sheet1 (2)").Select
    Sheets("Sheet1 (2)").Name = Recordset.[FO#]
    ' End of process.
    '
    ' Save the file (if necessary).
    '
    ' .ActiveWorkbook.Save

    ' Then close it.
    '
    ' .Workbooks.Close

    ' Close Excel. !!! This is very important !!!
    ' If you do not property close the instance of Excel it will remain
    ' locked in memory with no visible interface (only the Task Manager to kill it).
    '
    ' .Quit
    End With

    ' Clean up
    '
    Set xlApp = Nothing

    End Sub
    Last edited by dbshaft; 08-22-11 at 18:09.

  10. #10
    Join Date
    May 2009
    Posts
    104
    I'll clean out the comments in the code once i'm confident i don't need them anymore. Thank you for you help.

  11. #11
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome! Glad you could solve it.
    Have a nice day!

  12. #12
    Join Date
    May 2009
    Posts
    104
    Well actually you solved it, but i just had to pay attention to what you were saying. The problem i have now is it's not killing the instance of excel. I still see it in my task manager as a running process and when i try to click the button again it errors out. If i kill the process in task manager all works as it should. Any thoughts? Everything works as long as i use task manager to kill the task after i click the button. I see the notes about this problem so i'm not sure why it's doing it. Here is my code.
    Code:
    Private Sub Command57_Click()
    '
        Dim xlapp As Excel.Application
        Set xlapp = New Excel.Application
    '
        With xlapp
        
            .Visible = False
        
        ' Open the Excel file you want to work with.
        '
            .Workbooks.Open "U:\QC\FinalInspection\InspectionSheets\" & [Part Number] & ".xlsx"
    '
    ' Do something with the open workbook.
        Sheets("Sheet1").Select
        Sheets("Sheet1").Copy After:=Sheets(1)
        Sheets("Sheet1 (2)").Select
        Sheets("Sheet1 (2)").Name = Recordset.[FO#]
    '
    ' End of process.
    '
        ' Save the file (if necessary).
        '
            .ActiveWorkbook.Save
        
        ' Then close it.
        '
            .Workbooks.Close
        
        ' Close Excel. !!! This is very important !!!
        ' If you do not property close the instance of Excel it will remain
        ' locked in memory with no visible interface (only the Task Manager to kill it).
        '
            .Quit
        End With
        
        ' Clean up
        '
        Set xlapp = Nothing
        
    End Sub
    Last edited by dbshaft; 08-23-11 at 12:45.

  13. #13
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    There is something strange in your code. When you copy the sheet the reference to xlapp is missing:
    Code:
        Sheets("Sheet1").Select
        Sheets("Sheet1").Copy After:=Sheets(1)
        Sheets("Sheet1 (2)").Select
        Sheets("Sheet1 (2)").Name = Recordset.[FO#]
    While it should be:
    Code:
        .Sheets("Sheet1").Select
        .Sheets("Sheet1").Copy After:=Sheets(1)
        .Sheets("Sheet1 (2)").Select
        .Sheets("Sheet1 (2)").Name = Recordset.[FO#]
    However it works, but I don't understand why or how.

    Closing an OLE server (Excel in this case) can cause all sorts of problems. Usually <Application>.Quit does the trick but there are several reasons why the process can still remain into memory. A sure way for getting rid of a process is as follows:

    1. In the Declarations section of the module, place the following line:
    Code:
    Private Declare Function EndTask Lib "user32" (ByVal hWnd As Long, ByVal fShutDown As Long, ByVal fForce As Long) As Long
    2. Add the following lines to the procedure:
    Code:
    Private Sub Command57_Click()
    '
        Dim xlhwnd As Long
        Dim xlapp As Excel.Application
        Set xlapp = New Excel.Application
    '
        With xlapp
            xlhwnd = .hWnd
    And:
    Code:
            .Quit
            EndTask xlhwnd, 0, 1
        End With
    This will force Windows to unload Excel, the way the Tasks Manager operates.
    Have a nice day!

  14. #14
    Join Date
    May 2009
    Posts
    104
    That did it. Could this have anything to do with running a 64 bit version of Win7, with Excel 2007 & Access 2007?

  15. #15
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Not really : I have sometimes the same problem too, with XP and 32 bit versions of Office. It is related to the UserControl Property of the instance of the application you create. This property is Read-Only for you but can change from outside your application (see: General: Automation object doesn't close automatically). Unfortunately most of the advanced documentation and technical articles concerning Automation and COM objects disappeared from MSDN since .NET is the flavour of the month at Microsoft's and former technologies are considered obsolete. The only solution I could find consists in "killing" the instance of the application the way the Task Manager does it.
    Have a nice day!

Posting Permissions

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