Results 1 to 2 of 2
  1. #1
    2 Star Lounger
    Join Date
    Feb 2010
    Denver, CO
    Thanked 0 Times in 0 Posts

    Automating Excel File with Access (Access 2002)

    Trying to Modify an existing Excel Spreadsheet, but can't seem to get the syntax correct. I've come up with the following code. It compiles and runs, but doesn't seem to do anything. Any help will be most appreciated.

    <hr>Private Sub FormatWorksheet()

    Dim ws As Object
    Dim xlApp As Object
    Dim strFileName as string

    strFileName = "serverdatafilename.xls"

    Set xlApp = CreateObject("Excel.Application")
    Set ws = xlApp.Workbooks.Open(strFileName)

    xlApp.Visible = True <font color=448800>'I know it works to this point since the correct file becomes visible here</font color=448800>
    <font color=448800>However, nothing below here seems to do anything.</font color=448800>
    With Selection.Font
    .Name = "Arial"
    .Size = 10
    End With

    With ws
    .Selection.Insert Shift:=xlDown
    .Selection.Cut Destination:=Range("B1")
    . Range("B1").Select
    .Selection.NumberFormat = "m/d/yyyy hh:mm;@"
    .ActiveCell.FormulaR1C1 = "Report Run:"
    .Selection.Delete Shift:=xlToLeft

    .Selection.NumberFormat = "#,##0.00"
    .Close (True)

    Set ws = Nothing

    Exit Sub<hr>

    Any suggestions would be greatly appreciated.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 28 Times in 28 Posts

    Re: Automating Excel File with Access (Access 2002)

    Since you must already have a reference to the Microsoft Excel Object 10.0 Object library, you should declare

    Dim xlApp As Excel.Application

    etc. instead of As Object. You will benefit from IntelliSense, and find errors sooner.

    ws is a workbook, but cells and ranges are a property of a worksheet, not of a workbook. You can't just use Selection, since it is not Access. In general, you should avoid using Selection; code is much more efficient if you operate directly on the ranges, instead of keeping on selecting ranges. There is also an End With lacking in your code (and an End Sub)

    Here is revised code:

    Private Sub FormatWorksheet()

    Dim ws As Object
    Dim xlApp As Excel.Application
    Dim xlWbk As Excel.Workbook
    Dim xlSht As Excel.Worksheet
    Dim strFileName As String

    strFileName = "serverdatafilename.xls"

    Set xlApp = CreateObject("Excel.Application")
    Set xlWbk = xlApp.Workbooks.Open(strFileName)
    Set xlSht = xlWbk.Worksheets(1)

    xlApp.Visible = True

    With xlSht.Cells.Font
    .Name = "Arial"
    .Size = 10
    End With

    With xlSht
    .Rows("1:3").Insert Shift:=xlDown
    .Range("E6").Cut Destination:=Range("B1")
    .Range("B1").NumberFormat = "m/d/yyyy hh:mm;@"
    .Range("A1") = "Report Run:"
    .Columns("E:E").Delete Shift:=xlToLeft
    .Columns("D").NumberFormat = "#,##0.00"
    End With

    xlWbk.Close SaveChanges:=True

    Set xlSht = Nothing
    Set xlWbk = Nothing
    Set xlApp = Nothing

    End Sub

Posting Permissions

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