Print an invisible excel form

[Prav]

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.

[si_the_geek]

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

[Prav]

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

[si_the_geek]

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.

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.

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

[Prav]

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

[Prav]

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?

[si_the_geek]

To use an existing workbook, change this:

Set oXLBook = oXLApp.Workbooks.Add    'Add a new workbook

to this:

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.