FredixBlog

This function copies a file

Const FOF_NOCONFIRMMKDIR = &H200
Const FOF_FILESONLY = &H80

Private Type SHFILEOPSTRUCT
      hwnd As Long
    wFunc As Long
    pFrom As String
    pTo As String
    fFlags As Integer
    fAborted As Boolean
    hNameMaps As Long
    sProgress As String
End Type

Private Declare Function SHFileOperation Lib "shell32.dll" _
    Alias "SHFileOperationA" (lpFileOp As SHFILEOPSTRUCT) As Long

Public Function MyFileCopy(txtSource, txtDestination)
Dim lFileOp As Long
Dim lresult As Long
Dim lFlags As Long
Dim SHFileOp As SHFILEOPSTRUCT
Dim chkSilent, chkYesToAll, chkRename, chkDir, chkFilesOnly As Boolean

DoCmd.Hourglass True

lFileOp = FO_COPY

chkYesToAll = True
chkSilent = True

If chkSilent Then lFlags = lFlags Or FOF_SILENT
If chkYesToAll Then lFlags = lFlags Or FOF_NOCONFIRMATION
If chkRename Then lFlags = lFlags Or FOF_RENAMEONCOLLISION
If chkDir Then lFlags = lFlags Or FOF_NOCONFIRMMKDIR
If chkFilesOnly Then lFlags = lFlags Or FOF_FILESONLY
'
' NOTE: By adding the FOF_ALLOWUNDO flag you can move
' a file to the Recycle Bin instead of deleting it.
'
With SHFileOp
    .wFunc = lFileOp
    .pFrom = txtSource & vbNullChar & vbNullChar
    .pTo = txtDestination & vbNullChar & vbNullChar
    .fFlags = lFlags
End With
lresult = SHFileOperation(SHFileOp)
'
' If User hit Cancel button while operation is in progress,
' the fAborted parameter will be true
'
DoCmd.Hourglass False
'If lresult <> 0 Or SHFileOp.fAborted Then Exit Function
'MsgBox "Operation Complete", vbInformation, "File Operations"
MyFileCopy = lresult
End Function

• Create ODBC DSN
• Clear all line breaks
• GetOpenFileName()
• Gibson plus solide que Fender?
• Display a counter and remaining time in a loop
• Truncate long path names
• How can I e-mail a file from Microsoft Access?
• Create a linked table with VBA
• Coordinates of the third point
• Message to log file

This function is a call to the ‘Open File’ dialog box.
For instance, if you call the function from a button in a form, your call will look like this:

