Printing Excel Files from a Visual Basic App

[mpadgett]

I'm developing a simple VB app to manage the creation of some MS Excel files. Nothing complex, just some List Boxes, File List Boxes & some Command buttons. I'm using the Shell function in a button to allow the user to open a selected file with MS Excel. I'd like to do the same for printing a selected file. Similarly to right-clicking a file in Windows Explorer and selecting print. Is there a way to use the Shell function to accomplish this?

Thanks,

-MP

 

[BobRodes]

Are you using the shell function because after research it clearly was the best solution among the several that exist, or because you're not familiar with any other way to do it? You can use Excel objects in VB to open excel, and have more control over how you do it. You can also use them to print excel documents (if that's what you mean by print selected files) automatically, since the excel objects will allow you to send commands to Excel directly.

HTH

Bob

 

[RonRepp]

MP:

Bob is correct. Use the Excel Object Library and I guarantee you that you can do anything in your program that Excel can do.

Just for grins, use the Macro recorder to do some arduous task that you do repeatedly. You can see how the code is in the VBE and make adjustments.

There are tons of hints on this site and throughout the Internet to help you along. And simple questions can be asked here, in the VBA forum, or the MS Office Forum.

I hope this helps.

Ron Repp

If gray hair is a sign of wisdom, then I'm a genius.

 

[mpadgett]

Bob & Ron:

Thanks, I'll do some investigation on using Excel Objects.

-MP

 

[RonRepp]

MP:

There are thousands of examples on this site. Do a search in the VBA, VB 5 & 6, MS Office, and .Net forums.

In the meantime, just to play, here's an example of using Excel.

Private objXL As Object
Private Sub StartXL()
Set objXL = CreateObject("Excel.Application")
With objXL
    .Visible = True 'This opens the application workspace
    .Workbooks.Add 'This adds a workbook
    .Range("A1").Value = "Hello" 'Manipulate the range
End With

End Sub

Private Sub QuitXL()
    objXL.Quit
    Set objXL = Nothing
End Sub

I hope that this helps.

Ron Repp

If gray hair is a sign of wisdom, then I'm a genius.

 

[mpadgett]

Yes, thanks. I got it opening the Excel spreadsheet by using,

Private Sub Command2_Click()

Dim file_name As String
Dim oXLApp As Excel.Application

file_name = "k:\clad\form 102\" & List1.Text & "\" & File1.FileName

Set oXLApp = New Excel.Application
Set oXLBook = oXLApp.Workbooks.Open(file_name)

oXLApp.Visible = True
Set oXLApp = Nothing
End Sub

Next I'm going to code it to print the selected file.

Thanks,

-MP

 

[RonRepp]

MP:

Yes, using the app object is great, but then you must set a reference to it. If you deploy your app to another machine, they must have the same version (I believe) or they'll get errors.

Good job, though.

Ron Repp

If gray hair is a sign of wisdom, then I'm a genius.

 

[mpadgett]

Fantastic, I figured out that the PrintOut Method has to be imposed on a worksheet and I got the printing functionality working. Here's another challenge that I could use a suggestion on. Can I somehow specify a sheet number to be printed or does the sheet's name have to be passed to the PrintOut method? My reason for this is because my users will be printing 2 different spreadsheets from my app and the first sheet in the either workbook will be printed. And no, they're not named the same.

Much thanks again,

-MP

 

[mpadgett]

Never mind, I figured it out. Instead of .Worksheets("SheetName") use .Worksheets(1) to print the first sheet of the workbook.

-MP

 

[BobRodes]

Excel has a Worksheets collection that should do what you want. Now, there's something to keep in mind with Excel Automation. Any communication between the VB6 environment and the Excel environment is interprocess. This means that there is marshaling overhead, and this can be significant. If you want to understand this for yourself, try opening an ADO recordset in VB6, iterating through it, and each time copying a value to a cell in Excel. Then, try doing the same thing in Excel, without VB6. You should find that the latter is significantly faster.

So, there are different approaches to automating Excel. In general, you'll probably have the most success putting routines in the spreadsheet in VBA and then calling them from VB6. Try to avoid going into detail when using the link between VB6 and Excel.

Bob

 

[RonRepp]

MP:

Like this:

''In other words, if numerous sheets are selected,
''by holding down the control key and selecting multiple
''sheets, it will print them all.

ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True

Play with the macro recorder in Excel and you'll get 90% of all your answers and much of your code.

I hope this helps.


Ron Repp

If gray hair is a sign of wisdom, then I'm a genius.