use vba to copy to table to existing workbook 1

[nickjar2]

There may be an easier way but this is what i have so far:

Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet

' Open Excel
Set xlApp = Excel.Application

' Open Performance workbook
Set xlBook = xlApp.Workbooks.Open("c:\performance\test.xls&quot

' Add a new sheet
Set xlSheet = xlBook.Sheets.Add
' Rename the new sheet
xlSheet.Name = "testing"
xlApp.Visible = True

I have a table AND an existing xls file. I would like to export the data into a NEW sheet within the existing xls sheet.
I thought maybe the transfertext method, but it doesn't look like it will do the job.

Cheers 4 any help on this! Nick (Everton Rool OK!)

 

[ETID]

You would not need to export the data to excel, (too many steps)...

Create a live link to your table from within excel,

From excel click Data>Get External Data>New Database Query>....the choose msaccess databases* from the list...follow the wizard from there (to select your mdb and table within)then return your data to excel.

You can opt to use the wizard or MsQuery at this point...
after your comfy with this...I would recommend the MsQuery option.


when data is returned,...you can right click in the data and select properties such as refresh on open...fill formulas etc.

This way everytime you open the xls, any changed or new data in the table comes in... ;-)

 

[nickjar2]

cheers m8,

I knew there was an easier way. I have done it like this b4.

Thnx again. Nick (Everton Rool OK!)