Print an invisible excel form
hi there, i will need so help please. Here is my problem.
i have an excel form with specific named cells A1 (name), B1 (description), A2(deparment ID ) and B2 (purpose). I have to pass some a set of values via the vb code to the specific name cells in the excel form. Each set of values need to be in a different page so for example you have 3 pages for 3 sets of values. When i click on the Print Button on a different vb form, it should open excel, pass the values, print the form and close excel, everything in the background. This means i will not see any excel activities on my screen and it should go to the default printer. Thank you.
Re: Print an invisible excel form
Ok.. what have you done so far, and where are you stuck?
Re: Print an invisible excel form
Well i don't know the best way to approach this problem. I need some guidance, please.
Re: Print an invisible excel form
Ok, well here's some code that does pretty much what you want - you just need to set the values appropriately.
In order to use this in VB you will need a reference ("project" ->"references") to the Excel Object Library.
VB Code:
Dim oXLApp as Excel.Application 'Declare the object variables
Dim oXLBook as Excel.Workbook
Dim oXLSheet as Excel.Worksheet
'Start Excel:
Set oXLApp = New Excel.Application 'Create a new instance of Excel
Set oXLBook = oXLApp.Workbooks.Add 'Add a new workbook
Set oXLSheet = oXLBook.Worksheets(1) 'Work with the first worksheet
'Set the values:
With oXLSheet
.Range("A1").Value = "Name" 'note that you can use variables instead of "Name" etc.
.Range("B1").Value = "Description"
.Range("A2").Value = "Deparment ID"
.Range("B2").Value = "Purpose"
End With
'Print the workbook
oXLBook.PrintOut
'Close Excel, but dont save the workbook
Set oXLSheet = Nothing 'disconnect from the Worksheet
oXLBook.Close SaveChanges:= False 'Close (and disconnect from) the Workbook
Set oXLBook = Nothing
oXLApp.Quit 'Close (and disconnect from) Excel
Set oXLApp = Nothing
While you are testing it is probably better to not print it out every time (just display it instead to show any mistakes), so I would recommend using code below instead of the code from "Print the workbook" onwards until it is ok.
VB Code:
oXLApp.Visible = True 'Show it to the user
Set oXLSheet = Nothing 'Disconnect from all Excel objects (let the user take over)
Set oXLBook = Nothing
Set oXLApp = Nothing
Re: Print an invisible excel form
Thanks man, well i going to use a virtual printer for testing.
Re: Print an invisible excel form
how can i tell the code which exsisting excel workbook it should be using. There are different excel workbooks for different set of values. Moreover i used the reference - "Microsoft Visual Basic For Applications Extensibility 5.3", is it ok?
Re: Print an invisible excel form
To use an existing workbook, change this:
VB Code:
Set oXLBook = oXLApp.Workbooks.Add 'Add a new workbook
to this:
VB Code:
Set oXLBook = oXLApp.Workbooks.Open("c:\path\file.xls") 'Open a workbook
As for your reference, it isn't relevant to this - only the Excel Object Library matters.