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.

Hi JT
Can you post some of your Automation code. You may still have a
instance of Excel in the ROT that hasn't been cleared

--
Ivan F Moal

-----------------------------------------------------------------------
Ivan F Moala's Profile: http://www.excelforum.com/member.php...nfo&userid=195
View this thread: http://www.excelforum.com/showthread.php?threadid=26770

Here you go, some of the names have been changed to
protect the innocent.

Sub RollupExcelData()
Dim oXLApp As Excel.Application
Dim oXLWBk As Excel.Workbook, oXLWBk1 As Excel.Workbook
Dim oXLWsht As Excel.Worksheet, oXLWsht1 As
Excel.Worksheet

Dim liRow As Integer
Dim liIncr As Integer
Dim lasFileName(1 to 20) As String

On Error GoTo HandleErrors

Set oXLApp = New Excel.Application
Set oXLWBk = oXLApp.Workbooks.Add
Set oXLWsht = oXLWBk.Worksheets.Add

oXLWsht.Name = "DATA"
miExtractRowNum = 2

lsFileName = "U:\shared\Sample.xls"
Set oXLWBk1 = oXLApp.Workbooks.Open(lasFileName(1))
Set oXLWsht1 = oXLWBk1.Sheets("Sheet1")

liRow = 9
With oXLWsht1
Do Until UCase(Trim(Range("A" & liRow).Value)) = "END"
If Range("A" & liRow).Value = "IDCODE" Then

'Process data here

End If
liRow = liRow + 1
Loop
End With

oXLWBk1.Close 'the file that was just processed
oXLWBk.SaveAs ("filename") 'the new file
oXLWBk.Close
oXLApp.Quit

'clean up objects
Set oXLWsht = Nothing
Set oXLWBk = Nothing
Set oXLWsht1 = Nothing
Set oXLWBk1 = Nothing
Set oXLApp = Nothing
End Sub

I hope this is everything you need. Basically I create a
new sheet to store the data from the extraction so I need
two instances of the workbook and worksheet object. One
for the new workbook and one for the workbook where the
data is contained. I have taken some code (iteration
code) to simplify but the one thing I should add is the
oXLWsht1 is used to open multiple worksheets by using a
loop with an array that contains multiple file names. So
the general process is:

sFileName(1 to 20) as String
do
Set oXLWBk1 = oXLApp.Workbooks.Open(sFileName(X))
'Process data etc...
'Close current workbook
'increment variable X

loop

Hope this helps, thanks for your response..

-----Original Message-----

Hi JT
Can you post some of your Automation code. You may still
have an
instance of Excel in the ROT that hasn't been cleared.


--
Ivan F Moala


---------------------------------------------------------
---------------
Ivan F Moala's Profile:
http://www.excelforum.com/member.php?
action=getinfo&userid=1954
View this thread:
http://www.excelforum.com/showthread...hreadid=267702

.

Try changing this bit;

Do Until UCase(Trim(Range("A" & liRow).Value)) = "END"
If Range("A" & liRow).Value = "IDCODE" Then


To


Do Until UCase(Trim(.Range("A" & liRow).Value)) = "END"
If .Range("A" & liRow).Value = "IDCODE" Then

The diff is in how you have referenced the Xl object using the With Ke
word.
In COM automation not qualifying your addresses can lead to ghost ref
to another instance of the obj.

Also try dim all references to a Row as Long and NOT integer..... jus
incase

--
Ivan F Moal

-----------------------------------------------------------------------
Ivan F Moala's Profile: http://www.excelforum.com/member.php...nfo&userid=195
View this thread: http://www.excelforum.com/showthread.php?threadid=26770

Thanks for the help Ivan..

Regards

JT

"Ivan F Moala" wrote:


Try changing this bit;

Do Until UCase(Trim(Range("A" & liRow).Value)) = "END"
If Range("A" & liRow).Value = "IDCODE" Then


To


Do Until UCase(Trim(.Range("A" & liRow).Value)) = "END"
If .Range("A" & liRow).Value = "IDCODE" Then

The diff is in how you have referenced the Xl object using the With Key
word.
In COM automation not qualifying your addresses can lead to ghost refs
to another instance of the obj.

Also try dim all references to a Row as Long and NOT integer..... just
incase.


--
Ivan F Moala


------------------------------------------------------------------------
Ivan F Moala's Profile: http://www.excelforum.com/member.php...fo&userid=1954
View this thread: http://www.excelforum.com/showthread...hreadid=267702