Excel 2013 Template Not Operational Since Upgrade (1 Viewer)

Samantha

Registered User.
Local time
Today, 16:31
Joined
Jul 12, 2012
Messages
142
Hi, Let me start by saying thank you for anyone reading in an attempt to help. I am by no means a pro at this, and have borrowed and changed all of the VBA in my DB. I have spent a few days searching for examples and clues as to what is going on and this is beyond me.

I am running Windows 7, and up until Monday had been using Access 2013 with Word, Excel, Outlook 2010. The DB is split and the BE is stored on our server while each employee has a front end on their hard drive. Everything at that point was fully operational. Certain forms with data export to a Word and Excel templates, and another function creates an outlook appointment and invites people.

Monday we went to Office 365 and all of my Office 2010 applications were uninstalled and IT reinstalled all 2013 programs. I should add I also had Access 2010 installed however was using 2013 version daily. My Word templates function correctly and it works with Outlook the same. My issue is Excel. I am receiving "error 9 subscript out of range". I did look this up with Microsoft and it mentions using the Copy method in which I am not. When I remove the error handling the code stops at "Set XL = CreateObject("Excel.Application")". Unfortunately I really know nothing about debugging. Funny thing was another user had the Excel template open on their computer (we are on a network drive) Excel generated that the file was in use, I selected to open a read only file and received no error message and all of my data was populated like it should be.

Code:
Private Sub cmdMergeXLbttn_Click()
On Error GoTo Error_Handler

'Declare a variable named MySheetPath as string
Dim MySheetPath As String

'set file path to actual sheet
MySheetPath = "O:\Access\ProjectSheet.xltx"

'Set up object variables to refer to Excel and Objects
Dim XL As Excel.Application
Dim XLBook As Excel.Workbook
Dim XLSheet As Excel.Worksheet
Dim strServiceAddress As String
Dim ctl As Control
Dim varItem As Variant
Dim BillingAddress As String
Dim AddyLineVar As String
Dim CompanyAddress As String
Dim SalutationVar As String
Dim CustCompany As String
Dim CustEmail As String
Dim CustCell As String

'Open an instance of Excel, open the workbook.
Set XL = CreateObject("Excel.Application")
Set XLBook = GetObject(MySheetPath)

'Make sure everything is visible
XL.Visible = True
XLBook.Windows(1).Visible = True

'Define top sheet in Workbook as XLSheet
Set XLSheet = XLBook.Worksheets(1)

'Set Company Address
CompanyAddress = [Company] & vbCrLf & ([sfrmContacts].[Form]![MailingAddress]) & vbCrLf & ([sfrmContacts].[Form]![City]) & ", " & ([sfrmContacts].[Form]![State]) & " " & ([sfrmContacts].[Form]![ZipCode])

'Start building AddyLineVar, by dealing with blank last name fields.
    If IsNull([sfrmContacts].[Form]![Last]) Then
        AddyLineVar = [Company]
        'Just set salutation to generic.
        SalutationVar = "Sir or Madam"
    Else
        AddyLineVar = ([sfrmContacts].[Form]![Title]) & " " & ([sfrmContacts].[Form]![First]) & " " & ([sfrmContacts].[Form]![Last])
        
        'Add Company on after name.
        If Not IsNull([Company]) Then
            AddyLineVar = AddyLineVar & vbCrLf & [Company]
        End If
        
        'Salutation will be customer's last name
        SalutationVar = ([sfrmContacts].[Form]![Title]) & " " & ([sfrmContacts].[Form]![Last]) & ", "
    End If
        
    'Add line break and Address lines.
    AddyLineVar = AddyLineVar & vbCrLf & ([sfrmContacts].[Form]![MailingAddress])
    'Tack on line break then city, state, and zip.
    AddyLineVar = AddyLineVar & vbCrLf & ([sfrmContacts].[Form]![City]) & ", "
    AddyLineVar = AddyLineVar & ([sfrmContacts].[Form]![State]) & " " & ([sfrmContacts].[Form]![ZipCode])
    CustCompany = ([sfrmContacts].[Form]![Title]) & " " & ([sfrmContacts].[Form]![First]) & " " & ([sfrmContacts].[Form]![Last]) & vbCrLf & [Company]
    
'Start building BillingAddress
    If IsNull([Billing Information].Form![BillToCompany]) Then
       BillingAddress = AddyLineVar
    Else
       BillingAddress = ([Billing Information].Form![BillToCompany])
       
       If Not IsNull([Billing Information].Form![BillCareOf]) Then
            BillingAddress = BillingAddress & vbCrLf & "c/o " & ([Billing Information].Form![BillCareOf])
       End If
       
       If Not IsNull([Billing Information].Form![BillBoxNumber]) Then
            BillingAddress = BillingAddress & vbCrLf & ([Billing Information].Form![BillBoxNumber])
       End If
       
       If Not IsNull([Billing Information].Form![BillAttn]) Then
            BillingAddress = BillingAddress & vbCrLf & "Attn: " & ([Billing Information].Form![BillAttn])
       End If
            
       'Add line break and Address lines.
       BillingAddress = BillingAddress & vbCrLf & ([Billing Information].Form![BillAddress])
       'Tack on line break then city, state, and zip.
       BillingAddress = BillingAddress & vbCrLf & ([Billing Information].Form![BillCity]) & ", "
       BillingAddress = BillingAddress & ([Billing Information].Form![State]) & " " & ([Billing Information].Form![BillingZip])
    End If

