How to adjust script to use an existing excel worksheet

[kdjonesmtb2]

Hello,

How would I update this script to using an existing workbook and worksheet

set conn = createobject("adodb.connection")
conn.open("Provider=SQLOLEDB.1;Password=password;Persist Security Info=True;User ID=webuser;Initial Catalog=Northwind;Data Source=(local)")

set rs = conn.execute("select * from customers")

if not rs.eof then

set xlApp = CreateObject("Excel.Application")
Set xlWb = xlApp.Workbooks.Add
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 = rs.Fields.Count
For iCol = 1 To fldCount
xlWs.Cells(1, iCol).Value = rs.Fields(iCol - 1).Name
Next



recArray = rs.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

rs.close
set rs =nothing

conn.close
set conn = nothing

 

[PHV]

Don't use Workbooks.Add but Workbooks.Open

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

 

[kdjonesmtb2]

Hello I want to add this script to populate the 1st sheet in the same workbook

I have been closing and reopening the same workbook but the array results disappear when I try to populate the work with the second script


Set ExcelObject = CreateObject("Excel.Application")

ExcelObject.Workbooks.Open("C:\Summary of RX Production File for May 20120531_20120614.xlsx") ',Default, False)
ExcelObject.visible = True
ExcelObject.Sheets(1).Cells(3, 1).value = "TEST FOR ECHO 1" 'Distinct RX in Weekly RX
ExcelObject.Sheets(1).Cells(3, 2).value = "TEST FOR ECHO 2" 'Distinct RXs in U_Member_Medication
ExcelObject.Sheets(1).Cells(3, 3).value = "TEST FOR ECHO 3" 'Discrepancy
ExcelObject.Sheets(1).Cells(7, 1).value = "TEST FOR ECHO 4" 'Distinct Members in Weekly RX
ExcelObject.Sheets(1).Cells(7, 2).value = "TEST FOR ECHO 5" 'Distinct Members in U_Member Medication
ExcelObject.Sheets(1).Cells(7, 3).value = "TEST FOR ECHO 6" 'Discrepancy
ExcelObject.Sheets(1).Cells(7, 4).value = "TEST FOR ECHO 7" 'Missing Member counts
ExcelObject.Sheets(1).Cells(10, 1).value = "TEST FOR ECHO 8" 'Rows of data in Weekly RX
ExcelObject.Sheets(1).Cells(10, 2).value = "TEST FOR ECHO 9" 'Rows of data in U_Member_Medication
ExcelObject.Sheets(1).Cells(10, 3).value = "TEST FOR ECHO 10" 'Discrepancy
ExcelObject.Sheets(1).Cells(10, 4).value = "TEST FOR ECHO 11" 'Rows of data not in CCA
ExcelObject.Sheets(1).Cells(12, 1).value = "TEST FOR ECHO 12" 'NDC_id/NDCDrugCode not in CCA
ExcelObject.Sheets(1).Cells(12, 2).value = "TEST FOR ECHO 13" 'NDC_id/NDCDrugCode - Discrepancy Percentage Against RX Weekly File

'ExcelObject.Activeworkbook.SaveAs("C:\Summary of RX Production File for May 20120531_20120614.xlsx") 'Using Save As

 

[SkipVought]

but the array results disappear when I try to populate the work with the second script

You are writing your results to hard-coded specific row every time!

What is significant about rows 3, 7, 10 & 12?

Why these rows each time?

What is the structure of your sheet?

What, specifically, is happening that you do not want to happen?

What, specifically, should happen instead?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[kdjonesmtb2]

Hello

What is significant about rows 3, 7, 10 & 12?

This is a spreadsheet that has a summary report that displays the results from a QTP script.

The full script is below:

I want to be able to populate Sheet1 with the Summary data from QTP and the "Variance" worksheet with the array results

Do I need to open and close the spreadsheet after I run each export to excel script or can I combine these scripts to populate the desired worksheets on the same workbook (Summary of RX Production File.xlsx)

set rs30 = conn.execute("select * from ##Variance4")

if not rs30.eof then

