Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Apr 2003
    Location
    Calgary, Alberta
    Posts
    327
    Thanks
    0
    Thanked 0 Times in 0 Posts

    OPen excel file by VBA (Access 2000)

    I have an item on my main navigatio which populates an excel sheet. For the life of me, I cannot get the excel sheet open. How can I open an excel sheet assuming I know the path?

    Thanks

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: OPen excel file by VBA (Access 2000)

    You can use ShellExecute. See <post#=320106>post 320106</post#>.

  3. #3
    3 Star Lounger
    Join Date
    Apr 2003
    Location
    Calgary, Alberta
    Posts
    327
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: OPen excel file by VBA (Access 2000)

    Thanks for the input. Unfortunatly, I have still having problems writing to the spreadsheet. I have the spreadsheet open, but I cannot get anything written to it. My querys are pulling the right data, but I cannot get that data to the spreadsheet.

    When I want to assign a string to specific cell in a spreadsheet, how can I get specifically reference the cell

    Using just range(CellLocation) does not work, as no workbook is actaully referenced. But when I try to reference a workbook, I always get an error.

    EG:
    Dim ExcelDoc As Excel.Workbook
    Set ExcelDoc = "Tempalte.xls"

    I get a type mismatch.

    How can I reference the cell of a specific workbook?

    Thanks

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: OPen excel file by VBA (Access 2000)

    Sorry, I misunderstood your original question. I thought you had already written to the spreadsheet, and wanted to open it for the user to view. If you want to open a spreadsheet for manipulation, you must use Automation. You can't set a workbook to a string, you must use the Workbooks.Open method.
    <UL><LI>Activate the Visual Basic Editor.
    <LI>Select Tools | References...
    <LI>If you don't have it already, set a reference to the Microsoft Excel 9.0 Object Library (i.e. tick the check box), then click OK.
    <LI>Use code like this:

    Sub SomethingOrOther()
    Dim StartExcel As Boolean
    Dim objXL As Excel.Application
    Dim ExcelDoc As Excel.Workbook
    Dim objSheet As Excel.Worksheet

    On Error Resume Next

    ' Try to get active instance of Excel
    Set objXL = GetObject(, "Excel.Application")
    If objXL Is Nothing Then
    ' Try to start new instance of Excel
    Set objXL = CreateObject("Excel.Application")
    If objXL Is Nothing Then
    MsgBox "Cannot activate Excel.", vbCritical
    Exit Sub
    End If
    StartExcel = True
    End If

    On Error GoTo ErrHandler

    Set ExcelDoc = objXL.Workbooks.Open("Template.xls")
    Set objSheet = ExcelDoc.Worksheets("MySheet")
    ' Code to manipulate workbook goes here
    objSheet.Range("A1") = "Text from Access"
    ...
    ExcelDoc.Close SaveChanges:=True

    ExitHandler:
    On Error Resume Next
    Set objSheet = Nothing
    Set ExcelDoc = Nothing
    If StartExcel = True Then
    objXL.Quit
    End If
    Set objXL = Nothing
    Exit Sub

    ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
    End Sub[/list]Note: error handling is essential when using Automation. You may end up with invisible instances of Excel otherwise.

  5. #5
    3 Star Lounger
    Join Date
    Apr 2003
    Location
    Calgary, Alberta
    Posts
    327
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: OPen excel file by VBA (Access 2000)

    Thanks for the help, Everything is going smooth now

Posting Permissions

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