Exporting data to Excel into predefined format (1 Viewer)

A

atol

Registered User.

Local time

Today, 02:24

Joined

Aug 31, 2007

Messages

64

• Mar 7, 2008

• #1

Hello,

I have a quiery with about 4 to 5 fileds (columns). I would like to export the data to an Excel format (which have about 100+ colimns). The data will fill in just a 4 - 5 columns in Excel (and the rest Excel columns are not changing and no data is feeding to). The question is - can this be done, and what is the best way to handle it... Basically, the Excel represent a loader that updates another system; That's the reason why I need to do this excercise.
Any advice is greatly appreciated !
Rgds,
Atol

 

boblarson

Smeghead

Local time

Today, 02:24

Joined

Jan 12, 2001

Messages

32,059

• Mar 7, 2008

• #2

1. Create a query that pulls the data you want.

2. Use the Excel COM object model to do the work:

Code:Copy to clipboard

Dim objXL As Object
Dim xlWB As Object
Dim xlWS As Object
Dim rst As DAO.Recordset

Set objXL=CreateObject("Excel.Application")

Set xlWB = objXL.Workbooks.Open("YourFileAndPathHereToTheExistingWorkbook")

Set xlWS = xlWB.Worksheets("YourWorksheetNameHere")

Set rst = CurrentDb.OpenRecordset("YourQueryNameFromStep1Here")

xlWS.Range("B3").CopyFromRecordset rst

xlWB.Save

rst.Close
Set rst=Nothing

I believe that should work for you. Where I have B3 under RANGE you substitute the starting cell for you to copy the records into. You don't need the full range. So, if your data starts at G4 and your 5 columns are G, H, I, J, K then you just put in G4 for the range object and it will fill based on the query.

 

A

atol

Registered User.

Local time

Today, 02:24

Joined

Aug 31, 2007

Messages

64

• Mar 7, 2008

• #3

Bob,
Thanks a lot ! I will try the code tonight!


Rgds,
Atol

 

A

atol

Registered User.

Local time

Today, 02:24

Joined

Aug 31, 2007

Messages

64

• Mar 8, 2008

• #4

Bob,
I just tried the code. It did work fine, however it seems it keeps the respective Excel file in a read-only status. When I wanted to delete that excel file, it wouldn't let me. How can I close that Excel workbook so I can delete it?
And the second part of the question is regarding this part below:

xlWS.Range("B3").CopyFromRecordset rst ;

Unfotrunatelly, the Excel fileds I need to export to are not in a range, but rather in different columns, let's say B2, G2, M2, AX2......is there a way to change that code so it can export in those not-in-a-range format?
Thanks again Bob.

Atol

 

boblarson

Smeghead

Local time

Today, 02:24

Joined

Jan 12, 2001

Messages

32,059

• Mar 8, 2008

• #5

You can use separate queries to put the data into the spots. Just do the

Set rst = CurrentDb.OpenRecordset("YourQueryNameHere")


Then do the CopyFromRecordset and then do

rst.Close
Set rst = CurrentDb.OpenRecordset("YourNextQueryNameHere")

Then do the copy from recordset to the place you want for the next one and so on and so on.

 

A

atol

Registered User.

Local time

Today, 02:24

Joined

Aug 31, 2007

Messages

64

• Mar 8, 2008

• #6

Bob, very good idea. Thanks again.

Rgds.
Atol