How To Keep Adding Rows in Excel Without Closing

[RNIB]

I've got this subroutine in my macro where it enters the data it's captured earlier on into specific cells in an Excel spreadsheet and it enters all the data on a single row across 9 columns.

After it's added the row of data the macro loops around again and when it comes to add the next data, I want it to do that on the next row.

The only way I can get this to work though, is by saving and closing the spreadsheet after each row has been added. If I don't I get an error message saying the spreadsheet is already open in another application and a Invalid Variant Operation Error message.

My subrouting code is:

Code: Select all

SRT>Write_Report
 XLOpen>C:\Users\OJohnson\OneDrive - RNIB\Desktop\Files_in_TB_Library.xlsx,1,xlBook
SetFocus>Files_in_TB_Library.xlsx - Excel
 Let>w=0
 Repeat>w
 Let>w=w+1
 Let>Row=k+3
 Let>Col=w+1
 //TB Number
 XLSetCell>xlBook,Sheet1,%Row%,%Col%,%root_folder%,scResult_root_folder
 
 //Is it Mass Ingested
 Let>Col=w+2
 XLSetCell>xlBook,Sheet1,%Row%,%Col%,%MassIngest%,scMassIngest
 
 //If Mass Ingested are there visible links in the NCC
 If>MassIngest=Yes
 Let>Col=w+3
 XLSetCell>xlBook,Sheet1,%Row%,%Col%,%NCCLinks%,scNCCLinks
 Endif
 
 //Which Data drive is it on
 Let>Col=w+4
 XLSetCell>xlBook,Sheet1,%Row%,%Col%,%data%,scData
 
 //Are there subfolders
 Let>Col=w+5
 XLSetCell>xlBook,Sheet1,%Row%,%Col%,%SubCountResult%,scSubCountResult
 
 //Names of Subfolders
 If>subcount>1
 Let>Col=w+6
 XLSetCell>xlBook,Sheet1,%Row%,%Col%,%sub_folder_result%n%,scSubfolders
 Endif
 If>subcount=1
 Let>Col=w+6
 XLSetCell>xlBook,Sheet1,%Row%,%Col%,subfolders%,scSubfolders
 Endif
 
 //Are MP3's Present
 Let>Col=w+7
 XLSetCell>xlBook,Sheet1,%Row%,%Col%,%NoMP3Folder%,scNoMP3Folder
 
//Is MP3 File Name longer than 8 characters
 Let>Col=w+8
 XLSetCell>xlBook,Sheet1,%Row%,%Col%,%MP3Length%,scMP3Length
 
 //Name of MP3 File
 Let>Col=w+9
 XLSetCell>xlBook,Sheet1,%Row%,%Col%,%mp3_result%,scmp3_result
 
 XLSave>xlBook,C:\Users\OJohnson\OneDrive - RNIB\Desktop\Files_in_TB_Library.xlsx
 XLQuit>xlBook

END>Write_Report
Until>k=folder_names_count

Is there a way of doing this without having to keep opening and closing the spreadsheet?

[Dorian (MJT support)]

This should get you started. The principle is to open the Excel file first, before the loop, then add one to Row in a loop :

Code: Select all

//PseudoCode
XLOpen>...
Let>Row=0
Repeat>This
  Let>Row=Row+1
  XLSetCell>xlh,Sheet1,%Row%,%Col%,Some Data,res
Until>This,Something

Here's a standalone version that demonstrates the principle.

Code: Select all

let>TheData=Mon^Tue^Wed
Separate>TheData,^,DataParts

let>xlfile=%script_dir%\RowPlusOne.xlsx

IfFileExists>%xlfile%
  XLOpen>%xlfile%,1,xlh
 else
  XLCreate>%xlfile%,1,xlh
Endif

WaitWindowOpen>RowPlusOne*
Wait>0.5


//Everything below here is important
Let>Row=0
Let>Col=1

Repeat>Row
  Let>Row=Row+1
  XLSetCell>xlh,Sheet1,%Row%,%Col%,DataParts_%Row%,res
Until>Row,DataParts_count

There's a can of worms with your structure too. Read more here. Repeat and Gosub