Need vbscript for exporting Temporary Table results to Excel file on Shared drive

[kdjonesmtb2]

Hello

The following vbscript works well if there are around 100 records. If the record count is over 1000 records this script does not work efficiently

Is there a more efficient way from within vbscript to export the results of sql temporary table

set rs1 = conn.execute("select distinct * from ##Variance_NHPid_Sample" )

if not rs1.eof then

set xlApp = CreateObject("Excel.Application")
Set xlWb = xlApp.Workbooks.Open("J:\Beacon Eligibility Project\Variance NHPid sample.xlsx")
Set xlWs = xlWb.Worksheets("Sheet1")

' Display Excel and give user control of Excel's lifetime
xlApp.Visible = False
xlApp.UserControl = True

' Copy field names to the first row of the worksheet
fldCount = rs1.Fields.Count
For iCol = 1 To fldCount
xlWs.Cells(1, iCol).Value = rs1.Fields(iCol - 1).Name
Next



recArray = rs1.GetRows(-1)

recCount = UBound(recArray, 2) + 1


xlWs.Cells(2, 1).Resize(recCount, fldCount).Value = TransposeDim(recArray)
'xlWs.Cells(2, 1).Resize(recCount, fldCount).Value = xlApp.WorksheetFunction.Transpose(recArray)



xlApp.Selection.CurrentRegion.Columns.AutoFit
xlApp.Selection.CurrentRegion.Rows.AutoFit


xlApp.visible = true

else
msgbox "empty"
end if

'rs30.close
'set rs30 =nothing
'
'conn.close
'set conn = nothing



Function TransposeDim(v)

 

[PHV]

I'd replace all the stuff with recArray with simply this:
xlWs.Cells(2, 1).CopyFromRecordset rs1

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886