about opening an excel file and printing active sheet

[anchamal]

i have an excel file named form.xls

i would like to create code by pressing a button to open that form.xls, print the active sheet (sheet2) and the close ms excel.

can somebody help me?

thanks

 

[ajetrumpet]

do you know how to record macros in excel? if you do, you can get the code right away, for example...this function which works:

Dim xl As New Excel.Application
Dim wkbk As Excel.Workbook

xl.Visible = True

Set wkbk = xl.Workbooks.Open("file loc")
             
xl.ExecuteExcel4Macro "PRINT(1,,,1,,,,,,,,2,,,TRUE,,FALSE)"
xl.Quit

wkbk.Close

Set wkbk = Nothing
Set xl = Nothing

 

[anchamal]

do you know how to record macros in excel? if you do, you can get the code right away, for example...this function which works:

Dim xl As New Excel.Application
Dim wkbk As Excel.Workbook
 
xl.Visible = True
 
Set wkbk = xl.Workbooks.Open("file loc")
 
xl.ExecuteExcel4Macro "PRINT(1,,,1,,,,,,,,2,,,TRUE,,FALSE)"
xl.Quit
 
wkbk.Close
 
Set wkbk = Nothing
Set xl = Nothing

----------------------------

thanks for your reply

if i understood that code creates a new file right?
what if i want to open a file that is created from a query? my case it's called form.xls
thanks

 

[anchamal]

after pasting the code like this:

Dim xl As New Excel.Application
Dim wkbk As Excel.Workbook

xl.Visible = True

Set wkbk = xl.Workbooks.Open("file loc")

xl.ExecuteExcel4Macro "PRINT(1,,,1,,,,,,,,2,,,TRUE,,FALSE)"
xl.Quit

wkbk.Close

Set wkbk = Nothing
Set xl = Nothing


file opens but nothing prints out.
what should i change to print the current sheet (sheet2)

thanks

 

[ajetrumpet]

i have no idea how to write this code sir. i got the code from simply opening a workbook on my machine, start recording a macro, selecting print from the file menu and then pressing OK to print the current sheet. maybe it's different on yours. do what I did, and the program will give you the code, just like it did for me

 

[SOS]

Simple code here:

Function PrintExcelWS(strPathAndName As String)
    Dim objXL As Object
    Dim xlWB As Object
    
    Set objXL = CreateObject("Excel.Application")
    Set xlWB = objXL.Workbooks.Open(strPathAndName)
    
    xlWB.PrintOut
    
    xlWB.Close
    objXL.Quit
    
    Set xlWB = Nothing
    Set objXL = Nothing
    
End Function

Just pass the workbook's path and name to this function and there you go...

 

[anchamal]

THAT REALY HELPED, THANKS A LOT

i have one more question:

how can i set warnings off at the very begining?

when i press button i get the following:
This workbook contains links to other data sources,
....click yes
....click no

i want it to automaticaly choose yes, or no i don't realy mind.

thanks again for your help

 

[SOS]

That one, I'm not sure about.

 

[Subcancel]

DoCmd.SetWarnings False

should do the trick.. just up at the top of your function or sub

 

[boblarson]

DoCmd.SetWarnings False

should do the trick.. just up at the top of your function or sub

I don't think that will work Subcancel as it is an EXCEL warning, not an Access warning. It might work if

objXL.DisplayAlerts = False

was used, but not sure about that for a link refresh dialog.

And just as an FYI, whenever you use

DoCmd.SetWarnings False

you should always include

DoCmd.SetWarnings True

in the exit area of your procedure so you don't end up with them off and then it can cause a lot of pain.