Export Access to Excel : from a specific row

[ragsgold]

Hello All,

I have Access 2010 and I have 46 queries each one is a specific site data hence 46. I export them to excel and send it to customer. Everything so far was fine until two new requirements got included. New template was designed with a picture Logo in Row A1; row 2 has a note; row 3 has the field names (slightly re-worded compared to queries but the positions remain same); row 4 is where the data starts. Now I want my macro (which i have created to export these 46 queries into excel) to export the data alone (without field names) starting from row 4. I already have these 46 uniquely named excel files (goes by site name which is what is defined in the macro export command too). Now I dont know how to tell access to export data only from row 4

Can Anyone please help I need to run these reports tomorrow and trying to get a fix for this process so that I need not copy paste from excel to excel.

Many Thanks in advance

Raghav

[pbaldy]

You can use automation and CopyFromRecordset. See that section here:

http://support.microsoft.com/kb/247412

noting this line, which is telling it where to start:


oSheet.Range("A1").CopyFromRecordset rs

[ragsgold]

Hi Paul,

I am sorry I am new to VB and not quite sure as to how I can use this suggestion. I have a macro where I have used 46 importexport spreadsheet giving each site two outputs which when its exported its exported into two sheets with the file name as site name. You would see a file like Open Orders New York and when you open the file you would see two sheets Openorders and No PO each exported from two different queries.

Can this recordset command use the macro and export in row 4 ? if yes please help how I can code the VB script.


Thanks again

[pbaldy]

I don't use macros, but to my knowledge you can't specify the starting row in one.

[ragsgold]

Hi Paul,

Is there a way to rewrite this in VB. All I do in the macro is as below ...is there a way you can give a sample VB equivalent action as below ?

Importexport (standard macro command)

Spreadsheet type : Excel


Table name: I give my query name from which it pulls data to be exported.Each site has two tabs so two queries I give the two fifferent query names but the below file name is same so that it creates 1 file two tabs.

Destination location and file name: I give the folder where i want the data file to be exported with a unique file name for each site.