473,883 Members | 1,690 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

How to handle a missing VBA reference (To Excel)

365 Contributor
Hi guys,

I am in the process of migrating some access projects to 2010 (from 2003) and have hit another problem,

The background: we have upgraded some PC to office 2010, but not all, and some have had office removed (and we are using the viewers and access runtime).

Problem: in one project i have a reference to Excel (11.0) so that i can export some things straight to excel, now if excel isn't installed, it cant comit that reference, is there anything i can do to overcome this? (removing the reference invalidates the code objects, and it gets "all shouty")

i was thinking of somehow "blocking off" the excel code/modules if the reference couldn't be found, so that i didnt have to run two versions of the Front End.

Any ideas?

Cheers
May 9 '12 #1
10 12290
NeoPa
32,584 Recognized Expert Moderator MVP
You could try identifying the files found on the PCs where the software is installed (Tools | References lists where the actual files are found with the file names.) and copying just these files to any PC that doesn't have the software installed.

I'm afraid I can't tell you if this is in accordance with the license as I really don't know. I don't imagine it conflicts, but I can't be certain.
May 9 '12 #2
Dan2kx
365 Contributor
I thought about that, but the file referenced is excel.exe itself, so I imagined, it wouldn't work, but I could try
May 9 '12 #3
NeoPa
32,584 Recognized Expert Moderator MVP
In that case I'm pretty sure it would be an infringement of the license and I couldn't possibly recommend that.
May 9 '12 #4
Dan2kx
365 Contributor
Yeah, I thought I was clutching at straws a bit! Looks like its the hard way again!!
Unless its possible to deactivate code on the fly? Which sounds just as ludicrous!!
May 10 '12 #5
Stewart Ross
2,545 Recognized Expert Moderator Specialist
There is another approach to working in a dual Excel 2003/2010 environment, but it can be a pain to implement. You could change your code to be late-bound so that a specific reference to Excel is not required. This allows the use of Excel 11 (2003) and 12 or greater (2007, 2010) without setting references which conflict. Moreover, you can use the SAME code in both environments as long as you understand (and cater for) the differences in some cases introduced by Excel 2007 and above.

To implement this you would have to replace all Excel-specific object definitions with generic ones (referring for example to an Object type instead of, say an Excel.Worksheet ); you would have to define in your module header any Excel constants you use; and you'd have to replace the early-binding instantiation of the Excel instance with a late-bound version.

Example of explicit definition of Excel VBA constants
Expand|Select|Wrap|Line Numbers
  1. Const xlLastCell = 11
  2. Const xlLeft = -4131
  3. Const xlMaximized = -4137
  4. Const xlMinimized = -4140
  5. Const xlNext = 1
  6. Const xlNo = 2
  7. Const xlNone = -4142
  8. Const xlPrevious = 2
  9. Const xlSolid = 1
  10. Const xlSum = -4157
  11. Const xlThin = 2
  12. Const xlTop = -4160
  13. Const xlWBATWorksheet = -4167
  14. Const xlExcel8 = 56
  15. Const xlOpenXMLWorkbookMacroEnabled = 52
  16. Const xlOpenXMLWorkbook = 51
Example of Use of Generic Object Definitions
Expand|Select|Wrap|Line Numbers
  1. Private Function fFindNextBlankCellDown(ws As Object, ByVal StartRow As Long, ByVal currentcol As Long) As Long
  2.     Dim wsRange As Object
  3.     Dim varContents As Variant
  4.     Set wsRange = ws.Cells(StartRow, currentcol)
  5.     varContents = wsRange.Value
  6.     Do Until IsEmpty(varContents)
  7.         StartRow = StartRow + 1
  8.         Set wsRange = ws.Cells(StartRow, currentcol)
  9.         varContents = wsRange
  10.     Loop
  11.     fFindNextBlankCellDown = wsRange.Row
  12.     Set wsRange = Nothing
  13. End Function
Example of late-binding instantiation of Excel object
Expand|Select|Wrap|Line Numbers
  1. Public Sub InstantiateExcel()
  2.     Set objExcel = CreateObject("Excel.Application")
  3.     strFileExtension = ".xls"
  4.     If objExcel.Application.Version >= 12 Then
  5.         objExcel.Application.DefaultSaveFormat = xlOpenXMLWorkbook
  6.         strFileExtension = ".xlsx"
  7.     End If
  8. End Sub
(Note that this example is from a class module, where the Excel object variable objExcel is a private object global to the class module, as is the string strFileExtensio n - hence why these are not passed as parameters to the InstantiateExce l sub.)

