Create and print report from Excel 1

[llb1]

I would like to create a few reports that I will create and launch from Excel VBA. These reports would be similar to the one I could create and print if I was using Ms Access.
- First is it possible to use VBA in Excel?
- Second is there any sample or link that I could see?
Thanks in advance.
Louis

 

[byundt]

Louis,
VBA was first put to use in Excel 5, and has been Excel's macro language ever since. It didn't come to Access until much, much later.

You can create "reports" in Excel by printing a portion (or the entirety) of a worksheet, moving data to a new worksheet, filtering or sorting data on an existing worksheet, running SQL or ADO database inquiries, etc.

Rather than write a book about all the possibilities, perhaps you could describe what you are starting with and the desired results.

 

[EMax1]

Hi,
I am working on a project tah will retrieve info from an external server (VMS Database). When the data is loaded in the Excel spreasheet, I will do some specific calculations.
I do the above very often, therefore it should be OK.

For this project I will also need to retrieve some information and generate reports from the data. For example a report showing me all the sales for a specific salesperson.

I am not familiar with any reporting possibility in Excel and I was wondering if it is worth the time to do it in Excel or just do all using MS Access.
Thanks.
Louis

 

[byundt]

Louis,
I suggest you take a look at the Pivot Tables capability of Excel. This is excellent for slicing and dicing data. A good starting point is Chip Pearson's web page

http://www.cpearson.com/excel/pivots.htm

You can create and manipulate the Pivot Tables object using VBA. If you can produce what you want manually, then try recording a macro to automate the process.

Excel will record a lot of "extra" steps when it records macros, so it is always advisable to hand-tune the resulting code. I often use the recording feature to identify the required objects & properties, as well as to define correct syntax.
Brad

 

[EMax1]

I will give it a try.
Thanks

 

[Cresby]

any way to invoke the print dialog

the best I can find is
Excel.XlBuiltInDialog.xlDialogPrinterSetup which is a property

 

[Molby]

Cresby, you can use:

Sub test()
Application.Dialogs(xlDialogPrint).Show
End Sub

 

[Cresby]

Thanks - I will go try. I don't have VBA help at work! Not that it got me that close at home.

 

[Cresby]

have a star. Ta.

 

[Cresby]

update - if you type in the line it offers parameters which get a good explanation in VBA help - eg you can define the file name and it will fill the filename box ready for the user. The help file expands on all the dialogs but strangely (office 2000) does not include the hyperlink dialog

viz
Application.Dialogs(xlDialogPrint).Show filename