Excel Conversion {challenge???}

[stanl]

In my new role, I process data extracts from a Predictive Dialer, the format is basic .xls (meaning all columns are essentially formatted as General with hidden apostrophes).
If you don't know about hidden apostrophes, you might see a value like 100 but it is actually a char, and using Excel formatting to make it a number FAILS. You have to essentially copy it and paste it back as itself*1....
This was normally done manually, but I am putting together an XLClean compiled script...
and that is the easy part. Another column comes in as say 00:24:16 - but in General format cannot be converted to DateTime.  The current process is to create a formula that is
00:24:16[the cell value]/0.041666667  {which we all know is 5/12.... ha! ha!}... to obtain the numeric time as percentage of an hour. This is computed in a separate column, copied down then pasted back as values then copied and pasted to original column as values, then column is deleted
anyway... My Winbatch script has to process all rows with the following: NOTE: tOff refers to the column offset as the data can appear in different columns depending upon the query export.

Code (WINBATCH) Select Expand


      v = oWS.Cells(r,tOff).Value:""
      v1 = (Int(ItemExtract(1,v,":"))*3600 + Int(ItemExtract(2,v,":"))*60 + Int(ItemExtract(3,v,":")))
      v1=(v1*1.000000000)/(3600*1.000000000)
      oWS.Cells(r,tOff).Value=v1


Which equals the same value and same number of decimal places as the manual process/fomula.
The issue is the data often contains more than 20,000 rows and the WB script takes often 10 minutes to complete, whereas doing it manually takes a lot less.
I have tried to write macros to copy the range and convert all and then move to WB, but failed.
Anyone?

[DAG_P6]

What about a variation on the formula that I posted in "YmdHms to Native Excel Date/Time," in the script exchange section? I've applied the same technique, in similar circumstances, to other date and time strings that Excel didn't interpret as dates.

[stanl]

What about a variation on the formula that I posted in "YmdHms to Native Excel Date/Time," in the script exchange section?

David;
Obviously I misunderstood your post about Excel Dates, as you may have understood mine. The point is (in this post) not so much accomplishing the task but speed. If you are going to have WB interact with Excel then WB should take the lead, otherwise write macro/vba code [which I prefer not to do].

[DAG_P6]

What about a variation on the formula that I posted in "YmdHms to Native Excel Date/Time," in the script exchange section?

David;
Obviously I misunderstood your post about Excel Dates, as you may have understood mine. The point is (in this post) not so much accomplishing the task but speed. If you are going to have WB interact with Excel then WB should take the lead, otherwise write macro/vba code [which I prefer not to do].

That's fair enough.  :)

I understand your reluctance to write VBA macros. However, I've found many applications in which a VBA implementation makes much more sense. However, rather than store the code in the workbook, I often store it in an add-in module (XLAM file). I have several such add-ins that load whenever I start Excel. One of them is devoted entirely to worksheet functions that greatly extend the reach of Excel. Among other things, they can do the following.

• Test for the presence or absence of any file.
• If a file exists, report any of its three dates (Modified, Created, and Accessed).
• For a file that exists, report its size.
• For a PE file (EXE or DLL) that contains version resources, report any of the common string values (Comments, Description, CompanyName, ProductName, OriginalFileName, FileVersion, ProductVersion, LegalCopyright, LegalTrademarks)
• Report True or False if a string starts with or ends with another string. Unlike Left() and Right(), this function is smart enough to test the string length first, and report FALSE if it's too short to use with Left and Right.

In a separate add-in, I have a routine, wired to a keyboard shortcut, that creates an index of the workbook, in a sheet called Index, which is made the first sheet in the book. The first column of the sheet contains a row for each sheet, which contains its name, as it appears on its workbook tab. Presently, if the sheet exists, it is emptied and repopulated. I am considering changing it so that it preserves anything in adjacent cells, and keeps it aligned with the worksheet name that appeared next to it when it was entered. When implemented, this improvement will enable you to save annotations and other information in the index sheet.

[stanl]

I understand your reluctance to write VBA macros.

Not often a matter of reluctance. Most places I have worked distribute Excel files and do not want Macro code included, and with Office 2007+ that means no .xlxm files. I have used .bas files which I drag into an Excel 'sheet' in the VBA Editor then execute in order to build 1 or more Pivot tables, or I use WB to access the VBAProject object and paste in code to perform a process. My recent post about the replace function is a case in point. I am pointing together a compiled WB to 'clean' a number of different mainframe imports into Excel (for eventual uploading to Access Tables). With some 'old school' INI entries and a list box the exe handles up to seven different scenarios which otherwise would require a macro for each.
As for getting file or metadata, there is already ample WB code out there to do that.

[rayche]

Stan,
We have been working with large Excel files for years and found out that processing cells one by one in Excel is REALLY SLOW. We have found a way to greatly increase speed: we use OLE to save the file as tab-limited text, load it in an array, do the processing and generate a tab-delimited output that we paste back in Excel.

Raymond

[stanl]

We have been working with large Excel files for years and found out that processing cells one by one in Excel is REALLY SLOW. We have found a way to greatly increase speed: we use OLE to save the file as tab-limited text, load it in an array, do the processing and generate a tab-delimited output that we paste back in Excel.

I have used a similar procedure (fabricated recordsets rather than arrays). This thread is more about using VBA and type conversions, but your point is well taken.