Using Excel from Access

[john.fuller]

Inside an Access DB I am trying to open up and print a selected range
from an Excel File (long reason on why I ahve to do it this way, but
there is no work around). I wrote the following code in Excel (and it
works great there). I then transferred it into access, added teh
excelapp object lines, and basically put excelapp in front of every
command line. This, however, did not work. The 2 places I find it
crashing now is trying to set the active printer and set the wkbk. It
will open the file, but won't set the wkbk variable. Also, right now
the set printer code runs (due to the on error resume next), but it
doesn't change the printer. I tried giving set
excelapp.application.activeprinter a good string (as opposed to
looping through all the possible Ne##), and it still won't set it.
Any help is appreciated.

Sub Print_TOC_Chart()
Dim WkBk As Workbook, WkSht As Worksheet, ExcelApp As Object

Set ExcelApp = CreateObject("Excel.Application")
ExcelApp.Visible = True

Call Set_Printer(ExcelApp, Me.ComboBox_prtSelect.Value)

Set WkBk = ExcelApp.Workbooks.Open("\\hn-s-fileserv1\sharedmfg\mfg
\CFM\Reports\TOC_Reports\Workcenter TOC Reports.xls", 0)

Set WkSht = ExcelApp.WkBk.Worksheets("Workcenter Charts")

With ExcelApp.WkSht.PageSetup
.PrintArea = "B112:AW122"
.Zoom = False
.FitToPagesTall = 1
.FitToPagesWide = 1
.Orientation = xlLandscape
End With

ExcelApp.curWkSht.PrintOut Copies:=1

ExcelApp.WkBk.Close False
End Sub

Sub Set_Printer(ExcelApp As Object, PrinterName As String)
'On Error Resume Next

For i = 0 To 99
If i < 10 Then
ExcelApp.Application.ActivePrinter = PrinterName & " on
Ne0" & i & ":"
If ExcelApp.Err.Number = 0 Then Exit Sub
ExcelApp.Err.Clear
Else
ExcelApp.Application.ActivePrinter = PrinterName & " on
Ne" & i & ":"
If ExcelApp.Err.Number = 0 Then Exit Sub
ExcelApp.Err.Clear
End If
Next i
End Sub

 

[Daniel Pineault]

Try the following code,

Function Print_TOC_Chart()
Dim xlApp As Object
Dim xlWrkBk As Object
Dim xlWrkSht As Object

On Error Resume Next
Set xlApp = GetObject(, "Excel.Application") 'Bind to existing instance
of Excel

If Err.Number <> 0 Then
'Could not get instance of Excel, so create a new one
Err.Clear
'On Error GoTo Err_Handler
Set xlApp = CreateObject("excel.application")
Else
'On Error GoTo Err_Handler
End If

'xlApp.Visible = True 'make excel visible to the user
Set xlWrkBk = xlApp.Workbooks.Open("\\hn-s-fileserv1\sharedmfg\mfg" & _
"'\CFM\Reports\TOC_Reports\Workcenter TOC Reports.xls")

'Call Set_Printer(ExcelApp, Me.ComboBox_prtSelect.Value)

Set xlWrkSht = xlApp.Worksheets("WorkBookSheetName")

With xlWrkSht.PageSetup
.PrintArea = "B24"
.Zoom = False
.FitToPagesTall = 1
.FitToPagesWide = 1
.Orientation = xlLandscape
End With

xlWrkSht.PrintOut Copies:=1

ExcelApp.WkBk.Close False
End Function

It work for me, but I did not try the change printer aspect of it. If I
have the time I will look at it and post back with the solution, but this
should get you one step closer to the solution.
--
Hope this helps,

Daniel Pineault
http://www.cardaconsultants.com
If this post was helpful, please rate it by using the vote buttons.

 

[Daniel Pineault]

It's actually an easy fix, see below.

Function Print_TOC_Chart()
Dim xlApp As Object
Dim xlWrkBk As Object
Dim xlWrkSht As Object

On Error Resume Next
Set xlApp = GetObject(, "Excel.Application") 'Bind to existing instance
of Excel

If Err.Number <> 0 Then
'Could not get instance of Excel, so create a new one
Err.Clear
'On Error GoTo Err_Handler
Set xlApp = CreateObject("excel.application")
Else
'On Error GoTo Err_Handler
End If

'xlApp.Visible = True 'make excel visible to the user
Set xlWrkBk = xlApp.Workbooks.Open("\\hn-s-fileserv1\sharedmfg\mfg" & _
"'\CFM\Reports\TOC_Reports\Workcenter TOC Reports.xls")

Set xlWrkSht = xlApp.Worksheets("Workcenter Charts")

With xlWrkSht.PageSetup
.PrintArea = "B24"
.Zoom = False
.FitToPagesTall = 1
.FitToPagesWide = 1
.Orientation = xlLandscape
End With

xlWrkSht.PrintOut Copies:=1, ActivePrinter:=Me.ComboBox_prtSelect.Value

xlWrkBk.Close False
xlApp.Close

Set xlWrkSht = Nothing
Set xlWrkBk = Nothing
Set xlApp = Nothing

End Function


you can simply add the ActivePrinter parameter to the PrintOut line such as:
xlWrkSht.PrintOut Copies:=1, ActivePrinter:=Me.ComboBox_prtSelect.Value


--
Hope this helps,

Daniel Pineault
http://www.cardaconsultants.com
If this post was helpful, please rate it by using the vote buttons.