importing data

Hello,

Can anyone tell me the best/easiest way to transfer/link data between two
Excel files under the following conditions?:

-the source file is always open.
-the data on the source file is dynamic and continuously changing.
-the source file is not saved often (the changing data is not captured by
saving).
-the destination file is always open on another machine.
-the destination file must capture the changing source data within a short
period of time (1-5 min)

Macro? ODBC link? Any ideas appreciated.
Nathan

Nathan wrote:
Can anyone tell me the best/easiest way to transfer/link data between two
Excel files under the following conditions?:


Sounds like a job for NetDDE (not that I have used it myself).
Try this for some pointers:
http://www.angelfire.com/biz/rhaminisys/ddeinfo.html

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

nathan wrote in message ...
Hello,

Can anyone tell me the best/easiest way to transfer/link data between two
Excel files under the following conditions?:

-the source file is always open.
-the data on the source file is dynamic and continuously changing.
-the source file is not saved often (the changing data is not captured by
saving).

Macro in source workbook to regularly send updates to a remote database...

-the destination file is always open on another machine.
-the destination file must capture the changing source data within a short
period of time (1-5 min)

.... destination file to regularly poll the remote database.

Jamie.

--

That is exactly what I was thinking. Do you have any further suggestions on
how to update from the source to the remote database? I was thinking of
using a text file for the remote database. So, would I write a macro to
basically open the database, copy paste from the source, then save/close the
database? I don't want the source user to be interrupted or affected by the
process. Can it be done behind the scenes, maybe turn remote reference off??

Thanks.

"Jamie Collins" wrote:

nathan wrote in message ...
Hello,

Can anyone tell me the best/easiest way to transfer/link data between two
Excel files under the following conditions?:

-the source file is always open.
-the data on the source file is dynamic and continuously changing.
-the source file is not saved often (the changing data is not captured by
saving).

Macro in source workbook to regularly send updates to a remote database...

-the destination file is always open on another machine.
-the destination file must capture the changing source data within a short
period of time (1-5 min)

.... destination file to regularly poll the remote database.

Jamie.

--

nathan wrote in ...

That is exactly what I was thinking. Do you have any further suggestions on
how to update from the source to the remote database? I was thinking of
using a text file for the remote database.

I would use ADO to update the database:

Sub Main()
UpdateSource Now, 1, "One"
End Sub

Private Function UpdateSource( _
ByVal MyDateTime As Date, _
ByVal MyInteger As Long, _
ByVal MyText As String _
) As Boolean

Const DB_PATH As String = "C:\Tempo\"

Dim oConn As Object
Set oConn = CreateObject("ADODB.Connection")

With oConn
.CursorLocation = 3 ' adUseClient
.ConnectionString = "" & _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & DB_PATH & ";" & _
"Extended Properties='Text;HDR=YES'"
.Open

Dim strSql As String
' strSql = "" & _
' "CREATE TABLE db#txt (MyDateTimeCol DATETIME NULL," & _
' " MyIntCol INTEGER NULL, MyTextCol VARCHAR(255) NULL);"
strSql = "" & _
"INSERT INTO db#txt" & _
" (MyDateTimeCol, MyIntCol, MyTextCol) VALUES (" & _
"'" & Format$(MyDateTime, "yyyy-mm-dd hh:mm:ss") & "'" & _
"," & CStr(MyInteger) & _
",'" & MyText & "');"

Dim lngRecordsAffected As Long
.Execute strSql, lngRecordsAffected

.Close

End With
UpdateSource = CBool(lngRecordsAffected = 1)
End Function

Could be considered overkill for a log file but this article is in
agreement::

http://msdn.microsoft.com/library/de...ng03092004.asp

Also it would be very scalable i.e. fewer changes required when you
move to a proper database.

Speaking of which, rather than a text file, I'd start with a Jet
database i.e. a .mdb file:

Option Explicit

Private Function CreateJetDB() As Boolean

Const PATH As String = "" & _
"C:\Temp\"
Const FILENAME_JET As String = "" & _
"New_Jet_DB.mdb"

Const CONN_STRING_JET As String = "" & _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=PATHFILENAME"

' Build connection string
Dim strConJet As String
strConJet = CONN_STRING_JET
strConJet = Replace(strConJet, "PATH", PATH)
strConJet = Replace(strConJet, "FILENAME", FILENAME_JET)

' Create DB
Dim Cat As Object
Set Cat = CreateObject("ADOX.Catalog")
Cat.Create strConJet

' 'Inherit' connection
Dim Con As Object
Set Con = Cat.ActiveConnection
Set Cat = Nothing

' Create table
Dim strSql As String
strSql = "" & _
"CREATE TABLE MyTable (" & _
" MyDateTimeCol DATETIME NOT NULL," & _
" MyIntCol INTEGER NOT NULL DEFAULT 0," & _
" MyTextCol VARCHAR(255) NOT NULL DEFAULT '{{NA}}');"

With Con
.Execute strSql
.Close
End With

CreateJetDB = True

End Function

The advantage of a Jet database is it has many features of a 'proper'
database (constraints, referential integrity, indexes, etc) while
still being a single, freely-distributable file.

Jamie.

--

Thanks Jamie. I'm not very advanced and some fundamental questions: where
do I put the code for the private function "CreateJetDB"? In the source file
(.xls)? In a standard module, or ThisWorkbook? Will this function execute
automatically or will I have to use it in the spreadsheet somewhere? Thanks
for your patience.

"Jamie Collins" wrote:

nathan wrote in ...

That is exactly what I was thinking. Do you have any further suggestions on
how to update from the source to the remote database? I was thinking of
using a text file for the remote database.

I would use ADO to update the database:

