Such as topic, through Ado connection excel, access to excel "customer number", "customer name", "sales amount" three column value, and carries on the summary to the "sales amount", "customer number" column has been set up as the text type, when "customer number" column in "0" the beginning of the value, to get the customer number of null values, how do I solve? thank you ~

 con. The Open ". The provider=Microsoft Jet. The OLEDB. 4.0; Data source="& amp; TxtPath. Text & amp; "; Extended Properties=8.0 "" Excel; HDR=yes; Imex=1 ", "
"Rs. Open the "select customer number, customer name, sum (sales amount) as the company application form amount from [sheet1 $] group by customer number, name of the customer order by customer number", con, 3, 3 

CodePudding user response:

How no one ah

CodePudding user response:

The simplest method, customer number column does not use pure Numbers, for example, in front with a letter "C",

CodePudding user response:

And a bit more complicated, in Excel file down the same path a Schema. The ini file, declare the type of the columns, the length of the properties, such as online search Schema. Ini,

CodePudding user response:

refer to the second floor of123 response:

the most simple way to customer number column without the use of pure Numbers, for example in front with a letter "C",

reference of123 reply: 3/f

and a bit more complicated, in Excel file down the same path a Schema. The ini file, declare the type of the columns, the length of the properties, such as online search Schema. Ini,

Customer number is not a fixed length, if not excel to do change, only change the VB code to implement?

CodePudding user response:

It seems that your customer number is digital, in EXCEL, automatically judgment is that the data type, when reading EXCEL, judging by its data type, therefore, there will be a mistake in VB, the simple method is in a statement read is:
If Excel customer number=0 then the rs! Customer number="0" should be ok

CodePudding user response:

Try to change

"The provider=Microsoft. Jet. The OLEDB. 4.0. Data source="& amp; TxtPath. Text & amp; "; Extended Properties=8.0 "" Excel; HDR=Yes; IMAX=1 ", "
"
If not, add the Schema only. Ini, which should have MaxScanRows=0 specified scan all the records,

CodePudding user response:

reference of123 reply: 3/f

and a bit more complicated, in Excel file down the same path a Schema. The ini file, declare the type of the columns, the length of the properties, such as online search Schema. Ini,

this recruit enough" alone "!