Automating Excel File with Access (Access 2002)

Topic

New Reply

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.

[indent]

---

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 ‘I know it works to this point since the correct file becomes visible here
However, nothing below here seems to do anything.
ws.Cells.Select
With Selection.Font
.Name = “Arial”
.Size = 10
End With

With ws
.Rows(“1:3”).Select
.Selection.Insert Shift:=xlDown
.Range(“E6”).Select
.Selection.Cut Destination:=Range(“B1”)
. Range(“B1”).Select
.Selection.NumberFormat = “m/d/yyyy hh:mm;@”
.Range(“A1”).Select
.ActiveCell.FormulaR1C1 = “Report Run:”
.Columns(“E:E”).Select
.Selection.Delete Shift:=xlToLeft

.Cells.EntireColumn.AutoFit
.Columns(“D:D”).Select
.Selection.NumberFormat = “#,##0.00”
.Close (True)

Set ws = Nothing

Exit_Sub:
Exit Sub

---

[/indent]

Any suggestions would be greatly appreciated.

Reply | Quote

Replies

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
.Cells.EntireColumn.AutoFit
.Columns(“D:D”).NumberFormat = “#,##0.00”
End With

xlWbk.Close SaveChanges:=True
xlApp.Quit

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

End Sub

Reply | Quote

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
.Cells.EntireColumn.AutoFit
.Columns(“D:D”).NumberFormat = “#,##0.00”
End With

xlWbk.Close SaveChanges:=True
xlApp.Quit

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

End Sub

Reply | Quote