Sub Main()
UpdateSource Now, 1, "One"
End Sub

Private Function UpdateSource( _
ByVal MyDateTime As Date, _
ByVal MyInteger As Long, _
ByVal MyText As String _
) As Boolean

Const DB_PATH As String = "C:\Tempo\"

Dim oConn As Object
Set oConn = CreateObject("ADODB.Connection")

With oConn
.CursorLocation = 3 ' adUseClient
.ConnectionString = "" & _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & DB_PATH & ";" & _
"Extended Properties='Text;HDR=YES'"
.Open

Dim strSql As String
' strSql = "" & _
' "CREATE TABLE db#txt (MyDateTimeCol DATETIME NULL," & _
' " MyIntCol INTEGER NULL, MyTextCol VARCHAR(255) NULL);"
strSql = "" & _
"INSERT INTO db#txt" & _
" (MyDateTimeCol, MyIntCol, MyTextCol) VALUES (" & _
"'" & Format$(MyDateTime, "yyyy-mm-dd hh:mm:ss") & "'" & _
"," & CStr(MyInteger) & _
",'" & MyText & "');"

Dim lngRecordsAffected As Long
.Execute strSql, lngRecordsAffected

.Close

End With
UpdateSource = CBool(lngRecordsAffected = 1)
End Function

Could be considered overkill for a log file but this article is in
agreement::

http://msdn.microsoft.com/library/de...ng03092004.asp

Also it would be very scalable i.e. fewer changes required when you
move to a proper database.

Speaking of which, rather than a text file, I'd start with a Jet
database i.e. a .mdb file:

Option Explicit

Private Function CreateJetDB() As Boolean

Const PATH As String = "" & _
"C:\Temp\"
Const FILENAME_JET As String = "" & _
"New_Jet_DB.mdb"

Const CONN_STRING_JET As String = "" & _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=PATHFILENAME"

' Build connection string
Dim strConJet As String
strConJet = CONN_STRING_JET
strConJet = Replace(strConJet, "PATH", PATH)
strConJet = Replace(strConJet, "FILENAME", FILENAME_JET)

' Create DB
Dim Cat As Object
Set Cat = CreateObject("ADOX.Catalog")
Cat.Create strConJet

' 'Inherit' connection
Dim Con As Object
Set Con = Cat.ActiveConnection
Set Cat = Nothing

' Create table
Dim strSql As String
strSql = "" & _
"CREATE TABLE MyTable (" & _
" MyDateTimeCol DATETIME NOT NULL," & _
" MyIntCol INTEGER NOT NULL DEFAULT 0," & _
" MyTextCol VARCHAR(255) NOT NULL DEFAULT '{{NA}}');"

With Con
.Execute strSql
.Close
End With

CreateJetDB = True

End Function

The advantage of a Jet database is it has many features of a 'proper'
database (constraints, referential integrity, indexes, etc) while
still being a single, freely-distributable file.

Jamie.

--

nathan wrote ...

I'm not very advanced and some fundamental questions: where
do I put the code for the private function "CreateJetDB"? In the source file
(.xls)? In a standard module, or ThisWorkbook? Will this function execute
automatically or will I have to use it in the spreadsheet somewhere?

You can run it from any code module in any workbook :-)

Try using a standard module in a new blank workbook. Add a public sub
to call it e.g.

Public Sub Test()
CreateJetDB
End Sub

You can call this in a variety of ways e.g. from the menu choose,
Tools | Macros.

Jamie.

--

I' am following this thread too Jamie...
And how do we update (add/delete/edit) items in the *.mdb file via the same
excel book?


"Jamie Collins" wrote in message
om...
nathan wrote ...

I'm not very advanced and some fundamental questions: where
do I put the code for the private function "CreateJetDB"? In the source
file
(.xls)? In a standard module, or ThisWorkbook? Will this function
execute
automatically or will I have to use it in the spreadsheet somewhere?

You can run it from any code module in any workbook :-)

Try using a standard module in a new blank workbook. Add a public sub
to call it e.g.

Public Sub Test()
CreateJetDB
End Sub

You can call this in a variety of ways e.g. from the menu choose,
Tools | Macros.

Jamie.

--

"J_J" wrote ...

how do we update (add/delete/edit) items in the *.mdb file via the same
excel book?

Using an ADO connection to your database, I can think of two ways:

1) Use SQL DML e.g.

To add a row:
INSERT INTO MyTable (MyKeyCol, MyDataCol) VALUES (55, 'Tinatotac');

To amend a row:
UPDATE MyTable SET MyDataCol = 'Katewudes' WHERE MyKeyCol=55;

To delete a row:
DELETE FROM MyTable WHERE MyKeyCol=55;

2) Use an updatable recordset.

Open the recordset using an updateable query:

SELECT MyKeyCol, MyDataCol FROM MyTable;

To add a row: use the recordset's AddNew method:

rs.AddNew Array("MyKeyCol", "MyDataCol"), Array(55, "Tinatotac")

To amend a row: navigate to the required row (e.g. using the
recordset's Filter method) and change the Field values:

rs.Filter = "MyKeyCol = 55"
rs.Fields("MyDataCol").Value = "Katewudes"

To delete a row: use the recordset's Delete method:

rs.Filter = "MyKeyCol = 55"
rs.Delete 1 ' (adAffectCurrent)

When done, use the recordset's Update (or UpdateBatch) to propagate
the change to the database.

The recordset simply writes the SQL DML equivalents for you so the
choice basically comes down to whether you require control or
convenience. If an individual change will affect multiple rows e.g.

DELETE FROM MyTable WHERE MyKeyCol 99;

then the SQL DML approach is better.

Jamie.

--