By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
444,027 Members | 1,261 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 444,027 IT Pros & Developers. It's quick & easy.

excel automation thru access- array/implicit ref problem

P: n/a
I'm working on a program that imports a bunch of data from a folder
full of Excel files and compiles it into an Access DB. The Excel files
have a varying number of rows and columns that need to be imported,
and the data needs to be processed/error checked before it's written
to the DB. The way I've tackled this is to have a procedure that steps
through a list of Excel files, invokes an instance of Excel, opens
file "x", copies the relevant data to an array, then closes the xls
file and the Excel application. The data gets processed in the array,
then once it's in a format amenable to Access, it gets written to the
DB. Move on to the next .xls file; rinse and repeat. Simple.

The trouble I'm encountering appears to be the dreaded "implicit
reference". The Excel application fails to close if I use the
following:

'SAVarray() is a variant array
'XLrows and XLcols are the dimensions of the dataset, they've been
previously mapped

SAVarray()= XLsheet.Range(Cells(4, 1), Cells(XLrows + 3, XLcols))
'relevant data starts in row 4, column 1

<do something with the array here...>

redim SAVarray(1) ' since I'm done with the data, I want to erase the
array here

set XLsheet = nothing

This obviously omits a lot of pre- and post- coding, but this is the
section that causes the trouble, so the rest is fairly unimportant.

If I replace the above with the following, Excel behaves nicely:

ReDim SAVarray(1 To XLrows, 1 To XLcols) 'base 1 array
i = 1

Do Until i = XLrows + 1
j = 1
Do Until j = XLcols + 1
SAVarray(i, j) = XLsheet.Cells(i + 3, j).Value
j = j + 1
Loop
i = i + 1
Loop

<do something with the array here...>

redim SAVarray(1) ' since I'm done with the data, I want to erase the
array here

set XLsheet = nothing

The trouble is, this is sloooooooooow. Putting the whole range into
the array in one step is much, much faster. My guess is that when you
use the range to fill the array, it makes Access think that SAVarray()
is an Excel widget(??). Any thoughts on syntax that might make this
work? Thanks in advance; I hope I'm not missing something really
obvious.

Greg

Aug 13 '07 #1
Share this Question
Share on Google+
2 Replies


P: n/a
ge******@yahoo.com wrote:
I'm working on a program that imports a bunch of data from a folder
full of Excel files and compiles it into an Access DB. The Excel files
have a varying number of rows and columns that need to be imported,
and the data needs to be processed/error checked before it's written
to the DB. The way I've tackled this is to have a procedure that steps
through a list of Excel files, invokes an instance of Excel, opens
file "x", copies the relevant data to an array, then closes the xls
file and the Excel application. The data gets processed in the array,
then once it's in a format amenable to Access, it gets written to the
DB. Move on to the next .xls file; rinse and repeat. Simple.

The trouble I'm encountering appears to be the dreaded "implicit
reference". The Excel application fails to close if I use the
following:

'SAVarray() is a variant array
'XLrows and XLcols are the dimensions of the dataset, they've been
previously mapped

SAVarray()= XLsheet.Range(Cells(4, 1), Cells(XLrows + 3, XLcols))
'relevant data starts in row 4, column 1

<do something with the array here...>

redim SAVarray(1) ' since I'm done with the data, I want to erase the
array here

set XLsheet = nothing

This obviously omits a lot of pre- and post- coding, but this is the
section that causes the trouble, so the rest is fairly unimportant.

If I replace the above with the following, Excel behaves nicely:

ReDim SAVarray(1 To XLrows, 1 To XLcols) 'base 1 array
i = 1

Do Until i = XLrows + 1
j = 1
Do Until j = XLcols + 1
SAVarray(i, j) = XLsheet.Cells(i + 3, j).Value
j = j + 1
Loop
i = i + 1
Loop

<do something with the array here...>

redim SAVarray(1) ' since I'm done with the data, I want to erase the
array here

set XLsheet = nothing

The trouble is, this is sloooooooooow. Putting the whole range into
the array in one step is much, much faster. My guess is that when you
use the range to fill the array, it makes Access think that SAVarray()
is an Excel widget(??). Any thoughts on syntax that might make this
work? Thanks in advance; I hope I'm not missing something really
obvious.

Greg
Did you try

SAVarray()= _
XLsheet.Range(XLsheet.Cells(4, 1), _
XLsheet.Cells(XLrows + 3, XLcols))

in stead of

SAVarray()= XLsheet.Range(Cells(4, 1), Cells(XLrows + 3, XLcols))

Your syntax (without "anchoring" the Cells object to it's parent
object, might cause what you're experiencing. Check out
http://support.microsoft.com/default.aspx?kbid=178510
for more info

--
Roy-Vidar
Aug 13 '07 #2

P: n/a
Thanks a bunch... that looks like it.

Greg

On Aug 13, 5:03 pm, RoyVidar <roy_vidarNOS...@yahoo.nowrote:
gelli...@yahoo.com wrote:
I'm working on a program that imports a bunch of data from a folder
full of Excel files and compiles it into an Access DB. The Excel files
have a varying number of rows and columns that need to be imported,
and the data needs to be processed/error checked before it's written
to the DB. The way I've tackled this is to have a procedure that steps
through a list of Excel files, invokes an instance of Excel, opens
file "x", copies the relevant data to an array, then closes the xls
file and the Excel application. The data gets processed in the array,
then once it's in a format amenable to Access, it gets written to the
DB. Move on to the next .xls file; rinse and repeat. Simple.
The trouble I'm encountering appears to be the dreaded "implicit
reference". The Excel application fails to close if I use the
following:
'SAVarray() is a variant array
'XLrows and XLcols are the dimensions of the dataset, they've been
previously mapped
SAVarray()= XLsheet.Range(Cells(4, 1), Cells(XLrows + 3, XLcols))
'relevant data starts in row 4, column 1
<do something with the array here...>
redim SAVarray(1) ' since I'm done with the data, I want to erase the
array here
set XLsheet = nothing
This obviously omits a lot of pre- and post- coding, but this is the
section that causes the trouble, so the rest is fairly unimportant.
If I replace the above with the following, Excel behaves nicely:
ReDim SAVarray(1 To XLrows, 1 To XLcols) 'base 1 array
i = 1
Do Until i = XLrows + 1
j = 1
Do Until j = XLcols + 1
SAVarray(i, j) = XLsheet.Cells(i + 3, j).Value
j = j + 1
Loop
i = i + 1
Loop
<do something with the array here...>
redim SAVarray(1) ' since I'm done with the data, I want to erase the
array here
set XLsheet = nothing
The trouble is, this is sloooooooooow. Putting the whole range into
the array in one step is much, much faster. My guess is that when you
use the range to fill the array, it makes Access think that SAVarray()
is an Excel widget(??). Any thoughts on syntax that might make this
work? Thanks in advance; I hope I'm not missing something really
obvious.
Greg

Did you try

SAVarray()= _
XLsheet.Range(XLsheet.Cells(4, 1), _
XLsheet.Cells(XLrows + 3, XLcols))

in stead of

SAVarray()= XLsheet.Range(Cells(4, 1), Cells(XLrows + 3, XLcols))

Your syntax (without "anchoring" the Cells object to it's parent
object, might cause what you're experiencing. Check outhttp://support.microsoft.com/default.aspx?kbid=178510
for more info

--
Roy-Vidar

Aug 13 '07 #3

This discussion thread is closed

Replies have been disabled for this discussion.