EXCEL COM

[RAK]

I am working with excel and have had great success. However, there are 2 issues I am having..

1. I am extracting a range of cells into a string variable and could not get it to work unless I used the .copy function. I would rather not effect the clipboard. Is there way to place a range of field values into a variable without the copy function?

2. I am looking for the best way to populate a range of cells in an existing sheet with only a single step. I cannot seem to get the delimiters correct so it works properly. Now I am using a for loop to populate fields from a csv file - it works fine but if the user clicks - it kills the process and errors out. I don't want to use the ignore input function as it's also slow on large data sets.

I need to set a range of cells with a data variable - any tips on the delimiters? I tried tabs and line feeds on a single cell and the whole range - does not work. Data is repeated in cells. Am I missing something?

thanks
Roy

[Deana]

I am working with excel and have had great success. However, there are 2 issues I am having..

1. I am extracting a range of cells into a string variable and could not get it to work unless I used the .copy function. I would rather not effect the clipboard. Is there way to place a range of field values into a variable without the copy function?

The following Excel COM example reads out all of the cell values from the specified Range object into a WIL Array.
http://techsupt.winbatch.com/webcgi/webbatch.exe?techsupt/tsleft.web+WinBatch/OLE~COM~ADO~CDO~ADSI~LDAP/OLE~with~Excel/Samples~from~Users+Read~Excel~Cells~Into~WIL~Dialog.txt

However you might consider using ADOs Get String method: http://techsupt.winbatch.com/webcgi/webbatch.exe?techsupt/tsleft.web+WinBatch/OLE~COM~ADO~CDO~ADSI~LDAP/OLE~with~Excel+Put~Excel~Column~into~an~Array.txt

ADO reference: http://msdn.microsoft.com/en-us/library/windows/desktop/ms676975(v=vs.85).aspx

2. I am looking for the best way to populate a range of cells in an existing sheet with only a single step. I cannot seem to get the delimiters correct so it works properly. Now I am using a for loop to populate fields from a csv file - it works fine but if the user clicks - it kills the process and errors out. I don't want to use the ignore input function as it's also slow on large data sets.

I need to set a range of cells with a data variable - any tips on the delimiters? I tried tabs and line feeds on a single cell and the whole range - does not work. Data is repeated in cells. Am I missing something?

Not sure if this is the best way...But here is a code sample to import a CSV into Excel: http://techsupt.winbatch.com/webcgi/webbatch.exe?techsupt/tsleft.web+WinBatch/OLE~COM~ADO~CDO~ADSI~LDAP/OLE~with~Excel+Import~CSV~file~into~Excel.txt

Reference: http://msdn.microsoft.com/en-us/library/office/bb225606(v=office.12).aspx

[RAK]

>quote "reads out all of the cell values from the specified Range object into a WIL Array"
Looks like the array solution will work - although it must loop through cells - not the desired solution (slower) - I could just as easily populate a string variable this way.

>quote "However you might consider using ADOs Get String method:"
ADO requires supporting files/software that the user may or may not have correct?

>quote "import a CSV into Excel"
This I have used many times but I am populating an existing sheet so I must target a specific area in the sheet - not create a new one.

Is there no way to populate a range with a string variable? It seems to work - almost - I just cannot get the delimiters correct so the correct data ends up in the correct fields. I can paste it in fine, by selecting the appropriate top left field and paste - the delimiters work great. When I try to set the proper field range value using the same data - no deal. I just want to avoid the clipboard and slower looping procedures if possible. 

One more q - The IgnoreInput Function - if ignore is enabled and the exe crashes (of course this never happens) - is user input restored?

Thanks Deana!

[Deana]

>quote "reads out all of the cell values from the specified Range object into a WIL Array"
Looks like the array solution will work - although it must loop through cells - not the desired solution (slower) - I could just as easily populate a string variable this way.

>quote "However you might consider using ADOs Get String method:"
ADO requires supporting files/software that the user may or may not have correct?

Possibly yes.
http://msdn.microsoft.com/en-us/library/ms692897(v=vs.85).aspx
http://support.microsoft.com/kb/183606

