PUT data in closed workbook

[Max Potters]

Hi all,

I am able to retrieve data with VB code from a closed workbook, but now I
would also like to PUT data in the closed workbook. I am aware of what
ExecuteExcel4Macro's are, but this wont help me I think. Here is my code:

Sub PutDataInClosedFile()
'
filepath = "C:\Documents and Settings\Max\Desktop\temp"
Filename = "batch.xls"
sheetname = "Sheet1"
'
'''''''''''''''''''''''''
For n = 2 To 16
Strg = "'" & filepath & "\[" & Filename & "]" _
& sheetname & "'!" & "r" & n & "c1"
'
''''''code for putting data in closed workbook has to come here''''''''
<========================
'
Next n
'
End Sub

PROBLEM: How to PUT info from a certain workbook (closed or open) to a
(closed or open) other workbook.

I worked all my books through, but can't find the good syntax, or method to
it. The code you see here has a loop. I want to put a range of cells
("A1:A15") to the closed workbook, but all my methods fail!

Hope you have answers

Thanks in advance
Max

 

[Frank Kabel]

Hi
I don't think this is really possible. Why can't you open the workbook
(hide it maybe with application.screenupdating=false)?

 

[Tom Ogilvy]

I agree with Frank that opening the workbook makes the most sense.
However, if your data is organized as database, you can treat it as a
database and update it use SQL and ADO or DAO. I personally haven't done
this, but if you want to explore:

http://www.erlandsendata.no/english/index.php?t=envbadac
Mr. Erlandsen's site.

 

[Max Potters]

Thanks for your replies.

Though I really think this is possible. It's possible to read data from a
closed workbook file and copy this into another workbook, so why wouldn't it
be possible to PUT data in a closed workbook?

Anyway, for others who have (possible) solutions, please post me, or send to
my email: (e-mail address removed)

thanks
max

I agree with Frank that opening the workbook makes the most sense.
However, if your data is organized as database, you can treat it as a
database and update it use SQL and ADO or DAO. I personally haven't done
this, but if you want to explore:

http://www.erlandsendata.no/english/index.php?t=envbadac
Mr. Erlandsen's site.

--
Regards,
Tom Ogilvy

Hi all,

I am able to retrieve data with VB code from a closed workbook, but now I
would also like to PUT data in the closed workbook. I am aware of what
ExecuteExcel4Macro's are, but this wont help me I think. Here is my code:

Sub PutDataInClosedFile()
'
filepath = "C:\Documents and Settings\Max\Desktop\temp"
Filename = "batch.xls"
sheetname = "Sheet1"
'
'''''''''''''''''''''''''
For n = 2 To 16
Strg = "'" & filepath & "\[" & Filename & "]" _
& sheetname & "'!" & "r" & n & "c1"
'
''''''code for putting data in closed workbook has to come here''''''''
<========================
'
Next n
'
End Sub

PROBLEM: How to PUT info from a certain workbook (closed or open) to a
(closed or open) other workbook.

I worked all my books through, but can't find the good syntax, or method to
it. The code you see here has a loop. I want to put a range of cells
("A1:A15") to the closed workbook, but all my methods fail!

Hope you have answers

Thanks in advance
Max

 

[Ron de Bruin]

Hi Max

Tom give you a link to Ole's site
http://www.erlandsendata.no/english/index.php?d=envbadacwbdbado



--
Regards Ron de Bruin
http://www.rondebruin.nl

Thanks for your replies.

Though I really think this is possible. It's possible to read data from a
closed workbook file and copy this into another workbook, so why wouldn't it
be possible to PUT data in a closed workbook?

Anyway, for others who have (possible) solutions, please post me, or send to
my email: (e-mail address removed)

thanks
max

I agree with Frank that opening the workbook makes the most sense.
However, if your data is organized as database, you can treat it as a
database and update it use SQL and ADO or DAO. I personally haven't done
this, but if you want to explore:

http://www.erlandsendata.no/english/index.php?t=envbadac
Mr. Erlandsen's site.

--
Regards,
Tom Ogilvy

Hi all,

I am able to retrieve data with VB code from a closed workbook, but now I
would also like to PUT data in the closed workbook. I am aware of what
ExecuteExcel4Macro's are, but this wont help me I think. Here is my code:

Sub PutDataInClosedFile()
'
filepath = "C:\Documents and Settings\Max\Desktop\temp"
Filename = "batch.xls"
sheetname = "Sheet1"
'
'''''''''''''''''''''''''
For n = 2 To 16
Strg = "'" & filepath & "\[" & Filename & "]" _
& sheetname & "'!" & "r" & n & "c1"
'
''''''code for putting data in closed workbook has to come here''''''''
<========================
'
Next n
'
End Sub

PROBLEM: How to PUT info from a certain workbook (closed or open) to a
(closed or open) other workbook.

I worked all my books through, but can't find the good syntax, or method to
it. The code you see here has a loop. I want to put a range of cells
("A1:A15") to the closed workbook, but all my methods fail!

Hope you have answers

Thanks in advance
Max

 

[Tom Ogilvy]

Well, in actuallity, the workbook isn't really closed when you retrieve your
data. It is opened and read, just not in excel. Files are constructed in a
specific format which may require complete knowledge of the current state of
the information to be stored. Writing to a file may require that the whole
file be reorganized - or at least to some extent. For example, Excel has
65536 rows, but it doesn't store any information on most of the rows because
it knows that they were not in use. When you write to the file, it would
require the ability to make more rows in use and store information about
them. The knowledge to do this has to reside somewhere. For the database
approach, the ability is limited and the knowledge is supplied in the
drivers associated with the database. The ability to do this in general
just isn't provided.

so why wouldn't it
be possible to PUT data in a closed workbook?

because provision has been made only for very limited capabilities in this
instance.

--
Regards,
Tom Ogilvy

Thanks for your replies.

Though I really think this is possible. It's possible to read data from a
closed workbook file and copy this into another workbook, so why wouldn't it
be possible to PUT data in a closed workbook?

Anyway, for others who have (possible) solutions, please post me, or send to
my email: (e-mail address removed)

thanks
max

I agree with Frank that opening the workbook makes the most sense.
However, if your data is organized as database, you can treat it as a
database and update it use SQL and ADO or DAO. I personally haven't done
this, but if you want to explore:

http://www.erlandsendata.no/english/index.php?t=envbadac
Mr. Erlandsen's site.

now

I

would also like to PUT data in the closed workbook. I am aware of what
ExecuteExcel4Macro's are, but this wont help me I think. Here is my code:

Sub PutDataInClosedFile()
'
filepath = "C:\Documents and Settings\Max\Desktop\temp"
Filename = "batch.xls"
sheetname = "Sheet1"
'
'''''''''''''''''''''''''
For n = 2 To 16
Strg = "'" & filepath & "\[" & Filename & "]" _
& sheetname & "'!" & "r" & n & "c1"
'
''''''code for putting data in closed workbook has to come here''''''''
<========================
'
Next n
'
End Sub

PROBLEM: How to PUT info from a certain workbook (closed or open) to a
(closed or open) other workbook.

I worked all my books through, but can't find the good syntax, or

method
to

it. The code you see here has a loop. I want to put a range of cells
("A1:A15") to the closed workbook, but all my methods fail!

Hope you have answers

Thanks in advance
Max