Results 1 to 4 of 4
  1. #1
    MaxQTime is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Apr 2019
    Location
    Kelowna, B.C.
    Posts
    42

    Opening Excel On Network Server

    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

  2. #2
    GinaWhipp's Avatar
    GinaWhipp is offline Competent Performer
    Windows 7 64bit Access 2013 32bit
    Join Date
    Jul 2011
    Location
    Ohio, USA
    Posts
    377
    Are you sure that is a period in the Server path and not an underscore? If you are I would copy the path directly from Windows Explorer to make sure you got the complete path. I alos didn't spot a file extension. Just because it's hidden doesn't mean you line doesn't need it.

  3. #3
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    if the database and the excel file are in the same folder use


    Code:
    fullPath = replace(currentproject.path & "\", "\\", "\") &  "Dealer Work Order Sheet 2019.xlsx"
    Assuming your file is named Dealer Work Order Sheet 2019.xlsx

  4. #4
    MaxQTime is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Apr 2019
    Location
    Kelowna, B.C.
    Posts
    42
    That is amazing, I never even thought about the file extension as it was working without while on my desktop. It works great now, thank you.

    Also being able to gt the full path of your project will come in very useful

    Thank you everyone.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Opening Files from a network drive
    By hinchi1 in forum Access
    Replies: 5
    Last Post: 06-10-2018, 04:04 AM
  2. Opening a Network Folder Path
    By newbie_204 in forum Programming
    Replies: 3
    Last Post: 10-17-2017, 11:41 AM
  3. Replies: 2
    Last Post: 07-29-2014, 06:10 PM
  4. Replies: 3
    Last Post: 05-21-2013, 05:48 AM
  5. Replies: 2
    Last Post: 08-23-2012, 08:39 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums