Use SQL statements to obtain qualified data sets,
Data set to one of the two fields, write excel non-adjacent two columns in the cell. How to do?
I now use is cycle

The first line put name
For J=2 to the end of the data set a
Cells (j, 1)=data set of the first, the first field
Cells (j, 11)=data set of the first, the fifth field
Next j


So, found the speed is slow, are there any better way?

CodePudding user response:

 'do two queries 
'recordset rs1 select only the first field 
'recordset rs2 select only the fifth field 
'no need to loop can bulk copy 
Range (" A2 "). CopyFromRecordset rs1 
Range (" K2 "). CopyFromRecordset rs2 

CodePudding user response:

CopyFromRecordset positive solution,
+ 10086

CodePudding user response:

The Excel as a Jet Engine external database, write directly Insert Into... Select * From... .

CodePudding user response:

Thanks for guidance, continue to consult,

I have listed in the SQL table is as follows:
Record properties, of the date, amount, time, the recorder, the serial number (on the)
There are eight categories
record propertiesI need to excel in each of the four properties listed in the table, as follows:
Table 1:
Sale in China metal (domestic plastic wood sale in China paper record serial number (domestic plastic metal serial number (domestic wooden record serial number (domestic paper serial number
2.00 3.00 10.00 5.00 12 14 16 18
1.00 30.00 5.00 6.00 13 15 17 19
.
Table 2:
Export metal export plastic...
Similar table structure,
Generating SQL query in the table after write excel table
Is the definition of 16 different RECORDESET recordset, and then assigned to excel, or define a recordset, use different 16 queries on a SELECT statement, the assignment?
What are the advantages and disadvantages?

CodePudding user response:

reference 4 floor qq361161637 response:

thank guidance, continue to consult,

I have listed in the SQL table is as follows:
Record properties, of the date, amount, time, the recorder, the serial number (on the)
There are eight categories
record propertiesI need to excel in each of the four properties listed in the table, as follows:
Table 1:
Sale in China metal (domestic plastic wood sale in China paper record serial number (domestic plastic metal serial number (domestic wooden record serial number (domestic paper serial number
2.00 3.00 10.00 5.00 12 14 16 18
1.00 30.00 5.00 6.00 13 15 17 19
.
Table 2:
Export metal export plastic...
Similar table structure,
Generating SQL query in the table after write excel table
Is the definition of 16 different RECORDESET recordset, and then assigned to excel, or define a recordset, use different 16 queries on a SELECT statement, the assignment?
What are the advantages and disadvantages?

Or use the query again produce different child records?

Record set the Filter properties can Filter the records, "produced" a temporary recordset:
ADODC1. You. Filter="score=" qualified ", "
N1=ADODC1. You. RecordCount 'qualified number
ADODC1. You. Filter="grades=" unqualified ""
N2=ADODC1. You. RecordCount 'unqualified number
N3=n1/(n1 + n2) 'qualified rate
To restore the original data:
ADODC1. You. Filter=""

CodePudding user response:

If considering the data quantity is big, should use again with the same recordset, finishing data release can reduce memory requirements,
Amount of data of two kind of way is couldn't feel the difference,
# 5 Filter method is not suitable for large data,

And you can put the amount of a category, the serial number of disposable to output two columns are selected, a list of four categories of output by Excel their own column shear again after insert function to adjust the order of the columns,
A column is too inefficient,

CodePudding user response:

refer to 6th floor Tiger_Zhao response:

if considering the data quantity is big, should use again with the same recordset, finishing data release can reduce memory requirements,
Amount of data of two kind of way is couldn't feel the difference,
# 5 Filter method is not suitable for large data,

And you can put the amount of a category, the serial number of disposable to output two columns are selected, a list of four categories of output by Excel their own column shear again after insert function to adjust the order of the columns,
A column is too inefficient,

Educated, thank you.

CodePudding user response:

Using array output, the fastest, but can't customize excel format, all text,