Transfer Data To Excel

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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>

Jim Sagris said:
I created a query in Access that I need to export to excel. I have found I
Click to expand...
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
Click to expand...
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
Click to expand...
figure out how to circumvent this problem. Any help would be appreciated.
 
My error..... The other, earlier thread that has almost the same question is
from another person and in another newsgroup....

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

--
Ken Snell
<MS ACCESS MVP>

Jim Sagris said:
Ken,

If there is a similar question posed on a different newsgroup, could you
Click to expand...
please direct me to where that is so maybe I could also get some answers?
Thanks.
 
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>


Jim Sagris said:
I created a query in Access that I need to export to excel. I have found I
Click to expand...
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
Click to expand...
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
Click to expand...
figure out how to circumvent this problem. Any help would be appreciated.
 
Back
Top