Forum Discussion

wynandb1's avatar
wynandb1
Contributor
10 years ago

Re: .VBS Script Help Again Please...

Good day

 

I need help with the following code again please. 

 

I need to open excel file A with data in, and copy the content from Cell (F1) . 

 

Then I need to open excel File B and paste the content copied from excel file A Cell (F1) to Cell (A1) in excel file B.

 

Herewith the code that I have thats not working . 

 

dim obXl,obWb,wbA,wbB

Set objXl = CreateObject("Excel.Application")
objXl.Visible = True

objXl.displayalerts=False

Set objWbA = objXl.WorkSheets.Open("C:\Users\Wynandb\Documents\Surebank_Release_Regression_Test_Data.xls")("Sheet1")
Set objWbB = objXl.WorkSheets.Open("C:\Users\Wynandb\Documents\TC_Bizbank_Batch_Import\Beneficiary.xls")("Sheet1")

objWb.Activate
wsA.Range("F1").Select
excel.Selection.Copy
wsB.Activate
wsTo.Range("A1").Select
wsB.Paste

objWbB.SaveAs "C:\Users\Wynandb\Documents\TC_Bizbank_Batch_Import\Beneficiary" & "csv", 23


objXL.Quit

Set objExcel = Nothing
set objExcelBook = Nothing

 

 Any Help would be appreciated

 

Kind Regards

Wynand

 

 

 

  • HKosova's avatar
    HKosova
    10 years ago

    Hi Wynand,

     

    • Start by putting Option Explicit at the top of each of your VBScript units, and re-run the code. It'll alert you about undeclared variables, and will help spot misspelled or misused variable names.

    • Make sure to Dim and initialize (assign values) all variables before using them. For example, your code uses the following variables that haven't been assigned values: excel, wsA, wsB, objWb, wsTo, objExcel, objExcelBook

    • To open an Excel file, use the Excel object's .Workbooks.Open method, not .WorkSheets.Open.

    • To access sheets, use a workbook object's .Sheets("sheet_name") property.

    • There's no need to use .Copy and .Paste - you can assign the cell values directly, as in a = b.

    • "C:\Users\Wynandb\Documents\TC_Bizbank_Batch_Import\Beneficiary" & "csv" - this creates the file name ...\Beneficiarycsv. It is missing the dot (.) before the extension.

    • Avoid magic numbers like 23 in your code, turn them into named constants.

     

    Option Explicit

    Const xlCSVWindows = 23
Dim objXl, objWbA, objWbB, objSheetA, objSheetB

Set objXl = CreateObject("Excel.Application")
objXl.Visible = True
objXl.DisplayAlerts = False

Set objWbA = objXl.Workbooks.Open("C:\Users\Wynandb\Documents\Surebank_Release_Regression_Test_Data.xls")
Set objSheetA = objWbA.Sheets("Sheet1")
Set objWbB = objXl.Workbooks.Open("C:\Users\Wynandb\Documents\TC_Bizbank_Batch_Import\Beneficiary.xls")
Set objSheetB = objWbB.Sheets("Sheet1")

objSheetB.Range("A1").Value2 = objSheetA.Range("F1").Value2
objWbB.SaveAs "C:\Users\Wynandb\Documents\TC_Bizbank_Batch_Import\Beneficiary.csv", xlCSVWindows

objWbB.Close
objWbA.Close
    objXl.Quit

     

  • HKosova's avatar
    HKosova
    SmartBear Alumni (Retired)
    10 years ago

    Hi Wynand,

     

    • Start by putting Option Explicit at the top of each of your VBScript units, and re-run the code. It'll alert you about undeclared variables, and will help spot misspelled or misused variable names.

    • Make sure to Dim and initialize (assign values) all variables before using them. For example, your code uses the following variables that haven't been assigned values: excel, wsA, wsB, objWb, wsTo, objExcel, objExcelBook

    • To open an Excel file, use the Excel object's .Workbooks.Open method, not .WorkSheets.Open.

    • To access sheets, use a workbook object's .Sheets("sheet_name") property.

    • There's no need to use .Copy and .Paste - you can assign the cell values directly, as in a = b.

    • "C:\Users\Wynandb\Documents\TC_Bizbank_Batch_Import\Beneficiary" & "csv" - this creates the file name ...\Beneficiarycsv. It is missing the dot (.) before the extension.

    • Avoid magic numbers like 23 in your code, turn them into named constants.

     

    Option Explicit

    Const xlCSVWindows = 23
Dim objXl, objWbA, objWbB, objSheetA, objSheetB

Set objXl = CreateObject("Excel.Application")
objXl.Visible = True
objXl.DisplayAlerts = False

