Access - VBA Code

Question

Hi all,

i have a access database , in which there is link to open and print excel file. the following VBA code i am using:

Private Sub Command44_Click()

Dim objXLApp As Excel.Application

Dim objXLBook As Excel.Workbook

Dim objDataSheet As Excel.Worksheet

Set objXLBook = GetObject("C:\Documents and Settings\Atif\My Documents\Invoices" & [Combo26] & ".xls")

Set objXLApp = objXLBook.Parent

Set objDataSheet = objXLBook.Worksheets("Sheet1")

objDataSheet.PrintOut Copies:=1, Collate:=True

objXLBook.close savechanges:=False 'close without changes

Set objDataSheet = Nothing

Set objXLBook = Nothing

Set objXLApp = Nothing

End Sub

I use to print all my excel invoices from the database on my Dotmatrix printer but my defult printer is Laser Jet. to print invoices on Dotmatrix, first i have to open Excel for selecting printer then print invoice. is there any code i can on the above to print directly on Dotmatrix or make dotmatrix as defult printer only for the invoices.

Thanks in advance

Atif Hasan

Answer 1

It looks like you did it correctly, I don't know why it doesn't work, sorry!

Answer 2

Thanks for your reply and help

i put the code like you said but when i am running the code it is giving me an error:

run time error '1004':

Method 'Active printer' of object '_application' failed

   objXLApp.ActivePrinter = "EPSON LQ-300+II ESC/P2 on Ne05:" ' Change this!

do i have to use inverted commas also.

thanks

Answer 3

First, do the following: in Excel, select File > Print.

Select the dot matrix printer. You don't have to actually print at this time.

Go back to the Home tab of the ribbon.

Press Alt+F11 to activate the Visual Basic Editor.

Press Ctrl+G to activate the Immediate window.

Type the following, then press Enter:

? Application.ActivePrinter

The result will be the name of the dot matrix printer, plus information about the printer port, for example

Dot Matrix Printer on Ne01:

Next, edit the code like this:

Private Sub Command44_Click()

    Dim objXLApp As Excel.Application

    Dim objXLBook As Excel.Workbook

    Dim objDataSheet As Excel.Worksheet

    Dim strPrinter As String

    Set objXLBook = GetObject("C:\Documents and Settings\Atif\My Documents\Invoices" & [Combo26] & ".xls")

    Set objXLApp = objXLBook.Parent

    Set objDataSheet = objXLBook.Worksheets("Sheet1")

    ' Get the current printer

    strPrinter = objXLApp.ActivePrinter

    ' Temporarily set the printer to dot matrix

    objXLApp.ActivePrinter = "Dot Matrix Printer on Ne01:" ' Change this!

    objDataSheet.PrintOut Copies:=1, Collate:=True

    ' Restore the original printer

    objXLApp.ActivePrinter = strPrinter

    objXLBook.Close SaveChanges:=False 'close without changes

    Set objDataSheet = Nothing

    Set objXLBook = Nothing

    Set objXLApp = Nothing

End Sub

Substitute the actual name of the printer as shown in the Immediate window in Excel (you can copy and paste it).

You can now go back to Excel and set the 'normal' printer there.