Help On Array Loop
Hi, I'm trying to read from one excel sheet and write the same data to another excel sheet, below is the code what I wrote, but I'm unable to write all the data to the target sheet... only last...
Forum Discussion
Hi Allen,
Thank you very much for the code... it is working as expected.
One more thing I want to ask is... the excel file is not closing... I wrote below line to save and close th excel, but still I see excel pop-up saying
"Do you want to save the changes you made to 'MyResults.xlsx'"
Sub anotherexcelvalues
'open other excel file
Set Excel1 = Sys.OleObject("Excel.Application")
Set ExlFile = Excel1.Workbooks.Open("E:\Exported Reports\MyResults.xlsx")
Set Shtname = Excel1.Sheets("Sheet1")
Delay 500
'call other excel data
ArrayOfValues = checkexcelcells
For i = 0 to UBound(ArrayOfValues)
If ArrayOfValues(i) <>"" Then
Shtname.Cells(i + 1,1).Value = ArrayOfValues(i)
End If
Next
'Close excel connection 'added these lines to save and close the excel
Call Excel1.ActiveWorkbook.Save
Call Excel1.Workbooks.Close
End Sub
Please suggest!!
Thank you very much for the code... it is working as expected.
One more thing I want to ask is... the excel file is not closing... I wrote below line to save and close th excel, but still I see excel pop-up saying
"Do you want to save the changes you made to 'MyResults.xlsx'"
Sub anotherexcelvalues
'open other excel file
Set Excel1 = Sys.OleObject("Excel.Application")
Set ExlFile = Excel1.Workbooks.Open("E:\Exported Reports\MyResults.xlsx")
Set Shtname = Excel1.Sheets("Sheet1")
Delay 500
'call other excel data
ArrayOfValues = checkexcelcells
For i = 0 to UBound(ArrayOfValues)
If ArrayOfValues(i) <>"" Then
Shtname.Cells(i + 1,1).Value = ArrayOfValues(i)
End If
Next
'Close excel connection 'added these lines to save and close the excel
Call Excel1.ActiveWorkbook.Save
Call Excel1.Workbooks.Close
End Sub
Please suggest!!