Inserting data into a seperate Excel workbook

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.
--
J

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

"Jon" <[email protected]> a écrit dans le message de news:
[email protected]...
> 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.
> --
> J

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

"Jon" wrote:

> 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.
> --
> J

gocush wrote:
> 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.

--

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.

"onedaywhen" wrote:

>
> gocush wrote:
> > 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.
>
> --
>
>