excel automation thru access- array/implicit ref problem

[gellis72]

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

[RoyVidar]

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

[gellis72]

Thanks a bunch... that looks like it.

Greg

On Aug 13, 5:03 pm, RoyVidar <[e-mail]:

[e-mail] 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