A disadvantage of using late binding during development is that without a reference to the Excel module there is no intellisense for the Excel object variables within the VBA editor environment. Get an object method application wrong and you'll need to look it up in a different environment (I use Excel itself to do so when necessary).

As you will also see from the example instantation sub above, the default format for Excel files is not the same for Excel 11 and 12 files - if you simply force the file extension to .XLS in Excel 2007 or 2010 the actual file format used is NOT forced to the Excel 2003 format. Before I discovered this problem I had error messages in both environments - in 2003, where the .XLS file was not recognised (as it was actually in a 2007 format), and in 2007 (where the .xls extension raised a non-fatal warning that the file format was not correct).

If you need to cater for the situation you outline where you do not have Excel installed at all you could check for success in instantiating Excel, perhaps like this, where the function below returns True if Excel can be instantiated and False otherwise:

Expand|Select|Wrap|Line Numbers
  1. Public Function ExcelInstalled() As Boolean
  2.     Dim objExcel As Object
  3.     On Error GoTo NotInstalled
  4.     Set objExcel = CreateObject("Excel.Application")
  5.     objExcel.Quit
  6.     ExcelInstalled = True
  7.     Exit Function
  8. NotInstalled:
  9.     ExcelInstalled = False
  10. End Function
The late-bound code itself will not cause errors in such an environment, but you would need to ensure that you do not call any of the Excel-related functions and subs if Excel is not installed.

-Stewart
May 10 '12 #6
Dan2kx
365 Contributor
Thanks for the advice Stewart, i'm not entirely sure what you mean by early/late bound, unless you just mean referenced, or not (and then referred to in the function?

Here is an example of my code:

Expand|Select|Wrap|Line Numbers
  1. Function ExcelATE(qryName As String, var As Byte)
  2. On Error GoTo Trap
  3. Dim fn As String, xlApp As Excel.Application, xlBook As Excel.Workbook, x As Long, y As Long, z As Long
  4.     If MsgBox("Would you like to view data in EXCEL?", vbQuestion + vbYesNo, "How would you like to view data...") _
  5.             = vbNo Then DoCmd.OpenQuery qryName, , acReadOnly: Exit Function
  6.     fn = FolderFromPath(CurrentDb.Name) & qryName & ".xls"
  7.     DoCmd.SetWarnings False
  8.     Kill (fn)
  9.     DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, qryName, fn, True
  10.         Set xlApp = CreateObject("Excel.Application")
  11.         Set xlBook = xlApp.Workbooks.Open(fn)
  12.             With xlApp
  13.                 .Cells.Select
  14.                 .Cells.EntireColumn.AutoFit
  15.                 .Range("A1").Select
  16.             End With
  17.             Select Case var
  18.                 Case 1
  19.                     With xlApp
  20.                         .Range("A1:M1").Select
  21.                         .Selection.Interior.ColorIndex = 48
  22.                         x = 2
  23.                         Do Until .Range("A" & x).Value = Empty
  24.                             .Range("A" & x & ":M" & x).Select
  25.                             If .Range("E" & x).Value < .Range("D" & x).Value Then
  26.                                 With .Selection.Interior
  27.                                     .ColorIndex = 6
  28.                                     .Pattern = xlSolid
  29.                                 End With
  30.                             End If
  31.                             If .Range("G" & x).Value < Date + 28 Then .Selection.Font.ColorIndex = 3
  32.                             x = x + 1
  33.                         Loop
  34.                         .Range("A1").Select
  35.                     End With
  36.                 Case 2
  37.                     With xlApp
  38.                         .Range("A1:P1").Select
  39.                         .Selection.Interior.ColorIndex = 48
  40.                         x = 2
  41.                             Do Until .Range("A" & x).Value = Empty
  42.                                 y = x: z = x
  43.                                 If .Range("E" & x).Value < Date + 28 Then .Range("A" & x & ":P" & x).Select: .Selection.Font.ColorIndex = 3
  44.                                 If .Range("A" & x).Value = .Range("A" & x + 1).Value Then
  45.                                     Do Until .Range("A" & x).Value <> .Range("A" & x + 1).Value
  46.                                         If .Range("E" & x).Value < Date + 28 Then .Range("A" & x & ":P" & x).Select: .Selection.Font.ColorIndex = 3
  47.                                         x = x + 1
  48.                                         z = x
  49.                                     Loop
  50.                                     x = x + 1
  51.                                 Else
  52.                                     x = x + 1
  53.                                 End If
  54.                                 .Range("A" & y & ":P" & z).Select
  55.                                 .Selection.Borders(xlDiagonalDown).LineStyle = xlNone
  56.                                 .Selection.Borders(xlDiagonalUp).LineStyle = xlNone
  57.                                 With .Selection.Borders(xlEdgeLeft)
  58.                                     .LineStyle = xlContinuous
  59.                                     .Weight = xlMedium
  60.                                     .ColorIndex = xlAutomatic
  61.                                 End With
  62.                                 With .Selection.Borders(xlEdgeTop)
  63.                                     .LineStyle = xlContinuous
  64.                                     .Weight = xlMedium
  65.                                     .ColorIndex = xlAutomatic
  66.                                 End With
  67.                                 With .Selection.Borders(xlEdgeBottom)
  68.                                     .LineStyle = xlContinuous
  69.                                     .Weight = xlMedium
  70.                                     .ColorIndex = xlAutomatic
  71.                                 End With
  72.                                 With .Selection.Borders(xlEdgeRight)
  73.                                     .LineStyle = xlContinuous
  74.                                     .Weight = xlMedium
  75.                                     .ColorIndex = xlAutomatic
  76.                                 End With
  77.                                 .Selection.Borders(xlInsideVertical).LineStyle = xlNone
  78.                                 .Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
  79.                             Loop
  80.                         .Range("A1").Select
  81.                     End With
  82.             End Select
  83.         xlBook.Save
  84.         xlApp.Visible = True
  85.         Set xlApp = Nothing
  86.         Set xlBook = Nothing
  87.         DoCmd.SetWarnings True
  88. Exit Function
  89.  
  90. Trap:
  91.     Select Case Err.Number
  92.         Case 53, 1004
  93.             Resume Next
  94.         Case Else
  95.             MsgBox Err.Number & " - " & Err.Description
  96.             Exit Function
  97.     End Select
  98. End Function
  99.  
