Results 1 to 4 of 4
  1. #1
    Join Date
    May 2022
    Posts
    11

    DoCmd.OutputTo method will not work with variable file path once DB is move to Sharepoint

    I've completed a DB recently on my local work PC, however my manager wants me to migrate the DB onto SharePoint.


    He wants me to do this so that the DB can be accessed on any machine which has access to the internet.

    However, for creating reports my DB uses the DoCmd.OutputTo method, to automatically name my excel files.
    I've done this as such:

    DoCmd.OutputTo acOutputQuery, Report1, acFormatXLSX, Report1Name, True

    Where:
    1. Report1 is:
    Code:
    Report1 = "unionReport_TestLot_AllSw"
    2. Report1Name is:
    Code:
    Report1Name = FileLocation & Report1Desc & ".xlsx"
    3. FileLocation is:
    Code:
    FileLocation = "C:\Users\John.Doe\Generic Company\Desktop\REPORTS\"
    4. Report1Desc is:
    Code:
    Report1Desc = "[Test Lot Report] " & CurrentTestLot & " " & CurrentConfig & " " & CurrentAssetType & " " & editedDate


    My sharepoint location would be
    C:\Users\John.Doe\Generic Company\Engineering\Deliverables

    But if I change the FileLocation from
    C:\Users\John.Doe\Generic Company\Desktop\REPORTS\
    to
    C:\Users\John.Doe\Generic Company\Engineering\Deliverables

    That surely wouldn't allow it won't work on another PC.
    I'm not sure what is the best approach to this situation.

  2. #2
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    You could have the user pick the save to folder.


    This is how I export a query to Excel
    (Note: I haven't used this code for a long while )


    I have a reference set to "Microsoft Office 16.0 Object Library" (Office 365)

    In a standard Module I named "modBrowseFolder" are 2 functions
    Code:
    Function BrowseFolderExplorer(Optional DialogTitle As String, _
        Optional ViewType As MsoFileDialogView = _
            MsoFileDialogView.msoFileDialogViewdetails, _
        Optional InitialDirectory As String) As String
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' BrowseFolderExplorer
    ' This provides an Explorer-like Folder Open dialog.
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
        Dim fDialog  As Office.FileDialog
        Dim varFile As Variant
        Set fDialog = Application.FileDialog(msoFileDialogFolderPicker)
        fDialog.InitialView = ViewType
        With fDialog
            If Dir(InitialDirectory, vbDirectory) <> vbNullString Then
                .InitialFileName = InitialDirectory
            Else
                .InitialFileName = CurDir
            End If
            .Title = DialogTitle
            
            If .Show = True Then
                ' user picked a folder
                BrowseFolderExplorer = .SelectedItems(1)
            Else
                ' user cancelled
                BrowseFolderExplorer = vbNullString
            End If
        End With
    End Function
    
    
    Public Function UnqualifyPath(sPath As String) As String
    
       'Qualifying a path involves assuring that its format
       'is valid, including a trailing slash, ready for a
       'filename. Since SHBrowseForFolder will not pre-select
       'the path if it contains the trailing slash, it must be
       'removed, hence 'unqualifying' the path.
       If Len(sPath) > 0 Then
          If Right$(sPath, 1) = "\" Then
             UnqualifyPath = Left$(sPath, Len(sPath) - 1)
             Exit Function
          End If
       End If
    
       UnqualifyPath = sPath
    
    End Function


    The code to get the folder path is called like this:
    (Of course this is the click event of a button on a form)

    Code:
    Private Sub cmdExport_Click()
        Dim sPath As String
        Dim strSaveFileName As String
    
        '   Me.Text1.Value = CurrentProject.Path
        'The call can not have a trailing slash, so
        'strip it from the path if present
        sPath = UnqualifyPath((CurrentProject.Path))   '<<-- initial folder path
        sPath = BrowseFolderExplorer("Select a Folder", msoFileDialogViewPreview, sPath)
    
        strSaveFileName = sPath & "\MedPremium for Year_" & Month(Me.dteStartDate) & "_" & Year(Me.dteStartDate) & "_to_" & Month(Me.dteEndDate) & "_" & Year(Me.dteEndDate) & ".xls"
         
        DoCmd.OutputTo acOutputQuery, "EQ_MedPremForYear", acFormatXLS, strSaveFileName, False
    
        MsgBox "Done!!" & vbCrLf & vbCrLf & "The file was saved as:  " & strSaveFileName
    
    End Sub

  3. #3
    Join Date
    May 2022
    Posts
    11
    Thanks for the reply Steve.
    I've copied your code into my project and set about testing whether it will work for me.
    My plan is to use my file naming system - which I've already developed - with your user picks a folder code.

    I've set up the two functions, and I've also created a new button for cmdExport_Click, however upon loading the form I get an error.

    "The expression On Load you entered as the event property setting produced the following error: User-defined type not defined."

    If I comment out the BrowseFolderExplorer function then the error is no longer there, I'm not sure what the issue is to be honest.

    EDIT: Turns out I forgot to add reference for
    "Microsoft Office 16.0 Object Library".

    EDIT2:

    Alright, I've got it working, sort of. There are some issues. Let me describe what's happening.

    1. I press the create report button.
    2. "Select a Folder" dialog box pop's up. Current folder (where the project is saved) is already automatically filled in. For me this is "WIP" since my directory is
    C:\Users\John.Doe\OneDrive - Company\Desktop\WIP
    3. I press "OK" to save.
    4. I get an error message pop-up. It says
    "Select a Folder
    WIP
    Path does not exist.
    Check the path and try again."
    5. I press OK to dismiss the popup.
    6. I use the explorer to manually travel to WIP folder.
    7. I press OK to save
    8. I get a message saying:
    "Microsoft Access
    Done!!
    The fine was saved as: C:\Users\John.Doe\OneDrive - Company\Desktop\WIP[Test Lot Report] AOT - Lot 2 20220805_103917.xls"
    9. I press OK to dismiss the popup message
    10. I check the WIP folder, and there is no report saved there.
    11. I check the desktop and the report is there named "WIP[Test Lot Report] AOT - Lot 2 20220805_103917.xls"
    It seems that instead of assigning the file to WIP folder, it set that as the first part of the file name instead.

    So the two issues here are that
    A) It's not letting me save until I refresh the file path, even though it's entered by default.
    B) It's saving the file to the parent folder, of the one selected.
    C) It's appending the selected folder "WIP" to the start of the file name.

    Any insight or suggestions on how to solve this?

    Here is how I have utilized the code, perhaps it will offer some insight, perhaps not.


    Code:
    Function BrowseFolderExplorer(Optional DialogTitle As String, _
        Optional ViewType As MsoFileDialogView = _
            MsoFileDialogView.msoFileDialogViewdetails, _
        Optional InitialDirectory As String) As String
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' BrowseFolderExplorer
    ' This provides an Explorer-like Folder Open dialog.
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
        Dim fDialog  As Office.FileDialog
        Dim varFile As Variant
        Set fDialog = Application.FileDialog(msoFileDialogFolderPicker)
        fDialog.InitialView = ViewType
        With fDialog
            If Dir(InitialDirectory, vbDirectory) <> vbNullString Then
                .InitialFileName = InitialDirectory
            Else
                .InitialFileName = CurDir
            End If
            .Title = DialogTitle
            
            If .Show = True Then
                ' user picked a folder
                BrowseFolderExplorer = .SelectedItems(1)
            Else
                ' user cancelled
                BrowseFolderExplorer = vbNullString
            End If
        End With
    End Function
    
    
    
    
    Public Function UnqualifyPath(sPath As String) As String
    
    
       'Qualifying a path involves assuring that its format
       'is valid, including a trailing slash, ready for a
       'filename. Since SHBrowseForFolder will not pre-select
       'the path if it contains the trailing slash, it must be
       'removed, hence 'unqualifying' the path.
       If Len(sPath) > 0 Then
          If Right$(sPath, 1) = "\" Then
             UnqualifyPath = Left$(sPath, Len(sPath) - 1)
             Exit Function
          End If
       End If
    
    
       UnqualifyPath = sPath
    
    
    End Function
    
    
    Private Sub Command143_Click()
        Dim sPath As String
        Dim strSaveFileName As String
    
    
        '   Me.Text1.Value = CurrentProject.Path
        'The call can not have a trailing slash, so
        'strip it from the path if present
        sPath = UnqualifyPath((CurrentProject.Path))   '<<-- initial folder path
        sPath = BrowseFolderExplorer("Select a Folder", msoFileDialogViewPreview, sPath)
    
    
    
    
    
    
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    'Report 1 hold the name of the query, used for assigning data source to the subforms - DC 1 June 2022
    Dim Report1 As String
    Report1 = "unionReport_TestLot_AllSw"
    
    
    'Report1Desc holds the description aka the name of the file once the excel file is published
    'here you want to add stuff like, ALL, Current,  Non-Current, Date, SW Type, Parent system to make each excel file uniqely named and self descriptive
    'DC 2 June 2022
    Dim Report1Desc As String
    
    
    'Report1Name is what you use in the button code to export the specific query to a location and assign it a name - DC 2 June 2022
    'It is a concoctination of both File Location AND Report (1-4) description
    Dim Report1Name As String
    
    
    Dim FileLocation As String 'This is the SAVE LOCATION, it has to be defined in code if you want the reports to automatically be named via code - DC 2 June 2022
    FileLocation = "C:\Users\John.Doe\OneDrive - Company\Desktop\REPORTS\"
    
    
    'Here the code assigns multiple variables to the ReportDesc, which is the name of the excel file
    '------------------------------------------------------------------------------- START
    Dim editedDate As String 'This assigns todays date to the end of the filename
    editedDate = Format$(Now(), "yyyymmdd_hhnnss")
    
    
    Dim CurrentTestLot As String 'This assigns the name of the spec to the filename
    CurrentTestLot = Me.ComboL1.Column(1)
    
    
    Dim CurrentConfig As String 'this assigns All/Current/Non-Current to the filename, based on checkbox selection
    If Me.OptionFrame = 3 Then
        CurrentConfig = "Current AND Non-Current"
    Else
        If Me.OptionFrame = 2 Then
            CurrentConfig = "Non-Current"
        Else
            CurrentConfig = "Current"
        End If
    End If
    
    
    Dim CurrentAssetType As String 'This assigns SW Asset type to the filename (Such as SW, DC, EC, VM or ALL)
    CurrentAssetType = "ALL-Software"
    
    
    Report1Desc = "[Test Lot Report] " & CurrentTestLot & " " & CurrentConfig & " " & CurrentAssetType & " " & editedDate
    Report1Name = FileLocation & Report1Desc & ".xlsx"
    '------------------------------------------------------------------------------- END
    
    
    'This is the code which selects the query, and exports it as an excel file. It actiates when you press the button
    'This code also names the file, and assigns the file to a specific, predefined location - DC 3 June 2022
    Dim selectedReport As String
    
    
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    
    
        strSaveFileName = sPath & Report1Desc & ".xls"
         
        DoCmd.OutputTo acOutputQuery, Report1, acFormatXLS, strSaveFileName, False
        'DoCmd.OutputTo acOutputQuery, Report1, acFormatXLS, Report1Name, True
    
    
        MsgBox "Done!!" & vbCrLf & vbCrLf & "The file was saved as:  " & strSaveFileName
    End Sub
    EDIT3:

    I Changed

    Code:
    strSaveFileName = sPath & Report1Desc & ".xls"
    to

    Code:
    strSaveFileName = sPath & "\" & Report1Desc & ".xls"
    Now the file saves in the correct selected folder, rather than the parent.
    It seems a trailing slash was important in getting the file to save in the correct folder.
    Problem B) and C) are solved.

  4. #4
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,819
    A 'path' should always have a trailing slash?, else the filename will just get appended to the name of the lowest folder? and become a strangely named file in the next folder up in the structure.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

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

Similar Threads

  1. File path to user's OneDrive from SharePoint
    By SparkedIntoLife in forum Access
    Replies: 1
    Last Post: 07-27-2022, 01:58 PM
  2. Replies: 4
    Last Post: 03-07-2019, 01:27 PM
  3. Replies: 1
    Last Post: 07-30-2015, 12:56 PM
  4. Replies: 5
    Last Post: 06-29-2015, 11:30 AM
  5. Replies: 5
    Last Post: 09-05-2014, 12:06 PM

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