Toggling Between Access and Excel in VBA

[Phil Davis]

Hi, I struggling to locate how to toggle between Access and Excel within VBA code running on the Access side.

I am reading a flat (text) file to update both Access tables and Excel workbook within the same process.

No problem updating the Access table but for Excel purposes, I have created an Access VBA User Defined Function with 3 call types: OPEN, UPDATE and CLOSE that is intended to open a workbook on the first call, update the workbook on subsequent calls (having retrieved a record fron the flat file) and close the workbook on the final call

I have succeeded with the OPEN call, but not the Update because I suspect I have to tell VBA I'm now doing Excel type processing when I make an UPDATE call. I surely don't have to constantly open and close the workbook to make updates?

I can see how I can do this using a recordset (having first processed the flat file in its entirety) but would like to understand the basic principles of switching between the two APPS in VBA?

I am running Office 2003 on Windows 7

Many thanks in advance
Phil Davis

[ADezii]

How about posting the Code for the User Defined Function that Updates the Excel Workbook?

[NeoPa]

Clearly great effort was made to post a clear and understandable question. However, as ADezii says, it really could do with some relevant bits of the code posted too.

Working for now with what we have, I'm guessing that you are having some sort of issue related to the Access and Excel Application objects. The Access Application object is always available simply as Application. The Excel one is returned when you open Excel from the Access VBA code (See Application Automation for some background).

The Excel Application object (as well as some other Excel objects like Workbook for instance.) should be maintained in the code until they are finished with. Any reference to items within that object will automatically be seen as Excel related code. It's important that this object (these objects) are passed to any procedures that need to work on the Excel side of things.

[Phil Davis]

Hi NeoPa and ADezii, many thanks for your responses. I've had a fiddle following your advice in Application Automation but I think I just don't know how to reference objects that were established in the first call to my UDF from subsequent calls. These are the call to my UDF:

a = linkXL("Open", "c:\Phil\My File.xls")
a = linkXL("Update", "My File.xls", "Sheet2", "Car Tax", "£30.00")
a = linkXL("Close", "My File.xls")
 

This is my UDF as it currently stands after many attempts to get something to work!

Function linkXL(call_type As String, path As String, Optional sheet_name As String, Optional category As String, Optional item_value As Currency)
 
Dim oXLAPP As Excel.Application
Dim oXLBOOK As Excel.Workbook
Dim oXLSHEET As Excel.Worksheet
 
Select Case call_type
  Case "open"
    Set oXLAPP = New Excel.Application                      'Create a new instance of Excel
    ' Set oXLBOOK = oXLAPP.Workbooks.Add                    'Add a new workbook
    Set oXLBOOK = oXLAPP.Workbooks.Open(path)               'Open an existing workbook
    oXLAPP.Visible = True
    oXLAPP.DisplayAlerts = True
  Case "Update"
    Set oXLAPP = GetObject(, "Excel.Application")
    With oXLAPP
      .Workbooks(path).Activate
      .Sheets(sheet_name).Activate
      Cells(1, 1).value = category
      Cells(1, 2).value = item_value
    End With
  Case "Close"
    Set oXLSHEET = Nothing                                  'disconnect from the Worksheet
    oXLBOOK.SaveAs path                                     'Save (and disconnect from) the Workbook
    oXLBOOK.Close SaveChanges:=False
    Set oXLBOOK = Nothing
    oXLAPP.Quit                                             'Close (and disconnect from) Excel
    Set oXLAPP = Nothing
End Select
 
End Function
 
 

It now blows on the .Workbooks(path).Activate within the Update call with Error Num 9: Subscript out of range.

I'd be very grateful if you could spell out what I need to do with a coded example.

best Regards, Phil

[NeoPa]

I'll hold off with the coded example for now, as you need the concept understood more urgently (IMHO). The whole structure of your code is off. The Open and Close are integral parts of any update process. On the other hand, multiple updates can be encompassed within a single pair. If an Open is done in the called function which leaves the object open at the end, then the calling code needs that information, which is the object itself. Thus, the function should return the object. Therefore the calling code needs that object defined (Dim etc). This object, in turn, would be passed as a parameter to all the calls where that object is used, including where it's closed. Handling all of that in a single function is not an easy thing to implement, and when done successfully is still not a natural fit.

What you need to do is decide what it is you're really trying to achieve before working out how to achieve it in code.

[Phil Davis]

Hi NeoPa, I've done away with the idea of a UDF to handle the Excel updates (moving the code into the calling routine instead) and life is now so much easier as you suggest!

I tend to modularise my code (habits from a past life!) and I'd still like to be able to do something to tidy up the readability of the mainline code.

When you say "Handling all of that in a single function is not an easy thing to implement, and when done successfully is still not a natural fit", just to be absolutely clear you're referring to the difficulty of maintaining other (ie non Access) objects between calls to a UDF?

I'm still at the start of the Object Oriented learning curve and wish I could pinpoint some online documentation, specific to MS Office, that explains this stuff in simple terms. I've given up trying to wrap my head around the MS online stuff and I won't pay for basic manuals that I believe they (MS) should provide free of charge!

I and I guess many others only get by because people like yourself (and other experts) have the patience to help people like me. Many thanks indeed for that.

Best Rgds, Phil

[ADezii]

I cannot possibly expand on NeoPa's excellent explanation, but what I can do is to demonstrate to you how you can easily OPEN, UPDATE, and CLOSE an Excel Workbook within a single Function Call using only the PATH as an Argument and your Base Code:

Public Function fAutomationExample(strPath As String)
Dim oXLAPP As Excel.Application
Dim oXLBOOK As Excel.Workbook
Dim oXLSHEET As Excel.Worksheet
Const conSHEET_NAME As String = "Sheet1"        'Can also Pass as an Argument to Function
 
