Reading/Writing in Excel

Dominic_Fichera · Post by **Dominic_Fichera** » Mon Mar 24, 2014 12:19 pm

Hey guys,

I am trying to make a script that will use information from an excel document to feed into my program.

What I want to happen is for the Macro to pull a string of text from the excel document, "Send>" it into my program and then delete the cell it retrieved so that next time the macro runs, it pulls a new piece of information.

So, for example,

Test1
Test2
Test3
Test4
Test5

The first time I would run the macro, it would input "test1" into the appropriate field in my program. Once it was complete, the excel document would be edited to show:

Test2
Test3
Test4
Test5

The next time I run it, it would input "test2" and the document would now show:

Test3
Test4
Test5

etc.

Currently, I have something like this:

XLOpen>C:\Users\Dominic\Desktop\test.xlsx,1,xlBook
XLGetCell>xlBook,Sheet1,1,1,strCell
SetFocus>Notepad*
Send>strCell
XLDelRow>xlBook,Sheet1,1

It deletes the cell, but then it doesn't save it since when the script opens the excel document, it's "read only". I suspect this is my issue and once resolved, would be the script I need.

Looking for any insight at all!!

Thanks so much,

Dominic Fichera

Post by **Marcus Tettmar** » Mon Mar 24, 2014 12:41 pm

Not sure why your spreadsheet is opening read only. I can't replicate with a test spreadsheet so I think this must be some setting in your spreadsheet or maybe the file has been set to readonly on disk or the folder is read only. You might also want to check that it is not already open in Excel or by someone/something else.

djs · Post by **djs** » Tue Apr 15, 2014 12:19 am

From similar experience......
Open task manager, kill off all of the running Excel processes and it should open read / write on the next attempt.

Odds are, you have the spreadsheet open in another Excel process already.

Make sure you are closing excel in your code when you are done with it.

dan

Djek · Post by **Djek** » Tue Apr 15, 2014 5:50 pm

in addition of the comment of DJS

whenever excel is involved, in the start of the script I put in a check;
such as:

Code: Select all

ProcessExists>EXCEL.EXE,fodn
while>fodn=True
 MessageModal>proces Excel is allready active !!!
 goto>verder
EndWhile
label>verder

This signals a second instance of excel is open, and gives you a chance to close the other Excel.
(also take a look at the command KillProcess)

Hope this helps!
kind regards
Djek