Inserting data into a seperate Excel workbook

[Guest]

All,
I am interested in having a macro from Excel Workbook A insert data into
seperate and unopened Workbook B. Workbook B's name will vary. Is this
possible and if so how should I go about it? Thanks in advance.

 

[anonymousA]

Hi,

to work on a close file, you must use the ADO technique.
Have a look in Excel VBA Help on ADO. By this way, you can extract datas or
update them.There are some examples.

So long

 

[Guest]

If it is acceptable to open Workbook B, insert the data and close it at the
speed of light, without the user seeing any change then you can use something
like:

Sub InsertData()
Dim strFile as String
strFile = "xxx"
Application.ScreenUpdating=False
With Workbooks
.Open(strFile)
'your code to insert data
.Range("MyDestinationRange")="Hello"
.Close True
End with
End Sub

 

[onedaywhen]

If it is acceptable to open Workbook B, insert the data and close it at the
speed of light, without the user seeing any change then you can use something
like <snip>

Often it takes a little longer to open a workbook <g>. Here is the ADO
equivalent for a *closed* workbook:

Sub jtest()
Dim strFile As String
strFile = "xxx"
Dim con As Object
Set con = CreateObject("ADODB.Connection")
With con
..Open _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Extended Properties='Excel 8.0;HDR=NO';" & _
"Data Source=" & strFile
..Execute "UPDATE MyDestinationRange SET F1 = 'Hello';"
..Close
End With
End Sub

Jamie.

--

 

[Guest]

Hmmm, just copied/pasted the code into the editor and got an error stating
"Compile Error: Expected: Identifier or bracketed expression"

Any ideas on what this is from? Thanks in advance.