Set objWbA = objXl.Workbooks.Open("C:\Users\Wynandb\Documents\Surebank_Release_Regression_Test_Data.xls")
Set objSheetA = objWbA.Sheets("Sheet1")
Set objWbB = objXl.Workbooks.Open("C:\Users\Wynandb\Documents\TC_Bizbank_Batch_Import\Beneficiary.xls")
Set objSheetB = objWbB.Sheets("Sheet1")

objSheetB.Range("A1").Value2 = objSheetA.Range("F1").Value2
objWbB.SaveAs "C:\Users\Wynandb\Documents\TC_Bizbank_Batch_Import\Beneficiary.csv", xlCSVWindows

objWbB.Close
objWbA.Close
    objXl.Quit

     

    • wynandb1's avatar
      wynandb1
      Contributor
      10 years ago

      Hi Helen

       

      Thank you or your help. 

       

      I will certainly follow these guidelines. However there is still a problem with the script. 

       

      I am getting the following error when I run this code.

       

      Line 13 Char 1 Error : Subscript out of range.  

       

      Could you perhaps assist  please ? 

       

      Option Explicit

      Const xlCSVWindows = 23
Dim objXl, objWbA, objWbB, objSheetA, objSheetB

Set objXl = CreateObject("Excel.Application")
objXl.Visible = True
objXl.DisplayAlerts = False

Set objWbA = objXl.Workbooks.Open("C:\Users\Wynandb\Documents\Surebank_Release_Regression_Test_Data.xls")
Set objSheetA = objWbA.Sheets("Sheet1")
Set objWbB = objXl.Workbooks.Open("C:\Users\Wynandb\Documents\TC_Bizbank_Batch_Import\Beneficiary.xls)
Set objSheetB = objWbB.Sheets("Sheet1")

objSheetB.Range("A1").Value2 = objSheetA.Range("F1").Value2
objWbB.SaveAs "C:\Users\Wynandb\Documents\TC_Bizbank_Batch_Import\Beneficiary.csv", xlCSVWindows

objWbB.Close
objWbA.Close
      objXl.Quit
      • HKosova's avatar
        HKosova
        SmartBear Alumni (Retired)
        10 years ago

        Assuming line 13 is

        Set objSheetB = objWbB.Sheets("Sheet1")

        double-check the sheet name in your Beneficiary.xls file and update the code accordingly.

        If your Excel files have just one sheet, you can also use .Sheets(1) instead of .Sheets("sheet_name").



        P.S. Generic scripting questions that aren't directly related to TestComplete have better luck being answered on programming forums like Stack Overflow.

  • TanyaYatskovska's avatar
    TanyaYatskovska
    SmartBear Alumni (Retired)
    10 years ago

    Hi Wynand,

     

    Here are some notes based on your code:

     

    '...
objWb.Activate 'I think you should activate the excel file A here
wsA.Range("F1").Select
excel.Selection.Copy
wsB.Activate ' wsB wan't declared. You shoyld activate the excel file B here
wsTo.Range("A1").Select
wsB.Paste ' wsB wasn't assigned
objWbB.SaveAs "C:\Users\Wynandb\Documents\TC_Bizbank_Batch_Import\Beneficiary" & "csv", 23

objXL.Quit
Set objExcel = Nothing
set objExcelBook = Nothing

     

    • wynandb1's avatar
      wynandb1
      Contributor
      10 years ago

      Hi Tanya

       

      This is what I tried after your comments. Please note that I am not a developer. This is code that I scaped together from the internet and tweaked it a little. 

       

      dim obXl,obWb,wbA,wbB

      Set objXl = CreateObject("Excel.Application")
      objXl.Visible = True

      objXl.displayalerts=False

      Set objWbA = objXl.WorkSheets.Open("C:\Users\Wynandb\Documents\FileA.xls")("Sheet1")
      Set objWbB = objXl.WorkSheets.Open("C:\Users\Wynandb\Documents\TC_Bizbank_Batch_Import\FileB.xls")("Sheet1")

      objWb.Activate("C:\Users\Wynandb\Documents\FileA.xls")
      wsA.Range("F1").Select
      excel.Selection.Copy
      wsB.Activate("C:\Users\Wynandb\Documents\TC_Bizbank_Batch_Import\FileA.xls")
      wsTo.Range("A1").Select
      wsB.Paste("C:\Users\Wynandb\Documents\TC_Bizbank_Batch_Import\FileB.xls")

      objWbB.SaveAs "C:\Users\Wynandb\Documents\TC_Bizbank_Batch_Import\Beneficiary" & "csv", 23


      objXL.Quit

      Set objExcel = Nothing
      set objExcelBook = Nothing