Run-Time Error '9': Subscript Out of Range when generating spreadsheet

Question

I am currently trying to use an access database which uses vba to generate Excel spreadsheets and AutoCAD drawings; I didn't write the code, and I don't have experience coding in this language. When generating an excel file, the code gets to the line MyXL.Parent.Windows(1).Visible = True, it gives the error. The excel file is generated, but is identical to the template.

The File and directory names are placeholders

Dim MyXL As Object

    FileCopy "\Directory\Template", "\Directory\Filename"
    ' This copies an Excel file, first half, then renames it with the Sales order number
    Set MyXL = GetObject("\Directory\Template")
    ' This opens the Excel file named in the upper code second half
    MyXL.Application.Visible = True
    MyXL.Application.WindowState = 3
          '  MyXL1.Activate
    MyXL.Parent.Windows(1).Visible = True
    MyXL.Parent.ActiveWindow.WindowState = 2

    With MyXL.Worksheets(1)

    End With

At this point it sets a lot of values (I assume) in the form .Range("T60").Value = Me![Text516]

   MyXL.Worksheets(1).Activate
    MyXL.Save
MyXL.Parent.Quit ' This is what you have to do to close the Application
'MyXL.Parent.Quit
   ' MyXL.Parent.ActiveWindow.WindowState = xlMinimized
'    MyXL.Close

The possible duplicate relates to copying an excel spreadsheet, however this problem goes further than that

Edit: I made a mistake and previously had the line Set MyXL = GetObject("\SameDirectory\SameFilename") but it is actually Set MyXL = GetObject("\Directory\Template")

The file will be identical to the template. Do you mean the file name extension is xlst instead of xlsx? Just copying/renaming a file does not require opening Excel objects. — June7, Aug 13 '19 at 07:18
Possible duplicate of [Copying and renaming unopened workbook in excel](https://stackoverflow.com/questions/9182365/copying-and-renaming-unopened-workbook-in-excel) — June7, Aug 13 '19 at 07:18
There is more to the code, rather than it just being a copy of the excel template spreadsheet; following the code in the question, values from access are sent to the new excel file — , Aug 13 '19 at 07:30
Another approach is to open the Excel template workbook (xlst file) as object in VBA, VBA does desired edits then SaveAs to new file name. https://stackoverflow.com/questions/9165640/creating-a-new-excel-file-based-on-a-template-in-vba. I have never seen `.Parent` reference in manipulating Excel objects. — June7, Aug 13 '19 at 07:32
The error on that line indicates to me that there is no workbook open in the Excel application interface. Do you actually *see* the workbook? The code, as it stands in the question, will not *open* a file. `GetObject` picks up the workbook file *if it's already open*. Otherwise an error will be generated (which may be masked by code you're not showing us). — Cindy Meister, Aug 13 '19 at 10:25
Excel opens but the actual file doesn't open, even though it does exist - Please see edit for more code — , Aug 13 '19 at 10:37

June7 · Answer 1 · 2019-08-13T12:21:26.093

Example of working code to open an Excel workbook, edit, save to a new name.

Sub CopyExcel()
Dim xl As Excel.Application, xlw As Excel.Workbook
Set xl = CreateObject("Excel.Application")
'the following two lines have same result
Set xlw = xl.Workbooks.Open("C:\Users\June\MyStuff\Condos.xlsx", , True)
'Set xlw1 = xl.Workbooks.Add("C:\Users\June\MyStuff\Condos.xlsx")
'code to edit 
xlw.SaveAs "C:\Users\June\MyStuff\Condos2.xlsx"
xl.Quit
End Sub

Run-Time Error '9': Subscript Out of Range when generating spreadsheet

1 Answers1