Refer to "ActiveWorkbook" from Access 1

[kjv1611]

I thought this was simple, and maybe I'm just forgetting something small.

I'm wanting to be able to open an Excel workbook from email, and then have Access reference the then opened workbook. How can I refer to that workbook?

So far, I tried:

Sub TestWb
  Dim xlApp As New Excel.Application
  Dim wb As Excel.Workbook
  Dim ws As Excel.Worksheet

  Set wb = xlApp.ActiveWorkbook
  Set ws = wb.ActiveSheet

  '....
End Sub

And that doesn't work.

Then I tried:

   '...
   
   Set wb = Excel.ActiveWorkbook
   '...

Well, that didn't work either.

Any thoughts? Am I just totally overlooking the obvious somewhere? I can't refer to a specific workbook in this scenario, b/c it will just be the open workbook from an email... not saved to a particular location. If I have to go that route, then the whole process will be too far overkill for this one.

Thanks for any references/suggestions/examples,



"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[kjv1611]

Oh... and just to clarify in case anyone is wondering... it's giving me errors when I try this method, b/c the xlApp just never gets set this way (method 1), and the workbook never gets set in method2...

Each time, when I go into break mode, and hover over the variables, it just shows "Nothing" in the pop-up after I have set the variables to what I think I'm wanting to set it to...

So the errors I later get are "Object variable or With block variable not set."

I even tried later to refer to .ThisWorkbook just to try.. .that also was a no go... different error, basically just telling me the function for ThisWorkbook failed.


"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[PHV]

Provided there is a single instance of Excel running:

Sub TestWb
  Dim xlApp As Excel.Application
  Dim wb As Excel.Workbook
  Dim ws As Excel.Worksheet
  Set xlApp = GetObject(, "Excel.Application")
  Set wb = xlApp.ActiveWorkbook
  Set ws = wb.ActiveSheet
  '....
End Sub

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[kjv1611]

Sorry, didn't get back to this until today. Thanks for posting, I'll try to take a look at this by the end of today or else tomorrow at latest... and I'll post feedback.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[kjv1611]

PHV, Thanks a ton! That works perfectly!

Any clues on how to check for multiple instances of Excel? I guess that's getting more into Windows APIs than strict VBA..



"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57