set xlApp = CreateObject("Excel.Application")
Set xlWb = xlApp.Workbooks.Open("J:\ARE SQL\Summary RX Checkout Final Excel Link to QTP\Summary of RX Production File.xlsx")
Set xlWs = xlWb.Worksheets("Variance")

' 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 = rs30.Fields.Count
For iCol = 1 To fldCount
xlWs.Cells(1, iCol).Value = rs30.Fields(iCol - 1).Name
Next



recArray = rs30.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)

'Dim X As Long, Y As Long, Xupper As Long, Yupper As Long
'Dim tempArray As Variant

Xupper = UBound(v, 2)
Yupper = UBound(v, 1)

ReDim tempArray(Xupper, Yupper)
For X = 0 To Xupper
For Y = 0 To Yupper
tempArray(X, Y) = v(Y, X)
Next
Next

TransposeDim = tempArray

ExcelObject.Activeworkbook.Save("J:\Summary of RX Checkout Final Excel Link to QTP\Summary of RX Production File.xlsx")
ExcelObject.Activeworkbook.Close("J:\Summary of RX Checkout Final Excel Link to QTP\Summary of RX Production File.xlsx")
End Function



'Vbscript fragment for QTP:
'TEST FOR ECHO 1 THRU 13 are the areas to have current variables output to.

Set ExcelObject = CreateObject("Excel.Application")
ExcelObject.Workbooks.Open("J:\ARE SQL\Summary RX Checkout Final Excel Link to QTP\Summary of RX Production File.xlsx") ',Default, False)
ExcelObject.visible = True
ExcelObject.Sheets(1).Cells(3, 1).value = RX_NDC_Total 'Distinct RX in Weekly RX
ExcelObject.Sheets(1).Cells(3, 2).value = CCA_NDC_Total 'Distinct RXs in U_Member_Medication
'ExcelObject.Sheets(1).Cells(3, 3).value = "" 'Discrepancy
ExcelObject.Sheets(1).Cells(7, 1).value = RX_NDC_Mem_Total 'Distinct Members in Weekly RX
ExcelObject.Sheets(1).Cells(7, 2).value = NDCmem_CCA 'Distinct Members in U_Member Medication
'ExcelObject.Sheets(1).Cells(7, 3).value = "TEST FOR ECHO 6" 'Discrepancy
'ExcelObject.Sheets(1).Cells(7, 4).value = "" 'Missing Member counts
ExcelObject.Sheets(1).Cells(10, 1).value = RXTotalRowsData 'Rows of data in Weekly RX
ExcelObject.Sheets(1).Cells(10, 2).value = CCATotalRowsData 'Rows of data in U_Member_Medication
'ExcelObject.Sheets(1).Cells(10, 3).value = "" 'Discrepancy
'ExcelObject.Sheets(1).Cells(10, 4).value = "" 'Rows of data not in CCA
ExcelObject.Sheets(1).Cells(12, 1).value = RX_Variance 'NDC_id/NDCDrugCode not in CCA
ExcelObject.Sheets(1).Cells(12, 2).value = Result4 'NDC_id/NDCDrugCode - Discrepancy Percentage Against RX Weekly File
ExcelObject.Sheets(1).Cells(15, 2).value = Result5 'NDCid/NDCDrugCode - Discrepancy Percentage Against RX Weekly File with NDC= zero records removed
ExcelObject.Sheets(1).Cells(15, 1).value = NDCDrug_zero 'Variance Total NDC code removed

ExcelObject.Activeworkbook.Save("J:\Summary of RX Checkout Final Excel Link to QTP\Summary of RX Production File.xlsx") 'Using Save As
'ExcelObject.Workbooks.Close("J:\ARE SQL\Summary RX Checkout Final Excel Link to QTP\Summary of RX Production File.xlsx") ',Default, False)


'
'
'
'
'
'
'
'
'
'
'
'
'
'
'
'

 

[SkipVought]

Do I need to open and close the spreadsheet after I run each export to excel script or can I combine these scripts to populate the desired worksheets on the same workbook (Summary of RX Production File.xlsx)

Open the workbook

In a loop, access your database to populate the workbook

Save & Close the workbook

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}