sFilename = gsWinDlgOpen((Me.Hwnd), \"Title of the window\", \"MyFile.txt\" & Chr$(0) & \"MyFile.txt\" & Chr$(0), \"C:\\\")

Declare Function GetOpenFileNameA Lib \"COMDLG32.DLL\" (tFilename As CMDLG_FILENAME) As Integer

Public Function GetOpenFileName(tFilename As CMDLG_FILENAME) As Integer
  GetOpenFileName = GetOpenFileNameA(tFilename)
End Function
Type CMDLG_FILENAME
    lStructSize As Long
    hWndOwner As Long
    hInstance As Long
    lpstrsFilter As String
    lpstrCustomsFilter As String
    nMaxCustsFilter As Long
    nsFilterIndex 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

Declare Function GetOpenFileNameA Lib "COMDLG32.DLL" (tFilename As CMDLG_FILENAME) As Integer

Public Function GetOpenFileName(tFilename As CMDLG_FILENAME) As Integer
   GetOpenFileName = GetOpenFileNameA(tFilename)
End Function

Const CMDLG_OFN_READONLY = &H1
Const CMDLG_OFN_OVERWRITEPROMPT = &H2
Const CMDLG_OFN_HIDEREADONLY = &H4
Const CMDLG_OFN_NOCHANGEDIR = &H8
Const CMDLG_OFN_SHOWHELP = &H10
Const CMDLG_OFN_ENABLEHOOK = &H20
Const CMDLG_OFN_ENABLETEMPLATE = &H40
Const CMDLG_OFN_ENABLETEMPLATEHANDLE = &H80
Const CMDLG_OFN_NOVALIDATE = &H100
Const CMDLG_OFN_ALLOWMULTISELECT = &H200
Const CMDLG_OFN_EXTENSIONDIFFERENT = &H400
Const CMDLG_OFN_PATHMUSTEXIST = &H800
Const CMDLG_OFN_FILEMUSTEXIST = &H1000
Const CMDLG_OFN_CREATEPROMPT = &H2000
Const CMDLG_OFN_SHAREAWARE = &H4000
Const CMDLG_OFN_NOREADONLYRETURN = &H8000
Const CMDLG_OFN_NOTESTFILECREATE = &H10000

Const CMDLG_OFN_SHAREFALLTHROUGH = 2
Const CMDLG_OFN_SHARENOWARN = 1
Const CMDLG_OFN_SHAREWARN = 0

Function gsWinDlgOpen(hWnd As Long, sTitel As String, sFilter As String, sCurDir As String) As String

   Dim tFilename As CMDLG_FILENAME
   Dim sFilename As String
   Dim sFiletitel As String
   Dim sDefExt As String

   sTitel = sTitel + Chr$(0)
   sFilter = sFilter & "Alle Dateien (*.*)" & Chr$(0) & "*.*" & Chr$(0) & Chr$(0)
   sFilename = Chr$(0) & Space$(255) & Chr$(0)
   sFiletitel = Chr$(0) & Space$(255) & Chr$(0)
   sDefExt = Chr$(0)
   sCurDir = gvntNull2Arg(sCurDir, CurDir$) & Chr$(0)

   tFilename.lStructSize = Len(tFilename)
   tFilename.hWndOwner = hWnd
   tFilename.lpstrsFilter = sFilter
   tFilename.nsFilterIndex = 1
   tFilename.lpstrFile = sFilename
   tFilename.nMaxFile = Len(sFilename)
   tFilename.lpstrFileTitle = sFiletitel
   tFilename.nMaxFileTitle = Len(sFiletitel)
   tFilename.lpstrTitle = sTitel
   tFilename.Flags = CMDLG_OFN_FILEMUSTEXIST Or CMDLG_OFN_HIDEREADONLY
   tFilename.lpstrInitialDir = sCurDir

   If GetOpenFileName(tFilename) <> 0 Then
       gsWinDlgOpen = gsTrim(tFilename.lpstrFile)
   Else
       gsWinDlgOpen = ""
   End If

End Function
Function gsTrim(s As String) As String

   gsTrim = Trim$(Left$(s, InStr(1, s & Chr$(0), Chr$(0), 0) - 1))

End Function

Function gvntNull2Arg(vntExp As Variant, vntPara As Variant) As Variant

   If gbNull(vntExp) Then
       gvntNull2Arg = vntPara
   Else
       gvntNull2Arg = vntExp
   End If
   
End Function

Function gbNull(vnt As Variant) As Integer

   On Error GoTo gbNullError

   Select Case VarType(vnt)
   Case V_NULL, V_EMPT
       gbNull = True
   Case V_STRING
       gbNull = (Len(gsTrim(CStr(vnt))) = 0)
   Case V_DATE
       gbNull = False
   Case Else
       gbNull = (vnt = 0)
   End Select

gbNullExit:
   Exit Function

gbNullError:
   gbNull = True
   Resume gbNullExit

End Function

• How can I e-mail a file from Microsoft Access?
• Copy a file
• Create ODBC DSN
• Gibson plus solide que Fender?
• Display a counter and remaining time in a loop
• Truncate long path names
• Check if file exists
• Create a linked table with VBA
• Clear all line breaks
• Coordinates of the third point

Creates a system ODBC DSN thru a program.
As you can see, we create the DSN by making an entry in the registry.
In your Project you need Form1 and a command button Command1. Here’s the code for the General Declarations:

    Option Explicit

    Private Const REG_SZ = 1 'Constant for a string variable type.
    Private Const HKEY_LOCAL_MACHINE = &H80000002

    Private Declare Function RegCreateKey Lib "advapi32.dll" Alias _
       "RegCreateKeyA" (ByVal hKey As Long, ByVal lpSubKey As String, _
       phkResult As Long) As Long

    Private Declare Function RegSetValueEx Lib "advapi32.dll" Alias _
       "RegSetValueExA" (ByVal hKey As Long, ByVal lpValueName As String, _
       ByVal Reserved As Long, ByVal dwType As Long, lpData As Any, ByVal _
       cbData As Long) As Long

    Private Declare Function RegCloseKey Lib "advapi32.dll" _
       (ByVal hKey As Long) As Long

Next go to the click event of the Command1 button and enter the following:

Private Sub Command1_Click()

   Dim DataSourceName As String
   Dim DatabaseName As String
   Dim Description As String
   Dim DriverPath As String
   Dim DriverName As String
   Dim LastUser As String
   Dim Regional As String
   Dim Server As String

   Dim lResult As Long
   Dim hKeyHandle As Long

   'Specify the DSN parameters.

   DataSourceName = " "
   DatabaseName = ""
   Description = "<a>"
   DriverPath = " "
   LastUser = ""
   Server = ""
   DriverName = "SQL Server"</a>

   'Create the new DSN key.

   lResult = RegCreateKey(HKEY_LOCAL_MACHINE, "SOFTWARE\ODBC\ODBC.INI\" &amp; _
        DataSourceName, hKeyHandle)

   'Set the values of the new DSN key.

   lResult = RegSetValueEx(hKeyHandle, "Database", 0&, REG_SZ, _
      ByVal DatabaseName, Len(DatabaseName))
   lResult = RegSetValueEx(hKeyHandle, "Description", 0&, REG_SZ, _
      ByVal Description, Len(Description))
   lResult = RegSetValueEx(hKeyHandle, "Driver", 0&, REG_SZ, _
      ByVal DriverPath, Len(DriverPath))
   lResult = RegSetValueEx(hKeyHandle, "LastUser", 0&, REG_SZ, _
      ByVal LastUser, Len(LastUser))
   lResult = RegSetValueEx(hKeyHandle, "Server", 0&, REG_SZ, _
      ByVal Server, Len(Server))

   'Close the new DSN key.

   lResult = RegCloseKey(hKeyHandle)

   'Open ODBC Data Sources key to list the new DSN in the
ODBC Manager.
   'Specify the new value.
   'Close the key.

   lResult = RegCreateKey(HKEY_LOCAL_MACHINE, _
      "SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources",
hKeyHandle)
   lResult = RegSetValueEx(hKeyHandle, DataSourceName, 0&,
REG_SZ, _
      ByVal DriverName, Len(DriverName))
   lResult = RegCloseKey(hKeyHandle)

   End Sub

• Copy a file
• Display a counter and remaining time in a loop
• Truncate long path names
• How can I e-mail a file from Microsoft Access?
• Clear all line breaks
• GetOpenFileName()
• Relink all tables
• ShellWait()
• Gibson plus solide que Fender?
• Create a linked table with VBA

This is the same function as the Shell() function, but in this case the code will wait until the called program is finished.

Private Const STARTF_USESHOWWINDOW& = &H1
Private Const NORMAL_PRIORITY_CLASS = &H20&
Private Const INFINITE = -1&

Private Type STARTUPINFO
   cb As Long
   lpReserved As String
   lpDesktop As String
   lpTitle As String
   dwX As Long
   dwY As Long
   dwXSize As Long
   dwYSize As Long
   dwXCountChars As Long
   dwYCountChars As Long
   dwFillAttribute As Long
   dwFlags As Long
   wShowWindow As Integer
   cbReserved2 As Integer
   lpReserved2 As Long
   hStdInput As Long
   hStdOutput As Long
   hStdError As Long
End Type
Private Type PROCESS_INFORMATION
   hProcess As Long
   hThread As Long
   dwProcessID As Long
   dwThreadID As Long
End Type
Private Declare Function WaitForSingleObject Lib "kernel32" (ByVal _
   hHandle As Long, ByVal dwMilliseconds As Long) As Long
Private Declare Function CreateProcessA Lib "kernel32" (ByVal _
   lpApplicationName As Long, ByVal lpCommandLine As String, ByVal _
   lpProcessAttributes As Long, ByVal lpThreadAttributes As Long, _
   ByVal bInheritHandles As Long, ByVal dwCreationFlags As Long, _
   ByVal lpEnvironment As Long, ByVal lpCurrentDirectory As Long, _
   lpStartupInfo As STARTUPINFO, lpProcessInformation As _
   PROCESS_INFORMATION) As Long
Private Declare Function CloseHandle Lib "kernel32" (ByVal _
   hObject As Long) As Long
Public Sub ShellWait(Pathname As String, Optional WindowStyle As Long)
   Dim proc As PROCESS_INFORMATION
   Dim Start As STARTUPINFO
   Dim ret As Long
   ' Initialize the STARTUPINFO structure:
   With Start
       .cb = Len(Start)
       If Not IsMissing(WindowStyle) Then
           .dwFlags = STARTF_USESHOWWINDOW
           .wShowWindow = WindowStyle
       End If
   End With
   ' Start the shelled application:
   ret& = CreateProcessA(0&, Pathname, 0&, 0&, 1&, _
           NORMAL_PRIORITY_CLASS, 0&, 0&, Start, proc)
   ' Wait for the shelled application to finish:
   ret& = WaitForSingleObject(proc.hProcess, INFINITE)
   ret& = CloseHandle(proc.hProcess)
End Sub

• Create ODBC DSN
• Truncate long path names
• Clear all line breaks
• Copy a file
• GetOpenFileName()
• Splitting File and Path of folder: NoPath() & FilePath()
• Relink all tables