I have developed a database that is ported to other computer systems. My problem is that I have to hard-code the filename into the VBA code to identify the correct Application Object. I found that if I have activated another spreadsheet and run the code, it performs the actions on the wrong spreadsheet.
So my question is, is there a way to get the VBA code to automatically associate itself with the file in which it resides? That way, when I move the file to another computer, it knows what .xls file to use as the "Application Object".
here is what I have now (I replaced the actual paths with {x pathname}):
fname = "FCS Hardware Inventory - Cat Rev E - Ver 2.3.xls"
'fname = "FCS Hardware Inventory - Includes IVA Cat Rev E and JCPC - ver 2.3.xls"
'fname = "FCS Hardware Inventory - Cat Rev E - Ver 2.3 - for distribution.xls"
Set xlapp = GetObject("{1st pathname}\FCS Hardware Databases and Catalogs\" & fname)
'Set xlApp = GetObject("{2nd pathname}" & fname)
'Set xlapp = GetObject("{3rd pathname}" & fname)
Set xlapp = xlapp.Application
Display More
When I copy the file to another location, I manually uncomment the correct fname and xlApp name... a real pain, especially if I share this with a large community. I don't want to have to teach everyone how to do this every time!
thanks for any help you have to offer!