printing an excel sheet from VBA

[grmman]

I am trying to use Sheets.PrintOut to print out my excel sheet from my vba program.

Looks like excel hangs when I do it.

Any help.

Thanks

 

[EastWind]

It could be the code is looking for the Sheet name:
e.g. Sheets("Name of Worksheet&quot.PrintOut

ActiveSheet.PrintOut works for sure, however. If that still doesn't work for you, request the print preview option
ActiveSheet.PrintOut,,,True
to see if the preview is working. If it is, it might be a communication problem between your machine and the printer...not the code.

 

[grmman]

I can get it to print out now, but I cant get it to preview.
here is my sub I am using.

Dim x, Y, numFields As Integer
Dim j As Integer
Dim xlapp As Excel.Application
Dim xlwb As Excel.workbook
Dim sheet As Excel.Worksheet

Dim Db As DAO.Database
Dim rs1 As DAO.Recordset
Dim currentvalue As Variant
Dim currentfield As Variant
Dim workbook As Object

Set Db = CurrentDb()
Set rs1 = Db.OpenRecordset("8tranposed", dbOpenDynaset)


Set xlapp = New Excel.Application
Set xlwb = xlapp.Workbooks.Open("book1.xls&quot
Set sheet = xlapp.ActiveWorkbook.Sheets(1)

I do more code in here to load the data into the sheet.
then I try and print it.

xlwb.PrintOut _ this is working but I would like them to preview it


sheet.Application.ActiveWorkbook.Save
sheet.Application.ActiveWorkbook.Close
Set mysheet = Nothing
Set sheet = Nothing
Set xlapp = Nothing
Set Db = Nothing



Thanks for the help.

 

[mgolla]

Move the dim of the xlapp out of the function and at the very beginning of your module.

Then use this to view the excel window:

xlapp.visible = True

 

[grmman]

THANK YOU
IT WORKS GREAT.

 

[grmman]

I have one more question.
If I want to delete a range from my templete sheet is this the right way to do it.

Range("A14:F23&quot.Select
'Selection.Delete
'Range("A25:F26&quot.Select
'Selection.Delete
' Range("A28:F31&quot.Select
' Selection.Delete
' Range("A33:F35&quot.Select
'Selection.Delete

I have data in 3 cells I dont want deleted
in A 23 A 27 A 31
I need to keep them in the same row and not be deleted.

Thanks