Eport Excel data to closed Excel Workbook

[Spike]

Is it possible to export data from the active workbook to a closed Excel
workbook. I would like to do this monthly to a different sheet each time. I
know to import data the range has to be named so i assume it is the same
priciple.

If this is possible i would greatly appreciate the relevant code

 

[Spike]

Yes i am trying to avoid opening it, and thought ADO or DAo would do the job
as oppposed to copy etc but guess i will have to go downt that route then

 

[Barb Reinhardt]

The workbook may be closed to begin with, but I suspect you'll have to open
it to import and save your date. That can be done programmatically. Is
that what you mean?

 

[Patrick Molloy]

you can read/write to excel workbooks as if to a daabase...here's some simple
code - no error trapping - to give you the idea...the spreadsheet named as
database has a nambed range, "testdata" on sheet1

Sub WriteData()
Dim Conn As ADODB.Connection
Dim strConn As String
Dim sExcelSourceFile As String

sExcelSourceFile = "E:\Excel\Excel_database\Testdatabase.xls"

strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel
8.0;"
strConn = strConn & "Data Source="
strConn = strConn & sExcelSourceFile

Set Conn = New ADODB.Connection
Conn.Open strConn

With New Command
.ActiveConnection = Conn
.CommandText = "INSERT into testdata ([KEYV],[PROD],[COST])values(
..999,'X',.888)"
.CommandType = adCmdText
.Execute
End With

Conn.Close
Set Conn = Nothing
End Sub

 

[Spike]

That looks extremely helpful i am most grateful
--
with kind regards

Spike

you can read/write to excel workbooks as if to a daabase...here's some simple
code - no error trapping - to give you the idea...the spreadsheet named as
database has a nambed range, "testdata" on sheet1

Sub WriteData()
Dim Conn As ADODB.Connection
Dim strConn As String
Dim sExcelSourceFile As String

sExcelSourceFile = "E:\Excel\Excel_database\Testdatabase.xls"

strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel
8.0;"
strConn = strConn & "Data Source="
strConn = strConn & sExcelSourceFile

Set Conn = New ADODB.Connection
Conn.Open strConn

With New Command
.ActiveConnection = Conn
.CommandText = "INSERT into testdata ([KEYV],[PROD],[COST])values(
.999,'X',.888)"
.CommandType = adCmdText
.Execute
End With

Conn.Close
Set Conn = Nothing
End Sub

Is it possible to export data from the active workbook to a closed Excel
workbook. I would like to do this monthly to a different sheet each time. I
know to import data the range has to be named so i assume it is the same
priciple.

If this is possible i would greatly appreciate the relevant code