hi,

i am new to brio, and have solved some problems from this group - so
much thanks!

my problem now is that i have a query that i can export to an Access
table via Excel through JOOLE if the query returns anything up to
approximately 15,000 lines. anything over this, it fails to paste
the results table into the Excel sheet, and consequently the transfer
spreadsheet command will fail as there is nothing in the sheet to
transfer.

i know this query works for up to 28,000 lines, as i have
successfully exported the results table to Access - however that was
before i had included the 'GetExcelPID' and 'KillExcelPID' scripts.

any suggestions????

script file :


// call vbs script to get PID of excel instance
// the PID is stored in a file named SomeFileName.txt
// saved in the D:\ drive home directory.
//
if (gobjFSO.FileExists(gExportScripts+"\\GetExcelPID.vbs"))

Application.Shell "wscript",gExportScripts+"\\GetExcelPID.vbs");


//
//**************************************
//

// Create a new instance of Access
var oAccess= new JOOLEObject("Access.Application");
oAccess.Visible=true;

var myTableName="";
var myDbName="";
var myExcelFileName="";
var acImport = 0;
var acSpreadsheetTypeExcel9=8;

// Set table and database names

var fullpath_dir=ActiveDocument.Path;
var file_name=ActiveDocument.Name;
var directory_location;
var slash=Application.PathSeparator
var
dble_slash=Application.PathSeparator+Application.PathSeparator

directory_location = fullpath_dir.substr(0,
(fullpath_dir.length - (file_name.length + 1)))

var db_location=directory_location.substr(directory_location,
1, 2) + ":" + dble_slash

var build_string="";
for (var cntr=3; cntr<=directory_location.length-1; cntr
=cntr+1)
{
if (directory_location.charAt(cntr) != slash)
{
build_string=build_string +
directory_location.charAt(cntr);
}
else
{
db_location=db_location + build_string +
dble_slash;
build_string="";
}
}

db_location=db_location + build_string + dble_slash
+ "Bids.mdb";


myTableName= "OPG";

// add to existing database
oAccess.OpenCurrentDatabase (db_location); // to add
to existing database


// Copy the Brio data into Excel
oExcel = new JOOLEObject ("Excel.Application");
oExcel.Visible=true;

// Create a new Document
var myExcelDoc = oExcel.Workbooks.Add;
var mySheet=myExcelDoc.Worksheets.Add;
ActiveDocument.Sections["Results - OPG R'ton"].Copy();
mySheet.Cells.Item(1).Item(1).Select;
mySheet.Paste;
myExcelDoc.Save;
myExcelFileName=myExcelDoc.FullName;



// delete contents of existing table and copy excel
workbook over table.
//
// ***** NOTE Function Args in Reverse Order to Microsoft
Documentation
// Import the data into Access
oAccess.DoCmd.RunSQL("Delete * FROM OPG;");
oAccess.DoCmd.TransferSpreadsheet(-1, myExcelFileName,
myTableName, acSpreadsheetTypeExcel9, acImport);

//Close Excel Workbook
// use application.quit to close the excel window.
//oExcel.Workbooks.Close;
oExcel.Application.Quit();
oAccess.Application.Quit();

//***************************************************************


//Finally, KILL that Excel Process!!!!
if (gobjFSO.FileExists(gExportScripts+"\\KillExcelPID.vbs"))
Application.Shell
("wscript",gExportScripts+"\\KillExcelPID.vbs");

Alert("Transfer of Vizirail bid data to Bids database now complete.");