>quote "import a CSV into Excel"
This I have used many times but I am populating an existing sheet so I must target a specific area in the sheet - not create a new one.

Is there no way to populate a range with a string variable? It seems to work - almost - I just cannot get the delimiters correct so the correct data ends up in the correct fields. I can paste it in fine, by selecting the appropriate top left field and paste - the delimiters work great. When I try to set the proper field range value using the same data - no deal. I just want to avoid the clipboard and slower looping procedures if possible. 

Not that I am aware of. This question is more Excel specific rather than WinBatch. See Excel Object Model Reference: http://msdn.microsoft.com/en-us/library/office/bb149081(v=office.12).aspx

Maybe check out Excel's QueryTables Add Method: http://msdn.microsoft.com/en-us/library/office/bb178281(v=office.12).aspx. You could put the data into a temp file using FilePut() then call this method: http://msdn.microsoft.com/en-us/library/office/bb178281(v=office.12).aspx

Anyone else?

One more q - The IgnoreInput Function - if ignore is enabled and the exe crashes (of course this never happens) - is user input restored?

Thanks Deana!

Depends on what exactly you mean by crash. If the script simply errors out then input should be restored. If you are referring to a GPF, then probably not.

[td]

Mouse and keyboard input is automagically enabled (unblocked) when

• The WinBatch main thread or process expectedly or unexpectedly exits.
• The user presses CTRL+ALT+DEL.
• Any program hard faults or a device fails.

[RAK]

Mouse and keyboard input is automagically enabled (unblocked) when

• The WinBatch main thread or process expectedly or unexpectedly exits.
• The user presses CTRL+ALT+DEL.
• Any program hard faults or a device fails.

thanks.. So - I can ignore input - or hide excel until done to prevent users from clicking the window (and triggering an error) during the cell populate loop.
Is there anyway to 'lock' only the excel window while the loop is running so users cannot interrupt it but still have access to other programs? Sure wish there was a way to set values in a large field range in an existing worksheet without a loop or the clipboard.

Oh well. I will probably hide excel and display a progress bar until populate loop has completed. Seeing it happen sure looks cool as opposed to a progress bar. I am scared of the effects that the ignoreinput function may have on users (mental).   

[RAK]

I found this line in a help file - what does the insert function do?

MyRange.Insert(::SHIFT=xlDown)

[RAK]

So I gave up on avoiding the clipboard and it now generates 3 workbooks with 7 sheets in them in about 9 seconds  - that includes processing and excel launch time - some with over 1k rows. That's about <10% of the time it took using the looping populate cell functions. Users will love it and it only does what excel does anyway; clear the clipboard when you start it. duh!

thanks to Deana and td..
Roy

[stanl]

