CODEWITHSUNDEEP
vbaexcelvbscript
Gerle Batde
Gerle Batde

Reputation: 193

Editing excel file using vbscript but the edits are taking too long to complete

/* The reason why I am using vb scrips to edit the excel file is: the excel file will be different all the time but the format will be the same and the user does not want to save VBA macro on their workbook.

I am getting values from Column 3 and adding those values to Column 9 at the end of it.

*/

Set xlApp=CreateObject("Excel.Application")
Set xlBook=xlApp.Workbooks.Open("filepath", 0, true)
xlApp.visible=true


Set xlSheet=xlBook.Worksheets("Proposal Spreadsheet")
row=xlSheet.UsedRange.Rows.Count
col=xlSheet.UsedRange.Columns.Count

dim i 

For i=15 to xlSheet.UsedRange.Rows.Count 
cell=xlSheet.Cells(i,3).Value
celli=xlSheet.Cells(i,9).Value+"("+cell+")" //I am combining column 3 with column 9

xlSheet.Cells(i,9).Value=celli


Next

xlBook.Save
xlApp.Run "Submit_To_iDesk"
xlBook.Close

xlApp.Quit
Set xlApp=Nothing 
Set xlBook=Nothing 
WScript.Quit

Upvotes: 0

Views: 224

Answers (2)

user6432984
user6432984

Reputation:

The key to speeding up your procedure is reducing the number of write operations.
Here is how I would do it

  • Set a range variable (target) to the target cells in column 9
  • Create an array of values from the target range
  • Edit the elements of the values array Write the values array back over the original
  • target range in 1 operation
Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Open("C:\Users\best buy\Desktop\Book1.xlsx", 0, True)

xlApp.Visible = True
xlApp.ScreenUpdating = False
Set xlSheet = xlBook.Worksheets("Proposal Spreadsheet")

Dim i, values, LastRow, target

With xlSheet
    LastRow = xlSheet.UsedRange.Rows.Count
    Set target = .Range(.Cells(15, 9), .Cells(LastRow, 9))
    values = target.Value

    For i = LBound(values, 1) To UBound(values, 1)
        values(i, 1) = values(i, 1) & "(" & .Cells(i + 15 - LBound(values, 1), 3) & ")"
    Next
    target.Value = values
End With

xlBook.Save
'xlApp.Run "Submit_To_iDesk"
xlBook.Close
xlApp.ScreenUpdating = True
xlApp.Quit
Set xlApp = Nothing
Set xlBook = Nothing
WScript.Quit

Upvotes: 1

YowE3K
YowE3K

Reputation: 23984

If UsedRange is not returning a useful number, use one of the more reliable ways of finding the last used cell in a column:

Set xlApp=CreateObject("Excel.Application")
Set xlBook=xlApp.Workbooks.Open("filepath", 0, true)
xlApp.visible=true

Set xlSheet=xlBook.Worksheets("Proposal Spreadsheet")
row=xlSheet.Cells(xlSheet.Rows.Count, 9).End(-4162).Row ' -4162 is equivalent of "xlUp"

dim i 

For i=15 to row
    cell=xlSheet.Cells(i,3).Value
    celli=xlSheet.Cells(i,9).Value

    xlSheet.Cells(i,9).Value=celli & "(" & cell & ")"
Next

xlBook.Save
xlApp.Run "Submit_To_iDesk"
xlBook.Close

xlApp.Quit
Set xlApp=Nothing 
Set xlBook=Nothing 
WScript.Quit

Upvotes: 1

Related Questions