Create muliple sheets using querytable

  • Thread starter Thread starter mdengler
  • Start date Start date
M

mdengler

I'm currently able to export query results to an Excel file via the
querytable method. It is working great... and also very fast, but it
limited to 65xxx records. Is there any way for me to use a querytable
and to programmatically spill over to additional worksheets should I
exceed the Excel 65xxx threshold?

Examples would be appreciated. Thanks.
 
Another approach (if you don't need it all there at once) is to use you
database as the source for a pivot Table and use pagefields so you can look
at subsets of the data. This would be particularly more applicable if you
are summarizing your data anyway and retrieving it is an intermediate step.

Another approach would be to build queries in Access (making an assumption
here) and build the queries to break up your database. then build separate
querytables to the queries.
 
Just to expand on Tom's first point about the pivot table, the pivot table is
not limited to 65k records (depending on the amount of data you can approach
a million records) and you can use your existing query as the source for your
pivot (The first step in the pivot table wizard is to select the data source.
If you select External Data and then select queries you can find your query
and then you are off to the races.
 
Tom,

Thanks a bunch for the quick response! I appreciate it. I've changed
modified my export code to use "CopyFromRecordset", but unfortunately
when using this method (as well as using a multi-dimensional array), I
run into a situation whereby I cannot import any record greater than
911 characters into an excel cell. Anything less than this amount of
characters works fine.

Ideas?
 
Tom,

Thanks a bunch for the quick response! I appreciate it. I've changed
modified my export code to use "CopyFromRecordset", but unfortunately
when using this method (as well as using a multi-dimensional array), I
run into a situation whereby I cannot import any record greater than
911 characters into an excel cell. Anything less than this amount of
characters works fine.

Ideas?
 
To exapnd on my last reply, I'm including part of a method that uses a
DataTable (then copies to a multi-deminsional array object). This code
works, meaning it will successfully create as many worksheets as
necssary in the workbook. It just fails if any of the cells exceeds
911 characters.

int num_rows=mydatatable.rows.count;
int num_cols=mydatatable.columns.count;
int max_rows_per_sheet=65000;
if (num_rows<=max_rows_per_sheet)
{
max_rows_per_sheet=num_rows;
}
int row_index;
int col_index;
int sheet_index=1;
int sheet_row_counter=0;
object[,] objdata = new object[num_rows,num_cols];
object[,] objdata2 = new object[max_rows_per_sheet,num_cols];
object[] objheaders = new object[num_cols];

// excel object references.
excel.application objexcel = null;
excel.workbooks objbooks = null;
excel._workbook objbook = null;
excel.sheets objsheets = null;
excel._worksheet objsheet = null;
excel.range objrange = null;
excel.font objfont = null;

// start a new workbook in excel.
objexcel = new excel.application();
objexcel.sheetsinnewworkbook = 1;
objbooks = (excel.workbooks)objexcel.workbooks;
objbook = (excel._workbook)(objbooks.add(missing.value));
objsheets = (excel.sheets)objbook.worksheets;
objsheet = (excel._worksheet)(objsheets.get_item(sheet_index));

//build a header array
col_index=0;
foreach(datacolumn col in mydatatable.columns)
{
objheaders[col_index] = col.columnname;
col_index++;
}

//build an array of data rows
row_index=0;
foreach(datarow row in mydatatable.rows)
{
col_index=0;
foreach(object item in row.itemarray)
{
objdata[row_index,col_index] = item.tostring();
col_index++;
}
row_index++;
}

//build the spreadsheet
try
{
for(row_index=0; row_index<num_rows; row_index++)
{
if (sheet_row_counter==0)
{
objdata2 = new object[max_rows_per_sheet,num_cols];
}

//create an array of row values
for (col_index=0; col_index<num_cols; col_index++)
{
objdata2[sheet_row_counter,col_index] =
objdata[row_index,col_index];
}
sheet_row_counter++;

if (sheet_row_counter==max_rows_per_sheet || row_index==num_rows-1)

{
if (sheet_index>1)
{
objsheet =
(excel._worksheet)objbook.worksheets.add(missing.value,objsheet,missing.value,missing.value);

objsheet.name=\"sheet\"+(sheet_index);
}
else
{
objsheet =
((excel._worksheet)objexcel.worksheets[\"sheet\"+sheet_index]);
}

//add the header to the worksheet
objrange =
objsheet.get_range((excel.range)objsheet.cells[1,1],(excel.range)objsheet.cells[1,num_cols]);
objrange.set_value(missing.value, objheaders);
objfont = objrange.font;
objfont.bold=true;

//add the data rows to the worksheet
objrange =
objsheet.get_range((excel.range)objsheet.cells[2,1],(excel.range)objsheet.cells[max_rows_per_sheet+1,num_cols]);
//transfer the array to the worksheet starting at cell a2.
//*********************************************************
//* here is where we bomb if objdata2 is > 911 characters
//*********************************************************
objrange.value2 = objdata2;

sheet_row_counter=0;
sheet_index++;
}
}
 
Back
Top