Attempting to test the ADOEX_READEXCELSHEET program. Have downloaded your Win32 headers and example spread sheets. When I compile I get an compile error (PBCC 6) on this statement "pRecordset.CursorLocation = %adUseClient" . %adUseClient is an undefined equate. Is there some other code I am missing. Thanks.
You must also be aware that these headers are not extensions to the ones provided with the compiler, but a full replacement. Therefore, you must not mix them with the PowerBASIC include files in any way, neither directly (via #INCLUDE), nor indirectly (via the include path in the IDE).
Unzip the attached file to a folder of your choice and replace the PB Include path in the PB Ide or the editor that you are using to that folder instead of C:\PBWin10\WinApi.
The usual cause is to forget to replace the path of the include files in the PB editor.
Jose, Thank you again for your response. After I got my paths defined, I was able to get your examples compiled and they ran successfully. I ran both programs with the "Test1.xls" and "Test3.xls" examples. I played around with the programs to familiarize myself with the code and what it was doing. Then I modified the code to read one of my own spreadsheets with 10 columns. That worked OK except for one column where I had mixed numbers and letters and some with all numbers. the cells with numbers and letters returned a "1" variant type (NULL), and the ones with all numbers returned a "5" (Double) Variant type. I want to read this column as a WSTRING but cant seem to get it to work with a VARIANT$$. When I do this it just prints a blank in the variable. In my Excel worksheet all the columns are formatted as GENERAL data. I tried formatting them as Text, and then Left Justifying the data. None of that made any difference. I have other columns with mixed letters and numbers and they work OK. Got any ideas what I can do
When I have difficulties such as that Roy, which is basically all the time when working with Excel, the only solution I have found is to test the data type returned with VariantVT. If its a BStr then the data can be put in a string. If a double, then into a double, etc.
A "challenge" with Excel when using "data type 'general ' is., Excel assumes there is no such thing as "text" consisting of only numeric digits... it simply MUST be a number.
Working with EDI, I work with a lot of 9-digit DUNS numbers... so if I export some data and then import it to Excel the text string "123456789" becomes +1.2345E8 (Obviously totally useless as a DUNS number).
I just change the column data type to text and then and re-import or re-paste.
Dates, too, sometimes get reformatted... "20170101" might get reformatted to "01-Jan-2017" or "01/01/17" but what I want is the actual 'text' value... and since I use my clients' systems, it's not always the same for me. Same fix: change the column data type to "text" and handle any formatting before importing or pasting.
Excel giveth and Excel throws a wrench in the works by "helping."
Tal Systems Inc.
Racine WI USA
Excel is slippery in the extreme. Just to get an idea of how slippery it is, do the following...
1) Open blank Excel spreadsheet;
2) In cell A1 type a date such as 1/17/2017 and hit [ENTER];
3) Right click on the cell A! and you'll see the data type. Excel was smart enough to recognize you typed in a date;
4) Change the data type to a number;
5) Note what Excel did; The number you are seeing is the number of days since December 30, 1899 or something weird like that.
6 Exclaim BIZARRE!
Here in the Pennsylvania Bureau of Forestry there is a whole lot of history from around the time 2005. At that time I was coding our Timber Sale System in PowerBASIC and using the daylights out of the COM Excel functionality. That epoch is known by all as the "FMT Wars". What the deal was, is that we have this massive complicated Excel Workbook where all these complicated govermental forms with the prefix FMT (for Forest Management Timber) are located. There's the FMT-19, FMT-5, FMT-25, etc. Its where foresters specify all the timber sale control data preparatory to heavy duty data processing involving a timber sale where data is written to a centaliized database, timber sale prospectuses are generated, etc., etc. Well, we had a guy at the time who was good at coding behind Excel spreadsheets. You can do lots of stuff with our data because its all inter-related and complicated. So he would be trying to do something with VBA code behind the Excel spreadsheet and find that some other data type would make his coding easier than what it was, so he would change it. Then along comes Fred's code expecting one data type but finding another. And he wouldn't tell me about it. So the phone would ring and I'd hear that something or other of mine stopped working. Of course a couple hours later I'd have it tracked down and find out good 'ole Rob changed a data type on me. What we had were all these different Excel spread sheet Forms folks used and they all had different data types. That's where the term FMT Wars came from.
The only way I could guarantee my code worked no matter what hit it was test every Excel read with VariantVT to see what was in the variant. It may be a date field, for example the Timber Sale Tour date (where the forester conducts prospective buyers around the sale to show them the timber, roads needing to be constructed, expected costs, culverts to be installed, revegetation costs, required seed mixtures, etc., etc.), but someone may have changed it to an explicit text field. If it was indeed a date field, then I'd run code to deal with it as a date. If it came out a text field, then I'd have to parse a text field that could be this...
September 30, 2006
Sept 30, 2006
Sept. 30, 2006
god knows what.
Generally I'd use a Select Case testing for a BSTR, a number, or text. Those are the most common possibilities. Is it miserable? Yep!
Thanks for the additional Comments Mike and Fred. I have the problem Mike described. I receive an EXCEL File that is a commercial invoice from Taiwan. It contains 9 columns as follows:
Sequence No.(Numeric); Item Number (mixed); Vendor Item Number(Mixed); Description (string data with lots of commas imbedded); Unit of Measure(String), Unit Cost (numeric), Extended Cost (numeric), Vendor Invoice Number (Numeric but is more than 9 characters); Our Purchase Order Number (Mixed numeric and Character string).
Seq. No. processes fine. Item Number VARIANTVT processes either String or Numeric depending if it is Mixed data or numeric only. Numeric numbers are not more than 7 characters so I can process that using VARIANTVT to capture type. Vendor Item Number is always mixed so processes as String, as does Description and Unit of Measure. The two cost fields process as numeric.
My real issues are the last two columns. Vendor Invoice number is all numbers but typically greater than 9 digits. I want to process that as a String but the VARIANTVT forces it to numeric and I get the +12345E8 kind of issues. The Purchase Order number is mixed in the test file, The first two cells(rows) of the column contain mixed data and the VARIANTVT returns a 1. Not sure what that is all about???. The remaining cells of this column are numeric and are returned as such. I want to process this as a string also.
I was / am trying to figure out a way to force the VARIANT to return a string. Tried a lot of things, but to no avail. I did try to change the EXCEL columns from GENERAL to TEXT, but that did not seem to matter. I was hoping to avoid having to re-process the EXCEL file. If I have to do that, then an easy fix is to delete the Description Column (which I dont need) and convert it to a .CSV since I have no other imbedded comma's in the data, but then I have to have the users go through an additional step to process this data. That may be my only solution, however.