'Build CustEmail
        If IsNull([Billing Information].Form![BillEmailTo]) Then
            CustEmail = Nz([sfrmContacts].[Form]![E-mail address], "None Listed")
        Else: CustEmail = ([Billing Information].Form![BillEmailTo])
        End If
     
' String Customer Cell Phone
If Not IsNull([sfrmCellPhone].Form![Mobile Phone]) Then
    CustCell = [sfrmCellPhone].Form![Mobile Phone]
Else: CustCell = ""
End If

XLSheet.Range("DateGen") = [sfrmJobInformation].[Form]![DateAdded]
XLSheet.Range("ProjectNumber") = JobNumber
XLSheet.Range("CompanyAdd") = CompanyAddress
XLSheet.Range("Contact") = ([sfrmContacts].[Form]![First]) & " " & ([sfrmContacts].[Form]![Last])
XLSheet.Range("Phone") = ([sfrmContacts].[Form]![Phone])
XLSheet.Range("Fax") = ([sfrmContacts].[Form]![BusinessFax])
XLSheet.Range("ProjectDescription") = ProjectDescription
XLSheet.Range("ServiceAdd") = Me!ServiceAddress.Value 'returned only first selection when combo box is used, text box transfers whole string
XLSheet.Range("City") = City
XLSheet.Range("State") = State
XLSheet.Range("ProjectType") = ([sfrmProjectType].[Form]![ProjectType])
XLSheet.Range("PurchaseOrder") = PONumber
XLSheet.Range("OnsiteContact") = [OnsiteContact]
XLSheet.Range("BillTo") = BillingAddress
XLSheet.Range("CellPhone") = CustCell
XLSheet.Range("CustEmail") = CustEmail
XLSheet.Range("PM") = [Manager]

MsgBox "Your Project Information Sheet is Ready." & vbCrLf & "Please re-name your document and save to the job file.", vbOKOnly, "Successful"

Exit_Procedure:
Exit Sub
Error_Handler:
    MsgBox "An error has occurred in this application." & " Please contact your technical support with the following information:" & vbCrLf & vbCrLf & "Error Number" & " " & Err.Number & ", " & Err.Description, Buttons:=vbCritical
    
    Resume Exit_Procedure
End Sub
 

Minty

AWF VIP
Local time
Today, 21:31
Joined
Jul 26, 2013
Messages
8,018
It sounds like a references issue. If there is a chance you might not know what version of office is being used you should use late binding.
This will create the Excel object based on the office installation on the local machine rather than trying pre-loading a possibly missing reference on that machine. Something like

Code:
   Dim objXL As Object
    Dim xlWB As Object
    Dim xlWS As Object
   
    Set objXL = CreateObject("Excel.Application")
   etc...
 

Samantha

Registered User.
Local time
Today, 16:31
Joined
Jul 12, 2012
Messages
142
Minty, Thank you for your response. I attempted to look into it more today however other MS software is acting up putting this on the back burner. I will look Monday again and post back my results.

Thanks,
Samantha
 

Samantha

Registered User.
Local time
Today, 16:31
Joined
Jul 12, 2012
Messages
142
Good Morning,

Company wide we will run on 2013 - at least until Access 2016 is released in 365. I do have 15.0 object library referenced.

So after looking up some examples I changed the code to the following:
Code:
'Set up object variables to refer to Excel and Objects
Dim XL As Object 
Dim XLBook As Excel.Workbook
Dim XLSheet As Excel.Worksheet

'Open an instance of Excel, open the workbook.
Set XL = Excel.Application 
Set XLBook = GetObject(MySheetPath)

'Make sure everything is visible
XL.Visible = True
XLBook.Windows(1).Visible = True

'Define top sheet in Workbook as XLSheet
Set XLSheet = XLBook.Worksheets(1)


I am still receiving the same "error 9 subscript out of range" and the debugger is stopping at XLBook.Windows(1).Visible = True

Excel is opening the template although no worksheets are loading.
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 21:31
Joined
Sep 12, 2006
Messages
14,223
maybe it should be windows(0)
it might be the same for the xlsheet also.

try it, and see.
 

Samantha

Registered User.
Local time
Today, 16:31
Joined
Jul 12, 2012
Messages
142

Users who are viewing this thread

Top Bottom