Eport Excel data to closed Excel Workbook

S

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
 
S

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
 
B

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

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
 
S

Spike

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

Spike


Patrick Molloy said:
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 said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top