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.

Ok.. what have you done so far, and where are you stuck?

Well i don't know the best way to approach this problem. I need some guidance, please.

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

Thanks man, well i going to use a virtual printer for testing.

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?

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.