Hi fifijr,
I notice its been a while since you posted this question so I hope the advice is still current.
With regard to your referencing problem you appear to be describing an early binding solution. Early binding is great for development and use on a single development / operating machine but can fall over (i.e. lose the reference) when switching between machines. If you are going to distribute a bit of code I would always advise that you develop the code with early binding but then convert the code to late binding for distribution onwards. Late binding does not rely on the project having a specified file path to a given reference but rather lets VBA go and find the correct project library at run-time (note that this will make your code run slightly slower). To illustrate how to use early and late binding and the differences between them I will show a couple of very simple bits of code that do the same thing with the two methods.
Code:
Sub EarlyBinding()
' Declare objects
Dim scrFSO As Scripting.FileSystemObject
Dim scrTS As Scripting.TextStream
' Initiate FileSystemObject - NB can also be done in declaration line
Set scrFSO = New Scripting.FileSystemObject
' Create a new text file
Set scrTS = scrFSO.CreateTextFile("C:\Test1.txt")
scrTS.WriteLine Text:="Hello World"
scrTS.Close
' Dereference variables - memory management
Set scrTS = Nothing
Set scrFSO = Nothing
End Sub
Sub LateBinding()
' Declare objects
Dim scrFSO As Object
Dim scrTS As Object
' Initiate FileSystemObject
Set scrFSO = CreateObject("Scripting.FileSystemObject")
' Create a new text file
Set scrTS = scrFSO.CreateTextFile("C:\Test2.txt")
scrTS.WriteLine Text:="Hello World"
scrTS.Close
' Dereference variables - memory management
Set scrTS = Nothing
Set scrFSO = Nothing
End Sub
The key things to note here are that:
1) Under late binding you do not need any project references
2) Under late binding you declare all late bound variable as Object variable type
3) To initiate a variable under late binding you use the CreateObject function. You pass to the CreateObject function the library and the object name delimited by a full stop as a string.
4) All other coding remains as per early binding
5) You will not get any intelli-sense drop down boxes when writing late bound code as VBA only hooks into the library at run-time (hence the recommendation to code as early bound).
As to your second point this sounds like a project structuring issue and therefore altogether harder to answer. If you just want to mark out imported data sheets by the sheet name then you can easily change the name of the sheet using something along the lines of:
Code:
dim sh as Worksheet
' Other code...
Set sh = ThisWorkbook.Sheets("Sheet1")
' or whatever else you may be using in your code...
' Other code...
sh.Name = "INPUT - " & sh.Name
However I guess that I'm missing some of the subtlety of your problem as this is a trivial solution for someone who is automating external libraries.
HTH,
Maccas
2 problems: reference to scrrun.dll / worksheets
