Hi everyone, I've added some VBA code to my database to open a specific template we use and fill in some of the information from the database. This works absolutely perfect when the files are on my desktop, however I'm having trouble trying to get the proper path for it to work on the server. As other people will need to use this function, this is pretty important. I've spoken with our It team and gotten the full UNC address for the file, this is still not working. The excel file is literally right next to the access file in a folder on the network drive.
Does anyone have any ideas on how I can make this work? I've changed some of the file path for obvious reasons.
Code:
Private Sub CreateServiceBtn_Click()
Dim oXL As Object
Dim oXLbook As Object
Dim fullPath As String
Dim JobNumber As String
Dim Dealer As String
Dim contact As String
Dim phone As String
Dim tag As String
Dim Street As String
Dim CityProv As String
Dim postal As String
JobNumber = Me.ServiceNumber.Value
Dealer = Me.Parent.Dealer.Value
Street = Me.Parent.Street.Value
CityProv = Me.Parent.City.Value & ", " & Me.Parent.Province.Value
postal = Me.Parent.[Postal Code].Value
phone = CStr(Me.Parent.[contact 1 phone].Value)
contact = Me.Parent.[Contact 1 First Name].Value & " " & Me.Parent.[Contact 1 Last Name].Value
tag = Me.Parent.TagName.Value
Set oXL = CreateObject("excel.application")
fullPath = "\\Server.business.local\Folder1\Dealer Documents\Dealer Work Order Sheet 2019"
oXL.Application.Visible = True
Set oXLbook = oXL.workbooks.Open(fullPath)
oXLbook.activesheet.range("m1") = JobNumber
oXLbook.activesheet.range("a2") = Dealer
oXLbook.activesheet.range("a10") = Street
oXLbook.activesheet.range("a11") = CityProv
oXLbook.activesheet.range("a12") = postal
oXLbook.activesheet.range("b4") = contact
oXLbook.activesheet.range("b5") = phone
oXLbook.activesheet.range("a7") = tag
oXL.getsaveasfilename
End Sub