Import data macro
 

I have an Access2002sp3 Access.mdb database with a macro in it which
exports data to a costdata.xls workbook or file (it has only one sheet in
it).

Is it possible to have a macro in my excel2002sp3 report.xls workbook to
import the data from the costdata.xls file into a new sheet created in the
report.xls workbook ?

thanks .. Roger

Import data macro
 

Yes, you can setup a routine that first will open the file costdata.xls copy
Sheet1 into report.xls and then close the costdata.xls file to keep it as
the original set of data.
--
If this posting was helpful, please click on the Yes button.
Regards,

Michael Arch.




"Ro477" wrote:

I have an Access2002sp3 Access.mdb database with a macro in it which
exports data to a costdata.xls workbook or file (it has only one sheet in
it).

Is it possible to have a macro in my excel2002sp3 report.xls workbook to
import the data from the costdata.xls file into a new sheet created in the
report.xls workbook ?

thanks .. Roger

Import data macro
 

Not a macro but you can probaly record on from this:

http://www.ehow.com/how_11796_add-delete-excel.html

Rui

"Ro477" wrote in message
...
I have an Access2002sp3 Access.mdb database with a macro in it which
exports data to a costdata.xls workbook or file (it has only one sheet in
it).

Is it possible to have a macro in my excel2002sp3 report.xls workbook to
import the data from the costdata.xls file into a new sheet created in the
report.xls workbook ?

thanks .. Roger

Import data macro
 

Why not just pull the data from excel like this. You will need to change a
few things

Option Explicit
Sub saleData()
'Needs reference the Axtive X Library 2.0 or higher
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim fld As ADODB.Field
Dim SODrng As Range
Dim iCol As Long
Dim sSQL As String
Dim strConn As String

'C:\PathToYourMdb\Ilsa.mdb (Change)
strConn = "Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=C:\PathToYourMdb\databasename.mdb;Persist Security
Info=False"
Set cnn = New ADODB.Connection
cnn.Open strConn

'sSQL = "Replace with your query"
sSQL = "SELECT Field1 From TableName"

Set rs = New ADODB.Recordset
rs.Open sSQL, cnn, adOpenStatic, adLockOptimistic

iCol = 1
For Each fld In rs.Fields
ActiveSheet.Cells(1, iCol) = fld.Name
iCol = iCol + 1
Next
Set SODrng = ActiveSheet.Range("A2")
SODrng.CopyFromRecordset rs
Cells.EntireColumn.AutoFit

rs.Close
Set rs = Nothing
cnn.Close
Set cnn = Nothing
End Sub

"Ro477" wrote:

I have an Access2002sp3 Access.mdb database with a macro in it which
exports data to a costdata.xls workbook or file (it has only one sheet in
it).

Is it possible to have a macro in my excel2002sp3 report.xls workbook to
import the data from the costdata.xls file into a new sheet created in the
report.xls workbook ?

thanks .. Roger

Import data macro
 

Dear Michael

thanks for your reply, but it lacks the actual detail that I am looking for.
I certainly am able to manually open anothe file and copy/paste the data
into my report.xls sheet, but I am still puzzled in regard to whether it can
be done using a macro. The reply from Mike on this same post is interesting,
and I will try that to see how it goes

.... Roger

ps ... I can see no "YES" button at the foot of your reply !

"Michael" wrote in message
...
Yes, you can setup a routine that first will open the file costdata.xls
copy
Sheet1 into report.xls and then close the costdata.xls file to keep it as
the original set of data.
--
If this posting was helpful, please click on the Yes button.
Regards,

Michael Arch.




"Ro477" wrote:

I have an Access2002sp3 Access.mdb database with a macro in it which
exports data to a costdata.xls workbook or file (it has only one sheet in
it).

Is it possible to have a macro in my excel2002sp3 report.xls workbook to
import the data from the costdata.xls file into a new sheet created in
the
report.xls workbook ?

thanks .. Roger