So if i change the Dim line to be
Expand|Select|Wrap|Line Numbers
  1. Dim xlApp as Object
And keep line #10 same, will that just work? or do i need to still make constants of all the "xl" references?
May 11 '12 #7
Stewart Ross
2,545 Recognized Expert Moderator Specialist
You need to change ALL object definitions that currently reference Excel objects from their Excel.xxxx form to Object. For example, in line 3 above you have

Expand|Select|Wrap|Line Numbers
  1. Dim [...], xlApp As Excel.Application, xlBook As Excel.Workbook, [...]
which has to be changed to

Expand|Select|Wrap|Line Numbers
  1. Dim [...], xlApp As Object, xlBook As Object, [...]
Line 10 is indeed a late-bound creation of the Excel object.

With the normal practice of early binding you refer explicitly to a specified object library, so that the VBA interpreter 'knows' straight away at compile time where to find the definition of the methods concerned. With late binding, the VBA interpreter can't resolve references at compile time at all. Late-bound references to objects and their methods can only be resolved at run-time (hence 'late binding', as the VBA interpreter will not be able to 'know' if the method calls are correctly formed until the code is actually run).

And yes, you will have to define Excel VBA constants such as xlSolid, xlDiagonalDown etc in your module header - or alternatively you must replace each such reference with its actual numeric value. There are many such instances in the sample code you provided.

With early binding, the compiler resolves the symbolic constants to their numeric values as contained in the object library. It can't do that with late binding - so you must either substitute their numeric values directly into your code wherever you use an Excel constant, or define them as constants in the module header as appropriate.

It is easy enough to do a search for constants beginning with 'xl....', then copy these to a list in notepad, say. You can look up the value of each constant in the Excel object library from the VBA editor before you get rid of the Excel reference in your code - just change to the object view and search for each constant (eg xlSolid). The object window will show you the constant's numeric value.

Late binding carries a performance penalty, but in your case this is neglible compared to the benefits of the approach. For more information, please see our Insight article on Early vs Late Binding by our expert ADezii.

-Stewart
May 11 '12 #8
NeoPa
32,584 Recognized Expert Moderator MVP
Personally, I avoid late-binding like the plague. I much prefer code to be tidy and defined. It makes it so much more maintainable.

