Access - Excel Automation

  • Thread starter Thread starter J. Toews
  • Start date Start date
J

J. Toews

Good Day..

I have posted this question on both the Excel and Access
groups because I'm not sure who can help me on this, I am
new to this stuff.

I have an Access DB with a module that contains code to
populate a table from a number of Excel spreadsheets.
The TransferSpreadsheet method can't be used because the
data I need can be in different places on each
spreadsheet, ie. no standard format for all the
spreadsheets. So I have to literally search each sheet
using automation techniques with Excel vba for certain
indicators that tell me where I should grab the data from.

I know, sounds stupid but it has to be done this way as
has been told to me from the "Powers That Be".

Anyway, I declare all my automation objects
(Excel.Application, Excel.Workbook etc..) at the
beggining of the routine and at the end I clean all the
objects up (Object.quit, Object.Close, Set Object =
Nothing, etc..).
The routine works fine with one problem. After the
routine has run and I try to open up an Excel spreadsheet
through Windows Explorer (without having the Excel
Application open before hand) the Excel Application and
the selected file do open but all I can see is the Excel
frame (Toolbars, Status Bar) but I can't see the actual
spreadsheet. What I actually see is Windows explorer
within the Excel application frame (the last program used
before I double clicked on the file). By looking at the
cell address indicator on the menu I can tell that I am
in the spreadsheet and can move around and enter stuff
but I just can't see anything.

If I open Excel from the start menu and then open a
spreadsheet file from Explorer, everything is OK, no
problems.

So my question basically is, what is happening here? Is
there a way I can select something or press a hotkey that
will make the spreadsheet visible? Is there a certain
piece of code I need in my Access module that would
prevent this problem from happening?

I have no problem working around this but it confuses the
hell out of the users so if anybody has a solution to
this your help would be greatly appreciated!!

I hope this is enough to go on. If there is any piece of
information that you think I might have left out please
let me know and I'll repost..

Thanks in advance..

JT.
 
JT,
Not sure what your problem may be, but I found that indiscriminately opening
and closing Excel can cause strange problems.

First: here is a routing that I call when opening Excel, it insures that you
only open one instance of Excel.

Public Property Get Application() As Excel.Application

Dim exl As Excel.Application

On Error GoTo ErrorHandler

Set exl = GetObject(, "Excel.Application")

ExitHandler:
Set Application = exl
Exit Property
ErrorHandler:
If exl Is Nothing Then
Set exl = New Excel.Application
End If
Resume ExitHandler
End Property

Second, I would suggest, if you have the option would be to write your code
in an Excel Macro. Excel Automation is horribly slow from outside of Excel.
I also found that using the GetRows method of a queryDef or recordSet and
then transposing the resultant array and then setting an excel range to the
value of the transposed array to be really quick since it is done completely
in memory. The caveat is that you have enough memory or a relatively small
number of records. I have used this procedure with 2000 records by 10
fields with no problems.

Dan
 
Back
Top