The below code does not work in PB and throws up an error message 'Unknown variable' with Workbooks highlighted. Any thoughts? Thanks
Dim wkbk, wksh As Object
Set wkbk = Workbooks.Open(Filename:="Datafilename PI.xlsx")
You may want to check if there is a reference to the Microsoft Excel x.0 Object Library (where x is a number that corresponds to the version of Microsoft Office installed in the computer where you run ProcessBook) within the references in the VBA editor for your ProcessBook display.
Thanks Daniel & Jesse,
Your suggestion resolved the issue. It did not occur to me to look at References as a different pdi file opened an excel file without an error. Only after your response when I checked I found that the Reference was not checked for this particular pdi file. Is there a way to check it by default through code for any pdi file or when someone else opens the pdi file in their system? Thanks in advance.
The References that are enabled are stored on a file basis which means that every time you start a new Processbook file, the reference will not be checked. A way to workaround this is to create a new Processbook file, add the reference, save the file. Then use the file always when creating a new Processbook file.
This is obviously not a pretty workaround since it requires you to use the Save-As option as opposed to the Save option the very first time or else, you'll overwrite your blank file (something that is easily forgotten).
If the file already contains the reference, a new user on a different system will still have that reference checked.
Thanks Jesse. Appreciated.
I was able to confirm Daniel's suggestion. In order for the script to run, you need to enable the Microsoft Excel x.0 Object Library from the VBA Editor under Tools>References
Retrieving data ...