[Anyone else?

Not really sure what he is after without specifics, i.e. take this .csv data and put it in this range like this. There is always the standby of converting the , to a Tab and placing results in the top left cell of of the desired range, or using Excel's transpose() to alter something like

Data Data Data

to

Data
Data
Data

For real range tricks there are always Excel's xml functions.

[RAK]

Hey stanl,
I have been seeing your great posts for years. thanks..

When you say "placing results in the top left cell" you mean paste right?

I have a variable with lots of columns and rows in it. It is created by wb code so it's not in a csv file. I could put it into any form a solution may require. I just wanted to place the data into an existing sheet starting well below the first row without using the clipboard. I went to loops and they work fine but are slow and err if the user clicks (cool to watch though). I have tried setting the field range value with all kinds of field delimiters and line breaks. Nothing worked properly. I started to read about array methods but wanted to move on so I ended up with a paste solution that works fine.

I guess I expected there to be a method to set values in a field range with data from a string variable - as there is for a cell

thanks for chiming in..
Roy

[stanl]

When you say "placing results in the top left cell" you mean paste right?

You can place data in as a Range.Value, TAB should separate into cells, if not use the Text To Columns feature.

[stanl]

Here is the simplest example I can think of

Code (WINBATCH) Select Expand


oXL = CreateObject("Excel.Application")
oXL.Visible=1
oXL.WorkBooks.Add()
oWS=oXL.Worksheets(1)
oWS.Activate()
string = "1,2,3,4,5,6,7,8,9,10,11"
oWS.Range("A1").Value=string
oWS.Range("A1").Select
xlDelimited = 1
oXL.Selection.TextToColumns(::DataType=xlDelimited,Comma=@TRUE)
oWS=0
oXL=0
Exit


[RAK]

Here is the simplest example I can think of

This is where I started. In order to avoid using the clipboard, I wanted to select a range of fields (A1:X500) and SET the range value (.value=) using an array or delimited string. I realize now it's not so simple so I went back to using the clipboard.

thanks

[stanl]

This is where I started. In order to avoid using the clipboard, I wanted to select a range of fields (A1:X500) and SET the range value (.value=) using an array or delimited string. I realize now it's not so simple so I went back to using the clipboard.

thanks

My example did not use the Clipboard. TextToColumns() can use either a string or an array. You appear to be saying that is not simple, so you went back to the Clipboard???

[RAK]

I did not look close enough.
Looks like this is the solution I was looking for! I will implement and let you know how it worked out. - Will be a day or 2 until I can get back to it..

Thanks!
Roy

[RAK]

On further exam - I see there is a single row only. My desire is to populate many rows at once.
I just tried the above with different range settings and get the same results I had in my initial trials. The data is there - but not in the correct place..

For example
I would like to populate a multi-row field range; "A4:V1525" with data in a single command. As is possible with paste.

Thanks for you assistance..
Roy

[Deana]

Okay maybe we can finally put this thread to rest. I did some research into Excels object model and here is some sample code I came up with:

Code (winbatch) Select Expand

;**********************************************************************
;  Writing A Two Dimensional WIL Array To The Worksheet
;
; If you have a 2 dimensional array, you need to use Resize to resize the destination range to the proper size.
; The first dimension is the number of rows and the second dimension is the number of columns.
;
; The code below illustrates writing an array out to the worksheet starting at cell C3.
;
;   startingcell = "C3"
;   oDest = oWS.Range(startingcell).Resize(numrows,numcols);
;
; You can transpose the array when writing to the worksheet:
;
;   oDest.Value =  oXL.WorkSheetFunction.Transpose(arrData)
;
; Reference: http://www.cpearson.com/excel/ArraysAndRanges.aspx
;
;
; Deana Falk 2014.03.06
;**********************************************************************

; Define WB array
arrData = ArrDimension(3,5)
arrData[0,0] = "Name"
arrData[0,1] = "Address"
arrData[0,2] = "City"
arrData[0,3] = "State"
arrData[0,4] = "Zip"
arrData[1,0] = "Fred Flinstoe"
arrData[1,1] = "55 Stone St"
arrData[1,2] = "BedRock"
arrData[1,3] = "BC"
arrData[1,4] = "01959"
arrData[2,0] = "Barney Rubble"
arrData[2,1] = "123 Agate Ave."
arrData[2,2] = "BedRock"
arrData[2,3] = "BC"
arrData[2,4] = "01959"

; Get number of rows and columns
numrows = ArrInfo(arrData, 1 )
numcols = ArrInfo(arrData, 2 )

; Create Empty workbook  ( or optionally open an existing workbook )
oXL = CreateObject("Excel.Application")
oXL.Visible          = 1
oXL.ScreenUpdating   = 1
oXL.UserControl      = 1
oXL.DisplayAlerts    = 0
oXL.WorkBooks.Add(-4167)   
oWS = oXL.ActiveWorkBook.Worksheets(1)
oWS.Activate()

; IMPORTANT YOU MUST RESIZE THE RANGE FOR THE DESTINATION
startingcell = "C3" ; Starting cell for inserting the data
oDest = oWS.Range(startingcell).Resize(numrows,numcols)
;Pause(oDest.Rows.count,oDest.Columns.count) ; For debugging

; IMPORTANT YOU MUST TRANSPOSE THE ARRAY DATA BEFORE PASSING TO THE EXCEL RANGE
oDest.Value =  oXL.WorkSheetFunction.Transpose(arrData)

; Clean up
oDest = 0
oWS=0
oXL=0
Exit