Samantha
still learning...
- Local time
- Today, 13:40
- Joined
- Jul 12, 2012
- Messages
- 180
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.
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