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...
  • 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

     

TanyaYatskovska's avatar
TanyaYatskovska
SmartBear Alumni (Retired)

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