' This goes before Sub Main()
' --------------------------------------------------------
Public Declare Function GetOpenFileName Lib "comdlg32.dll" Alias "GetOpenFileNameA" (pOpenfilename As OPENFILENAME) As Long
' This is the code behind the button
' --------------------------------------------------------
Private Sub cmdGetInputFile_Click()
Dim OpenFile As OPENFILENAME
Dim lReturn As Long
Dim sFilter As String
OpenFile.lStructSize = Len(OpenFile)
sFilter = "GeoGlobal Excel files (*.xlsx)" & Chr(0) & "*.xlsx" & Chr(0)
OpenFile.lpstrFilter = sFilter
OpenFile.nFilterIndex = 1
OpenFile.lpstrFile = String(257, 0)
OpenFile.nMaxFile = Len(OpenFile.lpstrFile) - 1
OpenFile.lpstrFileTitle = OpenFile.lpstrFile
OpenFile.nMaxFileTitle = OpenFile.nMaxFile
OpenFile.lpstrInitialDir = fso.GetParentFolderName(Me.txtInputFileName)
OpenFile.lpstrTitle = "Select the Yardi spreadsheet"
OpenFile.flags = 0
lReturn = GetOpenFileName(OpenFile)
If lReturn = 0 Then
' MsgBox "The User pressed the Cancel Button"
Else
Me.txtInputFileName = OpenFile.lpstrFile
SaveSetting "GGETimeCard", "Saved settings", "GGETimeCardFile", Me.txtInputFileName
End If
End Sub
' This and below goes in its own Module
' --------------------------------------------------------
Type OPENFILENAME
lStructSize As Long
hWndOwner As Long
hInstance As Long
lpstrFilter As String
lpstrCustomFilter As String
nMaxCustFilter As Long
nFilterIndex As Long
lpstrFile As String
nMaxFile As Long
lpstrFileTitle As String
nMaxFileTitle As Long
lpstrInitialDir As String
lpstrTitle As String
flags As Long
nFileOffset As Integer
nFileExtension As Integer
lpstrDefExt As String
lCustData As Long
lpfnHook As Long
lpTemplateName As String
End Type
Type SHITEMID ' mkid
cb As Long ' Size of the ID (including cb itself)
abID() As Byte ' The item ID (variable length)
End Type
Type ITEMIDLIST ' idl
mkid As SHITEMID
End Type
Declare Function SHGetPathFromIDList Lib "shell32.dll" Alias "SHGetPathFromIDListA" (ByVal pidl As Long, ByVal pszPath As String) As Long
Public Const NOERROR = 0
Public Const CSIDL_DESKTOP = &H0
Public Const CSIDL_PROGRAMS = &H2
Public Const CSIDL_CONTROLS = &H3
Public Const CSIDL_PRINTERS = &H4
Public Const CSIDL_PERSONAL = &H5 ' (Documents folder)
Public Const CSIDL_FAVORITES = &H6
Public Const CSIDL_STARTUP = &H7
Public Const CSIDL_RECENT = &H8 ' (Recent folder)
Public Const CSIDL_SENDTO = &H9
' Recycle bin, file system directory containing file objects in the user's recycle bin.
' The location of this directory is not in the registry; it is marked with the hidden and
' system attributes to prevent the user from moving or deleting it.
Public Const CSIDL_BITBUCKET = &HA
' File system directory containing Start menu items.
Public Const CSIDL_STARTMENU = &HB
' File system directory used to physically store file objects on the desktop
' (not to be confused with the desktop folder itself).
Public Const CSIDL_DESKTOPDIRECTORY = &H10
' My Computer, virtual folder containing everything on the local computer: storage
' devices, printers, and Control Panel. The folder may also contain mapped network drives.
Public Const CSIDL_DRIVES = &H11
' Network Neighborhood, virtual folder representing the top level of the network hierarchy.
Public Const CSIDL_NETWORK = &H12
' File system directory containing objects that appear in the network neighborhood.
Public Const CSIDL_NETHOOD = &H13
' Virtual folder containing fonts.
Public Const CSIDL_FONTS = &H14
' File system directory that serves as a common repository for document templates.
Public Const CSIDL_TEMPLATES = &H15 ' (ShellNew folder)
'========================================================
' Frees memory allocated by SHBrowseForFolder()
Declare Sub CoTaskMemFree Lib "ole32.dll" (ByVal pv As Long)
' Displays a dialog box that enables the user to select a shell folder.
' Returns a pointer to an item identifier list that specifies the location
' of the selected folder relative to the root of the name space. If the user
' chooses the Cancel button in the dialog box, the return value is NULL.
'Declare Function SHBrowseForFolder Lib "shell32.dll" Alias "SHBrowseForFolderA" (lpBrowseInfo As BrowseInfo) As Long ' ITEMIDLIST
' Contains parameters for the the SHBrowseForFolder function and receives
' information about the folder selected by the user.
Public Type BrowseInfoX ' bi
' Handle of the owner window for the dialog box.
hOwner As Long
' Pointer to an item identifier list (an ITEMIDLIST structure) specifying the location
' of the "root" folder to browse from. Only the specified folder and its subfolders
' appear in the dialog box. This member can be NULL, and in that case, the
' name space root (the desktop folder) is used.
pIDLRoot As Long
' Pointer to a buffer that receives the display name of the folder selected by the
' user. The size of this buffer is assumed to be MAX_PATH bytes.
pszDisplayName As String
' Pointer to a null-terminated string that is displayed above the tree view control
' in the dialog box. This string can be used to specify instructions to the user.
lpszTitle As String
' Value specifying the types of folders to be listed in the dialog box as well as
' other options. This member can include zero or more of the following values below.
ulFlags As Long
' Address an application-defined function that the dialog box calls when events
' occur. For more information, see the description of the BrowseCallbackProc
' function. This member can be NULL.
lpfn As Long
' Application-defined value that the dialog box passes to the callback function
' (if one is specified).
lParam As Long
' Variable that receives the image associated with the selected folder. The image
' is specified as an index to the system image list.
iImage As Long
End Type
' BROWSEINFO ulFlags values:
' Value specifying the types of folders to be listed in the dialog box as well as
' other options. This member can include zero or more of the following values:
' Only returns file system directories. If the user selects folders
' that are not part of the file system, the OK button is grayed.
Public Const BIF_RETURNONLYFSDIRS = &H1
' Does not include network folders below the domain level in the tree view control.
' For starting the Find Computer
Public Const BIF_DONTGOBELOWDOMAIN = &H2
' Includes a status area in the dialog box. The callback function can set
' the status text by sending messages to the dialog box.
Public Const BIF_STATUSTEXT = &H4
' Only returns file system ancestors. If the user selects anything other
' than a file system ancestor, the OK button is grayed.
Public Const BIF_RETURNFSANCESTORS = &H8
' Only returns computers. If the user selects anything other
' than a computer, the OK button is grayed.
Public Const BIF_BROWSEFORCOMPUTER = &H1000
' Only returns (network) printers. If the user selects anything other
' than a printer, the OK button is grayed.
Public Const BIF_BROWSEFORPRINTER = &H2000
Declare Function DrawIcon Lib "user32" (ByVal hdc As Long, _
ByVal x As Long, _
ByVal Y As Long, _
ByVal hIcon As Long) As Boolean
Declare Function DrawIconEx Lib "user32" (ByVal hdc As Long, _
ByVal xLeft As Long, _
ByVal yTop As Long, _
ByVal hIcon As Long, _
ByVal cxWidth As Long, _
ByVal cyWidth As Long, _
ByVal istepIfAniCur As Long, _
ByVal hbrFlickerFreeDraw As Long, _
ByVal diFlags As Long) As Boolean
' DrawIconEx() diFlags values:
Public Const DI_MASK = &H1
Public Const DI_IMAGE = &H2
Public Const DI_NORMAL = &H3
Public Const DI_COMPAT = &H4
Public Const DI_DEFAULTSIZE = &H8
Declare Function SHGetFileInfo Lib "Shell32" Alias "SHGetFileInfoA" _
(ByVal pszPath As Any, _
ByVal dwFileAttributes As Long, _
psfi As SHFILEINFO, _
ByVal cbFileInfo As Long, _
ByVal uFlags As Long) As Long
' pszPath:
' Pointer to a buffer that contains the path and filename. Both absolute and
' relative paths are valid. If uFlags includes the SHGFI_PIDL, value pszPath
' must be the address of an ITEMIDLIST structure that contains the list of
' item identifiers that uniquely identifies the file within the shell's name space.
' This string can use either short (the 8.3 form) or long filenames.
' dwFileAttributes:
' Array of file attribute flags (FILE_ATTRIBUTE_ values). If uFlags does not
' include the SHGFI_USEFILEATTRIBUTES value, this parameter is ignored.
Public Const FILE_ATTRIBUTE_READONLY = &H1
Public Const FILE_ATTRIBUTE_HIDDEN = &H2
Public Const FILE_ATTRIBUTE_SYSTEM = &H4
Public Const FILE_ATTRIBUTE_DIRECTORY = &H10
Public Const FILE_ATTRIBUTE_ARCHIVE = &H20
Public Const FILE_ATTRIBUTE_NORMAL = &H80
Public Const FILE_ATTRIBUTE_TEMPORARY = &H100
Public Const FILE_ATTRIBUTE_COMPRESSED = &H800
' psfi and cbFileInfo:
' Address and size, in bytes, of the SHFILEINFO structure that receives the file information.
' Maximun long filename path length
Public Const MAX_PATH = 260
Type SHFILEINFO ' shfi
hIcon As Long
iIcon As Long
dwAttributes As Long
szDisplayName As String * MAX_PATH
szTypeName As String * 80
End Type
' uFlags:
' Flag that specifies the file information to retrieve. This parameter can
' be a combination of the following values:
' Modifies SHGFI_ICON, causing the function to retrieve the file's large icon.
Public Const SHGFI_LARGEICON = &H0&
' Modifies SHGFI_ICON, causing the function to retrieve the file's small icon.
Public Const SHGFI_SMALLICON = &H1&
' Modifies SHGFI_ICON, causing the function to retrieve the file's open icon.
' A container object displays an open icon to indicate that the container is open.
Public Const SHGFI_OPENICON = &H2&
' Modifies SHGFI_ICON, causing the function to retrieve a shell-sized icon.
' If this flag is not specified, the function sizes the icon according to the system metric values.
Public Const SHGFI_SHELLICONSIZE = &H4&
' Indicates that pszPath is the address of an ITEMIDLIST structure rather than a path name.
Public Const SHGFI_PIDL = &H8&
' Indicates that the function should use the dwFileAttributes parameter.
Public Const SHGFI_USEFILEATTRIBUTES = &H10&
' Retrieves the handle of the icon that represents the file and the index of the
' icon within the system image list. The handle is copied to the hIcon member
' of the structure specified by psfi, and the index is copied to the iIcon member.
' The return value is the handle of the system image list.
Public Const SHGFI_ICON = &H100&
' Retrieves the display name for the file. The name is copied to the szDisplayName
' member of the structure specified by psfi. The returned display name uses the
' long filename, if any, rather than the 8.3 form of the filename.
Public Const SHGFI_DISPLAYNAME = &H200&
' Retrieves the string that describes the file's type. The string is copied to the
' szTypeName member of the structure specified by psfi.
Public Const SHGFI_TYPENAME = &H400&
' Retrieves the file attribute flags. The flags are copied to the dwAttributes
' member of the structure specified by psfi.
Public Const SHGFI_ATTRIBUTES = &H800&
' Retrieves the name of the file that contains the icon representing the file.
' The name is copied to the szDisplayName member of the structure specified by psfi.
Public Const SHGFI_ICONLOCATION = &H1000&
' Returns the type of the executable file if pszPath identifies an executable file.
' To retrieve the executable file type, uFlags must specify only SHGFI_EXETYPE.
' The return value specifies the type of the executable file:
' 0 Nonexecutable file or an error condition.
' LOWORD = NE or PEHIWORD = 3.0, 3.5, or 4.0 Windows application
' LOWORD = MZHIWORD = 0 MS-DOS .EXE, .COM or .BAT file
' LOWORD = PEHIWORD = 0 Win32 console application
Public Const SHGFI_EXETYPE = &H2000&
' Retrieves the index of the icon within the system image list. The index is copied to the iIcon
' member of the structure specified by psfi. The return value is the handle of the system image list.
Public Const SHGFI_SYSICONINDEX = &H4000&
' Modifies SHGFI_ICON, causing the function to add the link overlay to the file's icon.
Public Const SHGFI_LINKOVERLAY = &H8000&
' Modifies SHGFI_ICON, causing the function to blend the file's icon with the system highlight color.
Public Const SHGFI_SELECTED = &H10000
