Am new to VBA & trying to learn as I go. Am currently stuck with an Issue here which I need some help.
Am trying to open an Excel workbook(<code style="margin: 0px; padding: 1px 5px; border: 0px; font-size: 13px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, serif; white-space: pre-wrap; background-color: rgb(238, 238, 238);">Test.xlsx</code>) from the share point using Internet Explorer. Once the File is opened, I would like to close IE. Then copy few Sheets from the Workbook, copy/Move it to a new Workbook & close the original Workbook. I searched a lot in Google & came with below codes which works OK if I run it separately, but If I combine with my original program (which I manged to generate by recording Macros) it gives an Error message 'Run-time error'9': Subscript out of range'which when I debug will highlight my Error Handler Code <code style="margin: 0px; padding: 1px 5px; border: 0px; font-size: 13px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, serif; white-space: pre-wrap; background-color: rgb(238, 238, 238);">(Workbooks("Test.xlsx").Close SaveChanges:=False)</code>.
I really cant understand the mistake here. Can someone please help me with the correct codes here.
Basically my requirement is to open an Excel Workbook from Share point(<code style="margin: 0px; padding: 1px 5px; border: 0px; font-size: 13px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, serif; white-space: pre-wrap; background-color: rgb(238, 238, 238);">"URL/TEST.xlsx"</code>) using Internet Explorer(in a new window),close IE window & then to copy/move few sheets from the original Workbook(Test.xlsx) to new Workbook, close the original workbook(Test.xlsx) & make amendments in the new workbook(Book##).
Can someone please help me with the codes here.
Below are the codes I managed to get from Google/Internet.
<code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, serif; white-space: inherit;">Sub OpenIE()
Dim objIE As Object
Set objIE = CreateObject("InternetExplorer.Application")
objIE.Navigate "URL/TEST.xlsx"
objIE.Visible = True**
'Below are the codes I generated by recording Macros & adding few codes which managed to get from Internet/GoogleOn Error
GoTo My_Error_Handler
Windows("Test.xlsx").Visible = True
On Error GoTo My_Error_Handler
Sheets(Array("Stats", "Mtx")).Select
Sheets("Mtx").Activate
Sheets(Array("Stats", "Mtx")).Copy
Sheets("Mtx").Select
'
'
'
'
' so on
'
'
My_Error_Handler:
Workbooks("Test.xlsx").Close SaveChanges:=False
ActiveWorkbook.Close SaveChanges:=False
MsgBox "Error: (" & Err.Number & ") " & Err.Description, vbCritical
End Sub
Thanks</code>
Am trying to open an Excel workbook(<code style="margin: 0px; padding: 1px 5px; border: 0px; font-size: 13px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, serif; white-space: pre-wrap; background-color: rgb(238, 238, 238);">Test.xlsx</code>) from the share point using Internet Explorer. Once the File is opened, I would like to close IE. Then copy few Sheets from the Workbook, copy/Move it to a new Workbook & close the original Workbook. I searched a lot in Google & came with below codes which works OK if I run it separately, but If I combine with my original program (which I manged to generate by recording Macros) it gives an Error message 'Run-time error'9': Subscript out of range'which when I debug will highlight my Error Handler Code <code style="margin: 0px; padding: 1px 5px; border: 0px; font-size: 13px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, serif; white-space: pre-wrap; background-color: rgb(238, 238, 238);">(Workbooks("Test.xlsx").Close SaveChanges:=False)</code>.
I really cant understand the mistake here. Can someone please help me with the correct codes here.
Basically my requirement is to open an Excel Workbook from Share point(<code style="margin: 0px; padding: 1px 5px; border: 0px; font-size: 13px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, serif; white-space: pre-wrap; background-color: rgb(238, 238, 238);">"URL/TEST.xlsx"</code>) using Internet Explorer(in a new window),close IE window & then to copy/move few sheets from the original Workbook(Test.xlsx) to new Workbook, close the original workbook(Test.xlsx) & make amendments in the new workbook(Book##).
Can someone please help me with the codes here.
Below are the codes I managed to get from Google/Internet.
<code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, serif; white-space: inherit;">Sub OpenIE()
Dim objIE As Object
Set objIE = CreateObject("InternetExplorer.Application")
objIE.Navigate "URL/TEST.xlsx"
objIE.Visible = True**
'Below are the codes I generated by recording Macros & adding few codes which managed to get from Internet/GoogleOn Error
GoTo My_Error_Handler
Windows("Test.xlsx").Visible = True
On Error GoTo My_Error_Handler
Sheets(Array("Stats", "Mtx")).Select
Sheets("Mtx").Activate
Sheets(Array("Stats", "Mtx")).Copy
Sheets("Mtx").Select
'
'
'
'
' so on
'
'
My_Error_Handler:
Workbooks("Test.xlsx").Close SaveChanges:=False
ActiveWorkbook.Close SaveChanges:=False
MsgBox "Error: (" & Err.Number & ") " & Err.Description, vbCritical
End Sub
Thanks</code>
Last edited:
