Add In needs to get information from a Workbook

[lostitagain]

using excel 2010

I am attempting to create an addin that will allow a user to input information into a userform and then search a Source Workbook for a matching entry and copy it into the current Workbook. I can get it to work by opening the Source Workbook and copying the sheets and entries needed.

What I would like to do, if possible, is embed the Source Workbook into the addin itself so that I do not have to open up the workbook to get the information needed.

Any help would be appreciated.

 

[Firefly2012]

What actual help do you need? An add-in is just an Excel workbook so it has sheets so you could paste data from the source workbook into the add-in and then source your data from the add-in worksheet?

 

[lostitagain]

I tried to include the macros in the source workbook and save it as an addin, but when I ran the addin it did not recognize any worksheets, named ranges, or information from the source workbook and I could not open the workbook itself to change the information in it.

I need a way to access this information that is faster than having the vba code open the Source Workbook anytime I need to copy a template sheet or search for and copy an entry from the source data. I also need to be able to update the information in the Source Workbook when new revisions are made.

 

[Firefly2012]

Assuming you have the add-in loaded in Excel, open up the VBE and navigate to it in the top left Project window. Click on its ThisWorkbook icon, and in the Properties window which should open below the Project window, set the IsAddin property to False. This will make the add-in workbook visible in Excel where you can manipulate the sheets & contents as you wish. When you have finished, just rest the IsAddin property, close Excel and confirm Yes to do you want to save changes.

 

[lostitagain]

Thank you. That gave me access to the workbook again and works great for updating the information.

I stilll have a problem with the macros accessing information in the workbook though. Everytime I try to access the workbook, a sheet in the workbook, or a named range in it I get a Subscript out of range (Error 9).

 

[Firefly2012]

Do you refer to the activeworkbook and/or activesheet in the code? Add-ins can't by definition be the activeworkbook. Perhaps post up some code that exhibits this problem?

You would need to explicitly reference the add-in eg via:

ThisWorkbook.Sheets("SomeSheet").Range("A1:C100").Copy

 

[lostitagain]

That's exactly what I was getting ready to post.

Set FromBook = ThisWorkbook
Set CurrBook = ActiveWorkbook

Now I can access the pages fine.

Thank you for the help.

 

[Firefly2012]

You're welcome