Writing to Spreadsheet with a macro/VBA

[Anonymous]

I have a spreadsheet (Book1.xls) which is our master doc register.

I have another spreadsheet (Book2.xlsm) which has an external link to the part numbers and descriptions in Book1.xls so that our document list is always up to date.

I have a piece of VBA in Book2.xlsm that deletes any filters and then creates a new filter. This is automatically updated when Book2.xlsm opens, ensuring that the list of part numbers and descriptions are up to date.

My problem is, that when I have a piece of illogic to populate Book2.xlsm with data, the code throws a dialogue up that says that Book2.xlsm is already open...

The problem disappears when I remove the VBA from Book2.xlsm -  Ilogic can populate the spreadsheet; no problem.

Is anyone else writing to excel spreadsheets that have either a macro or piece of VBA within them, and if so can you please let me know how to get around this issue I'm facing?

many thanks for any help with this !!

thx

[MechMachineMan]

Sounds like you need some error catching or a different method to connect to the documents.

If you open the document with both macros and try to write to both versions I'm not surprised it would throw errors. You need to be able to check to see if the document is open already, and if it is, connect to it there, or save and close it before you open it again.

[Anonymous]

hi Justin

thanks for replying.

I think you have misunderstood me slightly though.

I only want to write to one document, Book2.xlsm

Book2.xlsm has one macro only. (the macro runs when the document is open. it simply deletes all filters then creates a new filter...nothing fancy but it is scheduled to run upon opening of the document. This ensures that the document I am writing to (Book2.xlsm) is up to date with the part numbers and descriptions in Book1.xls)

If I write to the document Book2.xlsm with the macro running upon excel opening the workbnook, it throws an error stating that the document is already open.

If I remove the macro from Book2.xlsm then it writes to the document Book2.xlsm no problem.

The issue appears to be when a (run on open of document) macro exists in the document that I try to write to. - or at least I think that is the problem...

But it makes sense what you are saying re: the checking of the status of the document!!

I'm sure its due to the macro that runs when the excel file is opened. somehow causing it to think it is already opened.

I may have to think of another way to synchronise the two excel docs without the use of an excel macro running upon startup... but I'm out of ideas.

I'll have another think about this tonight and see If I can come up with something.

If you do have any ideas ( or anyone for that matter ) that you would like to share, I'd be grateful to hear.

thanks again.

[salariua]

I am having the exact same issue. Writing to xls is fine while writing to xlsm doesn't close the document. The document remains open (checked task manager).

I have only tried GoExcel which is good enough for what I need but I might try excelApp = CreateObject("Excel.Application")  and see if that works better.

 EDIT:

forgot to mention that I use AIP 2015 and Office 2007

[Anonymous · Accepted solution]

I had another go at this last night and found a workaround.

The issue was using a macro in a spreadsheet ie .xlsm, and having illogic write to the data on the sheet that had the macro.

The workflow is as follows:-

Drawing Numbers are pulled from a master doc reg

The CAD department need to monitor the status of the drawings, so a new spreadsheet is created that has an external link to the master doc reg

This external link ensures all drawing numbers and descriptions etc are copied over to the status spreadsheet.

I then require a filter on the status spreadsheet part number column to ensure that only drawings and sketches are shown. (we have tech docs and other items in the master doc reg that I need to filter out)

The filter on the status spreadsheet was ran on an open event of the spreadsheet. (This ensured that if anyone pulled a number from the master doc reg, then the status spreadsheet would update prior to illogic opening the spreadsheet. When illogic looks for the part number it should have a complete copy from the master doc reg.

That was the plan... but  I couldn't get it to work as the status spreadsheet wouldn't close and the user would be informed that a spreadsheet was already open.

So my workaround was relatively simple...

The status spreadsheet uses Sheet1 for the linked data to the master doc reg - as before,

I then create Sheet2 and create a link to Sheet1. The filtering/macro is now done on Sheet2 when sheet2 is activated.

Ilogic only writes to Sheet1 however, and the filtering / macro part is now done on Sheet2.

So moving the macro to another sheet on the same workbook has allowed the code to work without error.....

hope this is of some help to folks with similar issues. However I'm all ears if there is a better way 🙂

[xiaodong_liang]

Hi lynlaysaymo,

Thank you for reporting this issue and shared the workaround you found!

It looks like a limitation of current design of iLogic and Excel spreadsheet. Could you provide the demo sample files? I can produce myself, but I am not clear on the section (remove filter and create new filter). After I reproduce, I can report with the engineer team. Thank you!