[AccessD] New Computer Gives Error

Hi Arthur,

This might help.  I got this code from somewhere long ago and it works well.
This will cause the user to select a folder, which will give you the full
path to that folder, which you can then use to save a file.

Good Luck!
Dan 

'----------------

Private CallingProcedure()

    Dim stgFullPath

    stgFullPath = GetFolder

    '-- Now you can save a spreadsheet to the user-selected folder using
DoCmd.TransferSpreadsheet

End Sub

'----------------
Public Declare Function SHBrowseForFolder Lib "shell32.dll" Alias
"SHBrowseForFolderA" (lpBrowseInfo As BROWSEINFO) As Long

Public Declare Function SHGetPathFromIDList Lib "shell32.dll" Alias
"SHGetPathFromIDListA" (ByVal pidl As Long, ByVal pszPath As String) As Long
    
Public Declare Sub CoTaskMemFree Lib "ole32.dll" (ByVal pv As Long)

Public Type BROWSEINFO    'BI
    hOwner As Long
    pidlRoot As Long
    pszDisplayName As String
    lpszTitle As String
    ulFlags As Long
    lpfn As Long
    lParam As Long
    iImage As Long
End Type

Public Function GetFolder() As String

    Dim lngPidl As Long
    Dim BI As BROWSEINFO
    Dim stgPath As String
    Dim intPos As Integer
    Dim stgGetFolder As String
    
    '-- Fill BROWSEINFO structure data
    With BI
        .hOwner = 0
        .pidlRoot = 0
        .lpszTitle = "Browsing"
        .ulFlags = 1
        .pszDisplayName = Space$(260)
    End With
    
    '-- show dialog returning pidl to selected item
    lngPidl = SHBrowseForFolder(BI)
    
    '-- if pidl is valid, parse & return the user's selection
    stgPath = Space$(260)
    
    If SHGetPathFromIDList(ByVal lngPidl, ByVal stgPath) Then
    
        '-- SHGetPathFromIDList returns the absolute path to the selected
item. No path is returned for virtual folders.
        intPos = InStr(stgPath, Chr$(0))
        If intPos Then stgGetFolder = Left(stgPath, intPos - 1)
    
        '-- If a drive is selected then '\' is included in the string, but
if a folder is selected then '\' is not included.
        If InStrRev(stgGetFolder, "\") <> Len(stgGetFolder) Then
            stgGetFolder = stgGetFolder & "\"
        End If
    
    Else
    
        stgGetFolder = ""
        
    End If
    
    GetFolder = stgGetFolder
    
    '-- free the pidl
    Call CoTaskMemFree(lngPidl)
   
End Function
'--------------------

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Arthur Fuller
Sent: Saturday, June 09, 2012 7:00 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] New Computer Gives Error

Stuart, you are clearly a smart guy and I am not, so please forgive my
stupid question. Here is what I am trying to, so far without success.
Within Access, I open a report (could be in Excel or Word) and then I want
to bring up the FileSaveAs dialog. I have tried about 108 variations on the
code, and still no joy. You are the sharp knife in the drawer, please tell
me how to do this. Here's the scenario. I create an Excel object and add
stuff to it, all piped from Access. That part is cool, works perfectly. But
then I need to offer the user the chance to save said XLS file to anywhere
she wishes. I am flummoxed. I've looked at various snips from ADH etc.
without enlightenment. If you can tell me how to do this, I would be most
grateful.

A.
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com