That said Dan, if you want to explore late-binding, and you may well be in such a situation now, I couldn't think of anyone better to introduce/explain it to you than Stewart. Not only is his understanding very full, but he also has that uncommon ability to explain things well and fully. You couldn't be in better hands (I imagine after only a couple of posts so far in the thread, that's already obvious to you anyway).
May 11 '12 #9
Stewart Ross
2,545 Recognized Expert Moderator Specialist
@NeoPa - thank you for your very kind comments.

I agree entirely about avoiding late binding if at all possible, and would emphasise the point you make about tidiness and maintainability .

I myself have used late binding with Excel only when working in a dual A2003/A2007 environment, as it was the only practicable way to avoid problems with the references. For example, if changes were made to VBA code in A2007 the A2003 Excel reference was changed automatically and without warning by Access to the A2007 version - which resulted in failures when running again in the A2003 environment. This became unmanageable at the time, hence the late-bound approach.

Now that the environment in which my code is operating is almost entirely A2007 (with the odd A2010 instance) the same code is back to being early bound with all its normal object definitions etc in place.

Thanks again for your kind comments

-Stewart
May 11 '12 #10

Sign in to post your reply or Sign up for a free account.

Similar topics

3
2094
by: Christian Lammel | last post by:
i'm looking for a possibility in C# to get a unique Int32 handle for an object reference. This handle must later (within the same process of course) be convertible back to an object reference (call it handle or pointer or whatever...). at the moment i'm working with a Hashtable Int32 to Object, but i want to optimize this. I know it is possible to work with pointers in unsafe code blocks, but as far as i know these pointer remain valid...
1
1430
by: NickA | last post by:
I'm getting the following error: '<name>' is declared in project '<projectname1>', which is not referenced by project '<projectname2>' It just started showing up the last time I did a build. The reference is there in the project but for some reason, the IDE doesn't seem to recognize it. -- Nick
0
1070
by: Richard Sherratt | last post by:
I have an Access 97 front end to an SQL Server 2000 database. One of the functions in the front-end generates an Excel spreadsheet using a template stored on a file server and plugs in some values before saving it. Sometimes, and I haven't yet been able to establish a pattern for when, the generated spreadsheet has missing references for mc2PDF and PDFMaker.XLA. The users are on SOE machines with Office XP apart from Access which is...
0
1416
by: Gary | last post by:
Hi, there, I need to get the handle of a running excel application. However, there is no HWnd property. Following is my code: -------------------------------------------------------------- Process Processes = Process.GetProcesses(); for (int i = 0; i <= Processes.GetUpperBound(0); i++) { if (Processes.MainWindowHandle.ToString() == ExcelApp.Hwnd.ToString()) {
3
2088
by: AP | last post by:
I have an app that has one small component that uses a reference to MS Outlook 9.0 to create an email. Now we have users on 9.0 and 11.0, sometimes it causes the reference to switch and this causes the missing reference issue. What is the best way to get around this? Thanks
4
2336
by: bluewind44 | last post by:
Hello, I made a page that used for import data from .xls files. At this page, users upload the .xls file to the server, then the app uses an OleDB connection and the Jet provider to read the data using the OleDB DataAdapter, then I bind it to a datagrid. It works fine, but occationally some data is missing on a datagrid, especially number datas. Is anyone have same problem? or Is anyone can help me out?
3
2180
by: =?Utf-8?B?amV6MTIzNDU2?= | last post by:
Hi experts. I have a c# widows program that uses 3rd party dlls. All works correctly on my PC but when I try to run the exe on another PC, I get an error indicating a missing reference. I checked the other PC and all the 3rd party the dlls are present and registered. Is there anything I can do to resolve this? Thanks
9
14516
Nathan H
by: Nathan H | last post by:
I am getting this error when I install an Access 2007 db with the packaged runtime engine on a machine running Office 2003. When I look at the references on the developing PC, the Microsoft Excel Library is listed in the references. Any ideas how to begin to fix this problem?
3
2749
by: NEWSGROUPS | last post by:
I am in the midst of trying to convert about 25 Access 2000 to Access 2003. The new environment consists of Office/Access 2003 and Outlook 2003. When converting the back ends I have no problems. When converting the front ends I get the following issues and error. If I try to import all the objects into a blank Access 2003 shell I get a missing reference error for Outlook 2000, Excel 2000, and Word 2000 or after importing all the objects...
1
3803
by: DThreadgill | last post by:
Have a database that about 6 or 7 people use simultaneously with no problem and have for the past year or so. Have a new person that now needs to do work in there and she is getting Missing Reference: MS ActiveX Data Object 2.8 library. Upon checking her library, the latest version is 2.1. I don't want to make changes to the database and everything is working with ADO. What needs to be done to get her to version 2.8? Thanks in advance.
0
9933
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9781
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10734
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10836
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
9567
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7960
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5794
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5982
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
4211
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.