Question on Excel Report 1

[AlexCuse]

Hi All,

I am working on reporting for a new program we have taken on, and trying to get a handle on the best way to put together a report in excel. What needs to be included in this report is the output from 4 queries. These will be of various row counts, but I will know the maximum possible number of rows for each.

Basically, I want to open excel, copy a 'Template' worksheet and rename it with today's date. This I am OK on I think.

After this however, I want to export four queries to various positions on the new tab. AFAIK, I cannot use TransferSpreadsheet with two 'range' values (one for the name of the tab to place it on, one for the starting position of where my output should go).

Any advice on how to do this would be greatly appreciated. I have a method that I've used (it is a bit of a hack) that is very d-lookup intensive and as a result very slow. I would prefer not to use this if I can avoid it.

Thanks,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[SkipVought]

AlexCuse,

Since this is an Excel VBA question I'd suggest posting in VBA Visual Basic for Applications (Microsoft) forum707.

Skip,

_[red][/red]

 

[SkipVought]

You can use MS Query in Excel via Data/Get External Data/New Database Query -- MS Access...

You can you your 4 QueryTables on the same sheet. If the Criteria for each query is static, (ie, you don't need to supply any parameters on the fly) you can simple Data/Refresh each QT to get a new set of data. There are Data/Data Range Properties that can be set to refresh the data as desired.

Skip,

_[red][/red]

 

[AlexCuse]

Skip -

I had not thought of using MS Query. Is refreshing the data on the new tab (through access) a simple task?

I am thinking this would be my best approach, let me know what you think of it (I need to do this all through Access, that is why I did not post in VBA forum)

1. Create 'template' page, with MS Queries on here.
2. Open Excel/Refresh Data on 'template'/create new tab named today's date
3. Copy everything from 'template' and Paste Special --> values into today's tab (this would give end users static values that they are less likely to be able to mess up). Slightly less anyway ;-)
4. Save, Close Excel.

Does this sound good to you?

I am pretty good on everything I listed here, except for refreshing the data (I think Macro recorder should help for that).

Thanks,

Alex


Ignorance of certain subjects is a great part of wisdom

 

[SkipVought]

I do not understand why this has to be done from Access. Can you explain that to me?

Skip,

_[red][/red]

 

[AlexCuse]

The report is for a Fulfillment processing stream. We receive from a vendor files full of name and address information for people who call and request information about certain products. This information is processed through an access application, and then stored once ready for mailing in a SQL Server back end. I need to do it through access because I want the report to be created automatically while the application is being run, and then emailed after the last step. Sorry that I did not make this more clear.

Thanks for your ideas, and I will let you know how I get on with it,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[SkipVought]

OK makes sense.

You can do some of the Excel coding using the macro recorder - - copy the sheet, for instance.

Skip,

_[red][/red]

 

[AlexCuse]

Skip -

Thanks so much for the idea to use MS Queries. This is very much faster than my hacked method was. I really appreciate your insight, it helped me tremendously.

You are a star, as usual!

Alex

Ignorance of certain subjects is a great part of wisdom