Set oXLAPP = New Excel.Application
Set oXLBOOK = oXLAPP.Workbooks.Open(strPath)    'OPEN
 
oXLBOOK.Activate
 
With oXLAPP
  .Visible = True
  .DisplayAlerts = True
  .Sheets(conSHEET_NAME).Activate
 
  .Cells(1, 1).Value = "Category"               'UPDATE
  .Cells(1, 2).Value = "item_value"
End With
 
oXLBOOK.Save                                    'SAVE
oXLBOOK.Close
Set oXLBOOK = Nothing
 
Set oXLSHEET = Nothing
 
oXLAPP.Quit
Set oXLAPP = Nothing
End Function
 

Sample Function Call:

'Can use this Syntax since the Function does not
'return a Value
Call fAutomationExample("C:\Test\Test.xls")

[NeoPa]

Phil, I do enjoy dealing with you. You express things so clearly and ask exactly what you need to know.

Phil:
When you say "Handling all of that in a single function is not an easy thing to implement, and when done successfully is still not a natural fit", just to be absolutely clear you're referring to the difficulty of maintaining other (ie non Access) objects between calls to a UDF?

Not exactly no. I was referring to the fact that, with a little imagination and a fair bit of experience, it is possible to modularise the procedure (UDF in your terminology) in the way you like. It would be an awkward approach though. Modularising is definitely to be recommended, but the fit as to where the lines are drawn should always be a natural one. I hope that clarifies my point.

NB. In case you weren't aware, it is possible to pass multiple parameters to a procedure in such a way that the calling code sees all changes made to those specific parameters after the procedure has finished. This is done by using the term ByRef when declaring the parameters in the procedure declaration. This is over and above the returned value defined for a Function procedure.

Phil:
I'm still at the start of the Object Oriented learning curve and wish I could pinpoint some online documentation, specific to MS Office, that explains this stuff in simple terms

I consider Access and VBA to be a sort of pseudo OO. Not quite the real deal that you get with proper languages such as the .NET family. That said, I'm not sure exactly what you need to help at this point in your development, so I would just mention that I always choose the installation option to load the Help files for all packages and especially VBA when installing Office. I understand that this deteriorated in quality from version 2007, but I've always found the bundled Help much better and easier to work with than the web based ones.

I don't know how much, if any, of the following links will help, but here they are anyway in case :

1. Microsoft Access Tutorials (Strive4Peace).
2. Microsoft Access Tutorials
3. Microsoft Office Tutorials.
4. VBA Tutorial (Excel).

[Phil Davis]

Many thanks once again to you guys! I think the penny is slowly dropping!!

This morning I have written an Excel VBA to clear the contents of the test Workbooks that will be the target of my Access process. I wrote it to run from my collection in PERSONAL.xls and arranged for it to fire up a new instance of Excel under which the workbooks/sheets were activated and cleared. As an exercise it was very interesting because I now understand the demands of referencing the (Excel) objects explicitly when running in this environment. I can tell you I cleared the Active Sheet of PERSONAL.xls many times before I realised what was happening!!!

I was a career mainframe systems programmer coding assembler and an interpretive language called REXX (not unlike VBA) but OO (pseudo or not) is a whole new ball game for me! It is certainly keeping the grey matter ticking over. I will enjoy ploughing through the tuturials esp. the Excel VBA.

Best Regards
Phil

[NeoPa]

Phil:
I was a career mainframe systems programmer coding assembler

I was a bit similar myself way back when. I expect during that time you handled processor interrupts? If so, you could think along similar lines for doing OO (I found at least). There are rules in place for when code can and cannot be interrupted, but essentially you have the standard flow of instructions as controlled by your executing code, as well as the possibility of some procedures being called from outside of your control. As long as your thinking matches the paradigm it's in, you should find you can get to grips with it. There are other issues to deal with too, of course, but they're generally about extra syntax and capabilities to master.

Clearly, the benefits of OO are best realised by designing more of the objects that require external interrupts, whereas interrupt handling tended to require as little as possible be done outside the normally defined flow.

PS. I should have guessed you were old-school. It wouldn't even occur to you to write questions in a way that would be difficult to read and understand, whereas most members need telling a number of times that jotting something down in a way that's like normal conversation is unlikely to be easily understood the other side of a forum page. Refreshing indeed :-)

[Phil Davis]

Hi NeoPa, yes I've written many a program/timer/etc etc interrupt exit in my time! You do learn the need for precision and clarity in those disciplines esp in the documentation. It's what I find most frustrating in the MS world. Thank the G for you guys on BYTES.COM and Si-The Geek (in another place).

Cheers, Phil

[Phil Davis]

Neopa

"with a little imagination and a fair bit of experience"

Are you talking class modules? I just acquired an old VBA developer's guide with coded examples (Getz and Gilbert)!! Looks really interesting.

Cheers, Phil

[NeoPa]

I wasn't Phil, but I'm sure that would also be an appropriate solution.

I was talking about maintaining the necessary objects within the procedure as static (or even Module-level) variables and designing the interface and parameters such that they were flexible enough to handle the various requirements. That said, I don't believe I would be helping you much to lead you down that path. I only discuss it on the understanding that it is not my recommendation.

[Phil Davis]

Hi Neopa, so I have two new learning curves! Excellent. I've only just learnt how enjoyable programming is without the pressure of deadlines. Hopefully I will soon(ish) be contributing to this forum rather than just taking! Once again many thanks for your help.

Rgds, Phil

[NeoPa]

Phil:
Hopefully I will soon(ish) be contributing to this forum

Always welcome Phil, but never pressure. Actually, well presented questions are a contribution in their own right, if truth be told, but certainly contributing answers is more so ;-)