Hi All,
I am new to VBA and Excel Programming. I encountered the 'Run Time Error '9' - Subscript Out of Range' error while i tried to load the data. This is existing code. This code was written in Year 2010, I am not sure recent upgrade may causing this error.
code is
Thanks for you help.Private Sub Command32_Click() Dim try As Integer If Dir(CurrentProject.Path & "\InputData.xls") <> "" Then 'MsgBox ("Import Process") try = 1 Else 'MsgBox ("File Does Not exist ") Dim MyXL As Object 'Excel Application Object Dim XL_File As String Dim SheetName As String 'Dim xlBook As Excel.Workbook XL_File = CurrentProject.Path & "\InputData.xls" 'SheetName = "New Sheet Name" 'Create the Excel Application Object. Set MyXL = New Excel.Application 'Set MyXL = CreateObject("Excel.Application") 'Create new Excel Workbook 'Create the Excel Workbook Object, and open existing Excel Workbook ' Set xlBook = xlApp.Workbooks.Open(XL_File) MyXL.Workbooks.Add MyXL.Application.DisplayAlerts = False MyXL.Application.Visible = False MyXL.Worksheets(1).Name = "Testout" MyXL.Worksheets(2).Name = "EchimpInventory" ---------------------&&&&&&&&&&&&&&&&&&&&---------- > It is pointing to this line when I tried to debug. MyXL.Worksheets(3).Name = "abendcode" MyXL.Worksheets.Add(After:=MyXL.Worksheets(3)).Name = "jobabend" MyXL.Worksheets.Add(After:=MyXL.Worksheets(4)).Name = "SCHEDULEDURATION" MyXL.Worksheets.Add(After:=MyXL.Worksheets(5)).Name = "jobschedule" MyXL.Worksheets.Add(After:=MyXL.Worksheets(6)).Name = "Echimpformatted" MyXL.Worksheets.Add(After:=MyXL.Worksheets(7)).Name = "Jobschedtmp" ' Show the Excel *** in Excel Window. 'MyXL.Application.Visible = False 'Save the Excel File 'MyXL.Worksheets(7).SaveAs (XL_File) MyXL.Workbook.SaveAs (XL_File) 'Close the Workbook or else XL_File will still be open and available for read Only! 'Or MyXL.Quit could be used instead 'MyXL.Workbooks(1).Close 'Close the Excel Window and / or Application in background 'or else XL_File will still be open and available for read Only! MyXL.Quit Set MyXL = Nothing
Bookmarks