Transfer Data To Excel

[Guest]

I created a query in Access that I need to export to excel. I have found I can do this through the output to action, and it works fine. The problem is once I have the query in excel I want some excel macros to run the minute excel opens. I know how to do this for an existing file, but the problem is when you output to excel it copies over the old file, thereby erasing the pre-loaded macros I had in there.

My solution was that as the data was being exported, I opened a different excel workbook, and then copied the data just exported into this workbook, where the macros could then run. I am running into problems getting the sheet to copy over though, as the commands that usually do that in excel vb I am getting errors for here. Here is the code I was using:

Public Function test()
Dim objXL As Object, x
Set objXL = CreateObject("Excel.Application")
With objXL.Application
.Visible = True
.Workbooks.Open "C:\Documents and Settings\p0007617\My Documents\testing.xls"
.Worksheets("List").Copy
.Workbooks.Open "C:\Documents and Settings\p0007617\My Documents\Book2.xls"
.Worksheets("Destination").Activate
.Activesheet.Paste Destination:= _
.Worksheet("Destination")
End With
end function

It won't recognize the .Activesheet command. I got stuck here and couldnt figure out how to circumvent this problem. Any help would be appreciated.

Jim

 

[Ken Snell]

Jim, I will be replying to your earlier thread later today...am tied up on
some things right now, though.


--
Ken Snell
<MS ACCESS MVP>

I created a query in Access that I need to export to excel. I have found I

can do this through the output to action, and it works fine. The problem is
once I have the query in excel I want some excel macros to run the minute
excel opens. I know how to do this for an existing file, but the problem is
when you output to excel it copies over the old file, thereby erasing the
pre-loaded macros I had in there.

My solution was that as the data was being exported, I opened a different

excel workbook, and then copied the data just exported into this workbook,
where the macros could then run. I am running into problems getting the
sheet to copy over though, as the commands that usually do that in excel vb
I am getting errors for here. Here is the code I was using:

Public Function test()
Dim objXL As Object, x
Set objXL = CreateObject("Excel.Application")
With objXL.Application
.Visible = True
.Workbooks.Open "C:\Documents and Settings\p0007617\My Documents\testing.xls"
.Worksheets("List").Copy
.Workbooks.Open "C:\Documents and Settings\p0007617\My Documents\Book2.xls"
.Worksheets("Destination").Activate
.Activesheet.Paste Destination:= _
.Worksheet("Destination")
End With
end function

It won't recognize the .Activesheet command. I got stuck here and couldnt

figure out how to circumvent this problem. Any help would be appreciated.

 

[Ken Snell]

My error..... The other, earlier thread that has almost the same question is
from another person and in another newsgroup....

Sorry.

 

[Ken Snell]

I will post a reply to you shortly. I'm working on that other one now!

--
Ken Snell
<MS ACCESS MVP>

Ken,

If there is a similar question posed on a different newsgroup, could you

please direct me to where that is so maybe I could also get some answers?
Thanks.

 

[Ken Snell]

When using VBA automation from ACCESS to run EXCEL, it's always best to
fully reference sheets, workbooks, etc. Instead of using the ActiveSheet
object, replace it with specific reference to the worksheet: Try replacing
these lines

.Worksheets("List").Copy
.Workbooks.Open "C:\Documents and Settings\p0007617\My
Documents\Book2.xls"
.Worksheets("Destination").Activate
.Activesheet.Paste Destination:= _
.Worksheet("Destination")

with these lines

.Workbooks.Open "C:\Documents and Settings\p0007617\My
Documents\Book2.xls"
.Workbooks("testing.xls").Worksheets("List").Copy
..Workbooks("Book2.xls").Worksheets("Destination")

This should create a new worksheet in the Book2.xls file in front of the
Destination worksheet; this new sheet will have all the data that were on
the List workhseet in the testing.xls file.

Alternatively, you could copy the contents of the worksheet onto the other
worksheet:

.Workbooks.Open "C:\Documents and Settings\p0007617\My
Documents\Book2.xls"
.Workbooks("testing.xls").Worksheets("List").Cells.Copy
..Workbooks("Book2.xls").Worksheets("Destination").Cells

--
Ken Snell
<MS ACCESS MVP>

I created a query in Access that I need to export to excel. I have found I

can do this through the output to action, and it works fine. The problem is
once I have the query in excel I want some excel macros to run the minute
excel opens. I know how to do this for an existing file, but the problem is
when you output to excel it copies over the old file, thereby erasing the
pre-loaded macros I had in there.

My solution was that as the data was being exported, I opened a different

excel workbook, and then copied the data just exported into this workbook,
where the macros could then run. I am running into problems getting the
sheet to copy over though, as the commands that usually do that in excel vb
I am getting errors for here. Here is the code I was using:

Public Function test()
Dim objXL As Object, x
Set objXL = CreateObject("Excel.Application")
With objXL.Application
.Visible = True
.Workbooks.Open "C:\Documents and Settings\p0007617\My Documents\testing.xls"
.Worksheets("List").Copy
.Workbooks.Open "C:\Documents and Settings\p0007617\My Documents\Book2.xls"
.Worksheets("Destination").Activate
.Activesheet.Paste Destination:= _
.Worksheet("Destination")
End With
end function

It won't recognize the .Activesheet command. I got stuck here and couldnt

figure out how to circumvent this problem. Any help would be appreciated.