Copying from an Excel table to another worksheet

[RomeERome]

Hello All,

I'm trying to copy data from a table in Excel 2007 that is linked to a query in Access 2007 into another worksheet. If I open the file using Excel, it doesn't seem to have a problem. If I use VBA, and open this file from an Access 2007 database, then it gives me a 1004 runtime error, and tells me that the copy method failed.

Here is my code.

    Sheets("Data").Range("database1.accdb[#Headers").Copy
    Sheets("Data1").Range("A3").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
    Sheets("Data").Range("database1.accdb").Copy
    Sheets("Data1").Range("A4").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
    Sheets("Data").Range("database1.accdb").AutoFilter

It's driving me bananas, that this only happens when it's opened from Access 2007.

Any help will be greatly appreciated.

 

[SkipVought]

hi,

If I use VBA, and open this file from an Access 2007 database...

In Access VBA, you must have a reference to the workbook you want to manipulate. Access VBA has no idea what you are referring to.

You must either set a reference to MS Excel Object Library or use CreateObject method to create an Excel Application object.

Do you understand objects?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[RomeERome]

Hi Skip,

Thanks for your quick reply!!!

Here is my code that opens the Excel file from Access 2007. Once it opens the spreadsheet, it closes the database.

Private Sub Command5_Click()
    Dim xlApp As Excel.Application
    Dim sht As Excel.Worksheet
    Dim strFilename As String
    
    strFilename = "[Drive Letter:\Path\Tracking.xlsm"
    
    Set xlApp = CreateObject("Excel.Application") 'Create an instance of Excel
    xlApp.Workbooks.Open (strFilename) 'Open the file
    Set sht = xlApp.ActiveWorkbook.Sheets(1)
    sht.Activate
    xlApp.Application.Visible = True
    
    Set xlApp = Nothing
    Set sht = Nothing
    DoCmd.Quit
End Sub

By this code, Access knows the file.

Here's the rest of the story.

After the Excel file opens, then it refreshes a querytable, and copies that querytable data into another worksheet so that further calculations and manipulation can take place.

That's where the code that I showed you first comes into play.

 

[RomeERome]

    Sheets("Data").Range("database1.accdb[#Headers]").Copy
    Sheets("Data1").Range("A3").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
    Sheets("Data").Range("database1.accdb").Copy
    Sheets("Data1").Range("A4").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
    Sheets("Data").Range("database1.accdb").AutoFilter

Some of my code didn't make it so I'm reposting it again.

 

[SkipVought]

Well, your code references the first sheet in the workbook.

Set sht = xlApp.ActiveWorkbook.Sheets(1)

You must do the same for Data and Data1 in order to reference them correctly.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[RomeERome]

Skip,

I thought that once the workbook was opened, I could refer to any worksheet that was in my workbook since Access is now closed.

In the code sample that you show above, I was just telling Access to activate the first worksheet when it opened the file.

That first worksheet is a user guide that has an activex button to take the user to the worksheet where the other functions take place.

The Data worksheet has all of the data in a table that is linked to Access, and Data1 is where I want the data to be copied, but Excel is giving me a runtime error because it says that the copy method failed.

So my ultimate question is in reference to my syntax. Is there a better way to copy the table data. Obviously there is an error in my code, but all the sources that I tried to look at says that my code should work, but it doesn't.

 

[SkipVought]

I thought that once the workbook was opened, I could refer to any worksheet that was in my workbook since Access is now closed.

Where does your VBA code reside?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[RomeERome]

It resides in a subroutine called Workbook_Open so that when the workbook opens, it can refresh the data and copy it.

 

[SkipVought]

I was looking for the APPLICATION that your code is in.

I ASSUME that you mean that the code in question in NOT in MS Access but rather in MS Excel, in the Workbook_Open event.

Then why are you even trying to do this from MS Access?

Whenever the workbook is opened, the code will execute!!!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[RomeERome]

I am not trying to do it from Access. Skip, I was telling you the whole process. Access opens the file. Then after the file is opened, Access closes, and only the Excel file is open.

Then the code runs to refresh a querytable in worksheet called Data in Excel, and then copies that refreshed data into another worksheet called Data1.

 

[SkipVought]

  with thisworkbook.
'clear the target area
    .Sheets("Data1").Range("A3").CurrentRegion.Clear
'copy the entire source
    .Sheets("Data").UsedRange.Copy
'paste values and number formats
    .Sheets("Data1").Range("A3").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
  end with

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[RomeERome]

Thanks Skip! I'll try it and let you know.

 

[RomeERome]

Thanks Skip. It worked great!!

 

[SkipVought]

I should not have had a PERIOD after the ThisWorkbook object reference. Sorry!

  with thisworkbook
'clear the target area
    .Sheets("Data1").Range("A3").CurrentRegion.Clear
'copy the entire source
    .Sheets("Data").UsedRange.Copy
'paste values and number formats
    .Sheets("Data1").Range("A3").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
  end with

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[RomeERome]

Yeah, I caught that, and fixed it. Thanks a million away!!