Hi,

I'd like to know if it is possible to make a reference in vba to an external
program.

Now, I can make an object-reference to Excel like this:

Set xlExcel = New Excel.Application
xlExcel.Visible = True
xlExcel.Workbooks.Open vFile, False, True

I'd like to do the same, but now to a different (not registered in windows)
program. The reason is this: I made a portable version of excel (including
VBA) and I want to let my application start with that portable version
(Excel 2003), even if Excel 2007 is installed. In that case, I do not depend
on the caprices of Microsoft for my further development (like menu's which
disappear in office 2007).

My new code should be (Excel2003 = portable version of Excel 2003)

Set xlExcel = New Excel2003.Application
xlExcel.Visible = True
xlExcel.Workbooks.Open vFile, False, True

It would also be interesting to do this to other programs I use within VBA.
I think it has to go within the Class Module-section, but I don't know how
to start.

Thanks in advance
Jos Vens

You can make references to DLL. Most executables can be built either as a
DLL or an EXE. Look at the library defininitions like this one

Public Declare Function FtpSetCurrentDirectory Lib "wininet.dll" Alias
"FtpSetCurrentDirectoryA" _
(ByVal hFtpSession As Long, ByVal lpszDirectory As String) As Boolean


Here is an FTP application.

Const MAX_PATH = 260


' Set Constants
Const FTP_TRANSFER_TYPE_ASCII = &H1
Const FTP_TRANSFER_TYPE_BINARY = &H2
Const INTERNET_DEFAULT_FTP_PORT = 21
Const INTERNET_SERVICE_FTP = 1
Const INTERNET_FLAG_PASSIVE = &H8000000
Const GENERIC_WRITE = &H40000000
Const BUFFER_SIZE = 100
Const PassiveConnection As Boolean = True


Type WIN32_FIND_DATA
dwFileAttributes As Long
ftCreationTime As Currency
ftLastAccessTime As Currency
ftLastWriteTime As Currency
nFileSizeHigh As Long
nFileSizeLow As Long
dwReserved0 As Long
dwReserved1 As Long
cFileName As String * MAX_PATH
cAlternate As String * 14
End Type

' Declare wininet.dll API Functions
Public Declare Function FtpSetCurrentDirectory Lib "wininet.dll" Alias
"FtpSetCurrentDirectoryA" _
(ByVal hFtpSession As Long, ByVal lpszDirectory As String) As Boolean

Public Declare Function FtpGetCurrentDirectory Lib "wininet.dll" Alias
"FtpGetCurrentDirectoryA" _
(ByVal hFtpSession As Long, ByVal lpszCurrentDirectory As String,
lpdwCurrentDirectory As Long) As Boolean

Public Declare Function InternetWriteFile Lib "wininet.dll" _
(ByVal hFile As Long, ByRef sBuffer As Byte, ByVal lNumBytesToWite As Long, _
dwNumberOfBytesWritten As Long) As Integer

Public Declare Function FtpOpenFile Lib "wininet.dll" Alias "FtpOpenFileA" _
(ByVal hFtpSession As Long, ByVal sBuff As String, ByVal Access As Long,
ByVal Flags As Long, ByVal Context As Long) As Long

Public Declare Function FtpPutFile Lib "wininet.dll" Alias "FtpPutFileA" _
(ByVal hFtpSession As Long, ByVal lpszLocalFile As String, _
ByVal lpszRemoteFile As String, _
ByVal dwFlags As Long, ByVal dwContext As Long) As Boolean

Public Declare Function FtpDeleteFile Lib "wininet.dll" _
Alias "FtpDeleteFileA" (ByVal hFtpSession As Long, _
ByVal lpszFileName As String) As Boolean
Public Declare Function InternetCloseHandle Lib "wininet.dll" _
(ByVal hInet As Long) As Long

Public Declare Function InternetOpen Lib "wininet.dll" Alias "InternetOpenA" _
(ByVal sAgent As String, ByVal lAccessType As Long, ByVal sProxyName As
String, _
ByVal sProxyBypass As String, ByVal lFlags As Long) As Long

Public Declare Function InternetConnect Lib "wininet.dll" Alias
"InternetConnectA" _
(ByVal hInternetSession As Long, ByVal sServerName As String, ByVal
nServerPort As Integer, _
ByVal sUsername As String, ByVal sPassword As String, ByVal lService As
Long, _
ByVal lFlags As Long, ByVal lContext As Long) As Long


Public Declare Function FtpGetFile Lib "wininet.dll" Alias "FtpGetFileA" _
(ByVal hFtpSession As Long, ByVal lpszRemoteFile As String, _
ByVal lpszNewFile As String, ByVal fFailIfExists As Boolean, ByVal
dwFlagsAndAttributes As Long, _
ByVal dwFlags As Long, ByVal dwContext As Long) As Boolean

Declare Function InternetGetLastResponseInfo Lib "wininet.dll" _
Alias "InternetGetLastResponseInfoA" _
(ByRef lpdwError As Long, _
ByVal lpszErrorBuffer As String, _
ByRef lpdwErrorBufferLength As Long) As Boolean

Public Declare Function FtpFindFirstFile Lib "wininet.dll" Alias
"FtpFindFirstFileA" _
(ByVal hInternetSession As Long, ByVal lpszSearchFile As String, _
ByRef lpFindFileData As WIN32_FIND_DATA, ByVal dwFlags As Long, _
ByVal dwContext As Long) As Long


Public Declare Function InternetFindNextFile Lib "wininet.dll" Alias
"InternetFindNextFileA" _
(ByVal hInternetSession As Long, ByRef lpvFindData As WIN32_FIND_DATA) As Long



Function FTPFile(ByVal HostName As String, _
ByVal UserName As String, _
ByVal Password As String, _
ByVal LocalFileName As String, _
ByVal RemoteFileName As String, _
ByVal sDir As String, _
ByVal sMode As String) As Boolean

On Error GoTo Err_Function

' Declare variables
Dim hConnection, hOpen, hFile As Long ' Used For Handles
Dim iSize As Long ' Size of file for upload
Dim Retval As Variant ' Used for progress meter
Dim iWritten As Long ' Used by InternetWriteFile to report bytes uploaded
Dim iLoop As Long ' Loop for uploading chuncks
Dim iFile As Integer ' Used for Local file handle
Dim FileData(BUFFER_SIZE - 1) As Byte ' buffer array of BUFFER_SIZE (100)
elements 0 to 99

' Open Internet Connecion
hOpen = InternetOpen("FTP", 1, "", vbNullString, 0)

' Connect to FTP
hConnection = InternetConnect(hOpen, HostName, INTERNET_DEFAULT_FTP_PORT,
UserName, Password, INTERNET_SERVICE_FTP, IIf(PassiveConnection,
INTERNET_FLAG_PASSIVE, 0), 0)

' Change Directory
Call FtpSetCurrentDirectory(hConnection, sDir)

' Open Remote File
hFile = FtpOpenFile(hConnection, RemoteFileName, GENERIC_WRITE, IIf(sMode =
"Binary", FTP_TRANSFER_TYPE_BINARY, FTP_TRANSFER_TYPE_ASCII), 0)

' Check for successfull file handle
If hFile = 0 Then
MsgBox "Internet - Failed!"
ShowError
FTPFile = False
GoTo Exit_Function
End If

' Set Upload Flag to True
FTPFile = True

' Get next file handle number
iFile = FreeFile

' Open local file
Open LocalFileName For Binary Access Read As iFile

' Set file size
iSize = LOF(iFile)

' Iinitialise progress meter
Retval = SysCmd(acSysCmdInitMeter, "Uploading File (" & RemoteFileName &
")", iSize / 1000)

' Loop file size
For iLoop = 1 To iSize \ BUFFER_SIZE

' Update progress meter
Retval = SysCmd(acSysCmdUpdateMeter, (BUFFER_SIZE * iLoop) / 1000)

'Get file data
Get iFile, , FileData

' Write chunk to FTP checking for success
If InternetWriteFile(hFile, FileData(0), BUFFER_SIZE, iWritten) = 0 Then
MsgBox "Upload - Failed!"
ShowError
FTPFile = False
GoTo Exit_Function
Else
' Check buffer was written
If iWritten < BUFFER_SIZE Then
MsgBox "Upload - Failed!"
ShowError
FTPFile = False
GoTo Exit_Function
End If
End If

Next iLoop

' Handle remainder using MOD

' Update progress meter
Retval = SysCmd(acSysCmdUpdateMeter, iSize / 1000)

' Get file data
Get iFile, , FileData

' Write remainder to FTP checking for success
If InternetWriteFile(hFile, FileData(0), iSize Mod BUFFER_SIZE,
iWritten) = 0 Then
MsgBox "Upload - Failed!"
ShowError
FTPFile = False
GoTo Exit_Function
Else
' Check buffer was written
If iWritten < iSize Mod BUFFER_SIZE Then
MsgBox "Upload - Failed!"
ShowError
FTPFile = False
GoTo Exit_Function
End If
End If

Exit_Function:

' remove progress meter
Retval = SysCmd(acSysCmdRemoveMeter)

'close remote file
Call InternetCloseHandle(hFile)

'close local file
Close iFile

' Close Internet Connection
Call InternetCloseHandle(hOpen)
Call InternetCloseHandle(hConnection)

Exit Function

Err_Function:
MsgBox "Error in FTPFile : " & Err.Description
GoTo Exit_Function

End Function

Function FTPGetDir(ByVal HostName As String, ByVal User As String, _
ByVal PassWd As String, ByVal Folder As String)

' Declare variables
Dim hConnection, hOpen As Long ' Used For Handles
Dim lpszCurrentDirectory As String
Dim lpdwCurrentDirectory As Long
Dim lpFindFileData As WIN32_FIND_DATA
Dim hfind As Long

lpszCurrentDirectory = String(1024, Chr(0))
lpdwCurrentDirectory = 1024

' Open Internet Connecion
hOpen = InternetOpen("FTP", 1, "", vbNullString, 0)

' Connect to FTP
hConnection = InternetConnect(hOpen, HostName, INTERNET_DEFAULT_FTP_PORT,
UserName, Password, INTERNET_SERVICE_FTP, IIf(PassiveConnection,
INTERNET_FLAG_PASSIVE, 0), 0)


Status = FtpGetCurrentDirectory(hConnection, _
lpszCurrentDirectory, lpdwCurrentDirectory)

hfind = FtpFindFirstFile(hConnection, lpszCurrentDirectory, _
lpFindFileData, IIf(PassiveConnection, _
INTERNET_FLAG_PASSIVE, 0), 0)

If hfind < 0 Then
Range("A1") = lpFindFileData.cFileName
RowCount = 2
Do While lpFindFileData.cFileName < ""

lpFindFileData.cFileName = String(MAX_PATH, 0)
Status = InternetFindNextFile(hfind, lpFindFileData)

If Status = 0 Then
Exit Do
Else
Range("A" & RowCount) = lpFindFileData.cFileName
RowCount = RowCount + 1
End If
Loop
End If
End Function
Sub ShowError()
Dim lErr As Long, sErr As String, lenBuf As Long
'get the required buffer size
InternetGetLastResponseInfo lErr, sErr, lenBuf
'create a buffer
sErr = String(lenBuf, 0)
'retrieve the last respons info
InternetGetLastResponseInfo lErr, sErr, lenBuf
'show the last response info
MsgBox "Last Server Response : " + sErr, vbOKOnly + vbCritical
End Sub

Sub FTP()

' Upload file
If FTPFile("ftp.domain.com", "myUserName", "myPassword", "Full path and
Filename of local file", "Target Filename without path", "Directory on FTP
server", "Upload Mode - Binary or ASCII") Then
MsgBox "Upload - Complete!"
End If
End Sub
Sub test_GetDirectory()
HostName = "ftp.microsoft.com"
User = "FTP"
PassWd = " 'enter email account
Folder = ""

Call FTPGetDir(HostName, _
User, _
PassWd, _
Folder)

End Sub




"Jos Vens" wrote:

Hi,

I'd like to know if it is possible to make a reference in vba to an external
program.

Now, I can make an object-reference to Excel like this:

Set xlExcel = New Excel.Application
xlExcel.Visible = True
xlExcel.Workbooks.Open vFile, False, True

I'd like to do the same, but now to a different (not registered in windows)
program. The reason is this: I made a portable version of excel (including
VBA) and I want to let my application start with that portable version
(Excel 2003), even if Excel 2007 is installed. In that case, I do not depend
on the caprices of Microsoft for my further development (like menu's which
disappear in office 2007).

My new code should be (Excel2003 = portable version of Excel 2003)

Set xlExcel = New Excel2003.Application
xlExcel.Visible = True
xlExcel.Workbooks.Open vFile, False, True

It would also be interesting to do this to other programs I use within VBA.
I think it has to go within the Class Module-section, but I don't know how
to start.

Thanks in advance
Jos Vens

thanks Joel,

but that's not exactly what I'm looking for. Let's say the external
application is written by a third party, so there's no possibilty to convert
it to a dll-file. Code should look like the excel example:

Why is excel "known" by vba? and other programs not??? Can you put a path
into the object reference???

Set xlExcel = New Excel.Application
xlExcel.Visible = True
xlExcel.Workbooks.Open vFile, False, True

Thanks anyway,
Jos


"Joel" schreef in bericht
...
You can make references to DLL. Most executables can be built either as a
DLL or an EXE. Look at the library defininitions like this one

Public Declare Function FtpSetCurrentDirectory Lib "wininet.dll" Alias
"FtpSetCurrentDirectoryA" _
(ByVal hFtpSession As Long, ByVal lpszDirectory As String) As Boolean


Here is an FTP application.

Const MAX_PATH = 260


' Set Constants
Const FTP_TRANSFER_TYPE_ASCII = &H1
Const FTP_TRANSFER_TYPE_BINARY = &H2
Const INTERNET_DEFAULT_FTP_PORT = 21
Const INTERNET_SERVICE_FTP = 1
Const INTERNET_FLAG_PASSIVE = &H8000000
Const GENERIC_WRITE = &H40000000
Const BUFFER_SIZE = 100
Const PassiveConnection As Boolean = True


Type WIN32_FIND_DATA
dwFileAttributes As Long
ftCreationTime As Currency
ftLastAccessTime As Currency
ftLastWriteTime As Currency
nFileSizeHigh As Long
nFileSizeLow As Long
dwReserved0 As Long
dwReserved1 As Long
cFileName As String * MAX_PATH
cAlternate As String * 14
End Type

' Declare wininet.dll API Functions
Public Declare Function FtpSetCurrentDirectory Lib "wininet.dll" Alias
"FtpSetCurrentDirectoryA" _
(ByVal hFtpSession As Long, ByVal lpszDirectory As String) As Boolean

Public Declare Function FtpGetCurrentDirectory Lib "wininet.dll" Alias
"FtpGetCurrentDirectoryA" _
(ByVal hFtpSession As Long, ByVal lpszCurrentDirectory As String,
lpdwCurrentDirectory As Long) As Boolean

Public Declare Function InternetWriteFile Lib "wininet.dll" _
(ByVal hFile As Long, ByRef sBuffer As Byte, ByVal lNumBytesToWite As
Long, _
dwNumberOfBytesWritten As Long) As Integer

Public Declare Function FtpOpenFile Lib "wininet.dll" Alias "FtpOpenFileA"
_
(ByVal hFtpSession As Long, ByVal sBuff As String, ByVal Access As Long,
ByVal Flags As Long, ByVal Context As Long) As Long

Public Declare Function FtpPutFile Lib "wininet.dll" Alias "FtpPutFileA" _
(ByVal hFtpSession As Long, ByVal lpszLocalFile As String, _
ByVal lpszRemoteFile As String, _
ByVal dwFlags As Long, ByVal dwContext As Long) As Boolean

Public Declare Function FtpDeleteFile Lib "wininet.dll" _
Alias "FtpDeleteFileA" (ByVal hFtpSession As Long, _
ByVal lpszFileName As String) As Boolean
Public Declare Function InternetCloseHandle Lib "wininet.dll" _
(ByVal hInet As Long) As Long

Public Declare Function InternetOpen Lib "wininet.dll" Alias
"InternetOpenA" _
(ByVal sAgent As String, ByVal lAccessType As Long, ByVal sProxyName As
String, _
ByVal sProxyBypass As String, ByVal lFlags As Long) As Long

Public Declare Function InternetConnect Lib "wininet.dll" Alias
"InternetConnectA" _
(ByVal hInternetSession As Long, ByVal sServerName As String, ByVal
nServerPort As Integer, _
ByVal sUsername As String, ByVal sPassword As String, ByVal lService As
Long, _
ByVal lFlags As Long, ByVal lContext As Long) As Long


Public Declare Function FtpGetFile Lib "wininet.dll" Alias "FtpGetFileA" _
(ByVal hFtpSession As Long, ByVal lpszRemoteFile As String, _
ByVal lpszNewFile As String, ByVal fFailIfExists As Boolean, ByVal
dwFlagsAndAttributes As Long, _
ByVal dwFlags As Long, ByVal dwContext As Long) As Boolean

Declare Function InternetGetLastResponseInfo Lib "wininet.dll" _
Alias "InternetGetLastResponseInfoA" _
(ByRef lpdwError As Long, _
ByVal lpszErrorBuffer As String, _
ByRef lpdwErrorBufferLength As Long) As Boolean

Public Declare Function FtpFindFirstFile Lib "wininet.dll" Alias
"FtpFindFirstFileA" _
(ByVal hInternetSession As Long, ByVal lpszSearchFile As String, _
ByRef lpFindFileData As WIN32_FIND_DATA, ByVal dwFlags As Long, _
ByVal dwContext As Long) As Long


Public Declare Function InternetFindNextFile Lib "wininet.dll" Alias
"InternetFindNextFileA" _
(ByVal hInternetSession As Long, ByRef lpvFindData As WIN32_FIND_DATA) As
Long



Function FTPFile(ByVal HostName As String, _
ByVal UserName As String, _
ByVal Password As String, _
ByVal LocalFileName As String, _
ByVal RemoteFileName As String, _
ByVal sDir As String, _
ByVal sMode As String) As Boolean

On Error GoTo Err_Function

' Declare variables
Dim hConnection, hOpen, hFile As Long ' Used For Handles
Dim iSize As Long ' Size of file for upload
Dim Retval As Variant ' Used for progress meter
Dim iWritten As Long ' Used by InternetWriteFile to report bytes uploaded
Dim iLoop As Long ' Loop for uploading chuncks
Dim iFile As Integer ' Used for Local file handle
Dim FileData(BUFFER_SIZE - 1) As Byte ' buffer array of BUFFER_SIZE (100)
elements 0 to 99

' Open Internet Connecion
hOpen = InternetOpen("FTP", 1, "", vbNullString, 0)

' Connect to FTP
hConnection = InternetConnect(hOpen, HostName, INTERNET_DEFAULT_FTP_PORT,
UserName, Password, INTERNET_SERVICE_FTP, IIf(PassiveConnection,
INTERNET_FLAG_PASSIVE, 0), 0)

' Change Directory
Call FtpSetCurrentDirectory(hConnection, sDir)

' Open Remote File
hFile = FtpOpenFile(hConnection, RemoteFileName, GENERIC_WRITE, IIf(sMode
=
"Binary", FTP_TRANSFER_TYPE_BINARY, FTP_TRANSFER_TYPE_ASCII), 0)

' Check for successfull file handle
If hFile = 0 Then
MsgBox "Internet - Failed!"
ShowError
FTPFile = False
GoTo Exit_Function
End If

' Set Upload Flag to True
FTPFile = True

' Get next file handle number
iFile = FreeFile

' Open local file
Open LocalFileName For Binary Access Read As iFile

' Set file size
iSize = LOF(iFile)

' Iinitialise progress meter
Retval = SysCmd(acSysCmdInitMeter, "Uploading File (" & RemoteFileName &
")", iSize / 1000)

' Loop file size
For iLoop = 1 To iSize \ BUFFER_SIZE

' Update progress meter
Retval = SysCmd(acSysCmdUpdateMeter, (BUFFER_SIZE * iLoop) / 1000)

'Get file data
Get iFile, , FileData

' Write chunk to FTP checking for success
If InternetWriteFile(hFile, FileData(0), BUFFER_SIZE, iWritten) = 0
Then
MsgBox "Upload - Failed!"
ShowError
FTPFile = False
GoTo Exit_Function
Else
' Check buffer was written
If iWritten < BUFFER_SIZE Then
MsgBox "Upload - Failed!"
ShowError
FTPFile = False
GoTo Exit_Function
End If
End If

Next iLoop

' Handle remainder using MOD

' Update progress meter
Retval = SysCmd(acSysCmdUpdateMeter, iSize / 1000)

' Get file data
Get iFile, , FileData

' Write remainder to FTP checking for success
If InternetWriteFile(hFile, FileData(0), iSize Mod BUFFER_SIZE,
iWritten) = 0 Then
MsgBox "Upload - Failed!"
ShowError
FTPFile = False
GoTo Exit_Function
Else
' Check buffer was written
If iWritten < iSize Mod BUFFER_SIZE Then
MsgBox "Upload - Failed!"
ShowError
FTPFile = False
GoTo Exit_Function
End If
End If

Exit_Function:

' remove progress meter
Retval = SysCmd(acSysCmdRemoveMeter)

'close remote file
Call InternetCloseHandle(hFile)

'close local file
Close iFile

' Close Internet Connection
Call InternetCloseHandle(hOpen)
Call InternetCloseHandle(hConnection)

Exit Function

Err_Function:
MsgBox "Error in FTPFile : " & Err.Description
GoTo Exit_Function

End Function

Function FTPGetDir(ByVal HostName As String, ByVal User As String, _
ByVal PassWd As String, ByVal Folder As String)

' Declare variables
Dim hConnection, hOpen As Long ' Used For Handles
Dim lpszCurrentDirectory As String
Dim lpdwCurrentDirectory As Long
Dim lpFindFileData As WIN32_FIND_DATA
Dim hfind As Long

lpszCurrentDirectory = String(1024, Chr(0))
lpdwCurrentDirectory = 1024

' Open Internet Connecion
hOpen = InternetOpen("FTP", 1, "", vbNullString, 0)

' Connect to FTP
hConnection = InternetConnect(hOpen, HostName, INTERNET_DEFAULT_FTP_PORT,
UserName, Password, INTERNET_SERVICE_FTP, IIf(PassiveConnection,
INTERNET_FLAG_PASSIVE, 0), 0)


Status = FtpGetCurrentDirectory(hConnection, _
lpszCurrentDirectory, lpdwCurrentDirectory)

hfind = FtpFindFirstFile(hConnection, lpszCurrentDirectory, _
lpFindFileData, IIf(PassiveConnection, _
INTERNET_FLAG_PASSIVE, 0), 0)

If hfind < 0 Then
Range("A1") = lpFindFileData.cFileName
RowCount = 2
Do While lpFindFileData.cFileName < ""

lpFindFileData.cFileName = String(MAX_PATH, 0)
Status = InternetFindNextFile(hfind, lpFindFileData)

If Status = 0 Then
Exit Do
Else
Range("A" & RowCount) = lpFindFileData.cFileName
RowCount = RowCount + 1
End If
Loop
End If
End Function
Sub ShowError()
Dim lErr As Long, sErr As String, lenBuf As Long
'get the required buffer size
InternetGetLastResponseInfo lErr, sErr, lenBuf
'create a buffer
sErr = String(lenBuf, 0)
'retrieve the last respons info
InternetGetLastResponseInfo lErr, sErr, lenBuf
'show the last response info
MsgBox "Last Server Response : " + sErr, vbOKOnly + vbCritical
End Sub

Sub FTP()

' Upload file
If FTPFile("ftp.domain.com", "myUserName", "myPassword", "Full path and
Filename of local file", "Target Filename without path", "Directory on FTP
server", "Upload Mode - Binary or ASCII") Then
MsgBox "Upload - Complete!"
End If
End Sub
Sub test_GetDirectory()
HostName = "ftp.microsoft.com"
User = "FTP"
PassWd = " 'enter email account
Folder = ""

Call FTPGetDir(HostName, _
User, _
PassWd, _
Folder)

End Sub




"Jos Vens" wrote:

Hi,

I'd like to know if it is possible to make a reference in vba to an
external
program.

Now, I can make an object-reference to Excel like this:

Set xlExcel = New Excel.Application
xlExcel.Visible = True
xlExcel.Workbooks.Open vFile, False, True

I'd like to do the same, but now to a different (not registered in
windows)
program. The reason is this: I made a portable version of excel
(including
VBA) and I want to let my application start with that portable version
(Excel 2003), even if Excel 2007 is installed. In that case, I do not
depend
on the caprices of Microsoft for my further development (like menu's
which
disappear in office 2007).

My new code should be (Excel2003 = portable version of Excel 2003)

Set xlExcel = New Excel2003.Application
xlExcel.Visible = True
xlExcel.Workbooks.Open vFile, False, True

It would also be interesting to do this to other programs I use within
VBA.
I think it has to go within the Class Module-section, but I don't know
how
to start.

Thanks in advance
Jos Vens

VBA is visual basic application which is embedded ion microsoft office
applications. the is a compiled version of Visual basic that will produce
executable files like .EXE and DLL. It is a seperate production that you
have to buy from microsoft.

You can launch other programs from VBA using a shell application and pass
the programs control information through a commeand line (provided the
aplicattion supports command line inputs).

The issue with running other applicattions is how to control the application
and how to pass data to/from the application. Some controls are possible by
using KEY functions. Like in excel you can save a file by pressing Alt-F and
then pressing S. these arre shortcut keys. If the applicaiton support
shortcut keys then you can run the shortcuts from VBA pretty easily. Othe
rappliocation accept scripting lanuages that on startup you can give the
program a script file from a command line input.

You have to read the manual for the application and see what feature are
support from a command line. Also check the menus for shortcut keys and the
characters that are underlines on the main menu. Like excel has F underlined
on the main menu which indicates a shortcut key of Alt-F.


"Jos Vens" wrote:

thanks Joel,

but that's not exactly what I'm looking for. Let's say the external
application is written by a third party, so there's no possibilty to convert
it to a dll-file. Code should look like the excel example:

Why is excel "known" by vba? and other programs not??? Can you put a path
into the object reference???

Set xlExcel = New Excel.Application
xlExcel.Visible = True
xlExcel.Workbooks.Open vFile, False, True

Thanks anyway,
Jos


"Joel" schreef in bericht
...
You can make references to DLL. Most executables can be built either as a
DLL or an EXE. Look at the library defininitions like this one

Public Declare Function FtpSetCurrentDirectory Lib "wininet.dll" Alias
"FtpSetCurrentDirectoryA" _
(ByVal hFtpSession As Long, ByVal lpszDirectory As String) As Boolean


Here is an FTP application.

Const MAX_PATH = 260


' Set Constants
Const FTP_TRANSFER_TYPE_ASCII = &H1
Const FTP_TRANSFER_TYPE_BINARY = &H2
Const INTERNET_DEFAULT_FTP_PORT = 21
Const INTERNET_SERVICE_FTP = 1
Const INTERNET_FLAG_PASSIVE = &H8000000
Const GENERIC_WRITE = &H40000000
Const BUFFER_SIZE = 100
Const PassiveConnection As Boolean = True


Type WIN32_FIND_DATA
dwFileAttributes As Long
ftCreationTime As Currency
ftLastAccessTime As Currency
ftLastWriteTime As Currency
nFileSizeHigh As Long
nFileSizeLow As Long
dwReserved0 As Long
dwReserved1 As Long
cFileName As String * MAX_PATH
cAlternate As String * 14
End Type

' Declare wininet.dll API Functions
Public Declare Function FtpSetCurrentDirectory Lib "wininet.dll" Alias
"FtpSetCurrentDirectoryA" _
(ByVal hFtpSession As Long, ByVal lpszDirectory As String) As Boolean

Public Declare Function FtpGetCurrentDirectory Lib "wininet.dll" Alias
"FtpGetCurrentDirectoryA" _
(ByVal hFtpSession As Long, ByVal lpszCurrentDirectory As String,
lpdwCurrentDirectory As Long) As Boolean

Public Declare Function InternetWriteFile Lib "wininet.dll" _
(ByVal hFile As Long, ByRef sBuffer As Byte, ByVal lNumBytesToWite As
Long, _
dwNumberOfBytesWritten As Long) As Integer

Public Declare Function FtpOpenFile Lib "wininet.dll" Alias "FtpOpenFileA"
_
(ByVal hFtpSession As Long, ByVal sBuff As String, ByVal Access As Long,
ByVal Flags As Long, ByVal Context As Long) As Long

Public Declare Function FtpPutFile Lib "wininet.dll" Alias "FtpPutFileA" _
(ByVal hFtpSession As Long, ByVal lpszLocalFile As String, _
ByVal lpszRemoteFile As String, _
ByVal dwFlags As Long, ByVal dwContext As Long) As Boolean

Public Declare Function FtpDeleteFile Lib "wininet.dll" _
Alias "FtpDeleteFileA" (ByVal hFtpSession As Long, _
ByVal lpszFileName As String) As Boolean
Public Declare Function InternetCloseHandle Lib "wininet.dll" _
(ByVal hInet As Long) As Long

Public Declare Function InternetOpen Lib "wininet.dll" Alias
"InternetOpenA" _
(ByVal sAgent As String, ByVal lAccessType As Long, ByVal sProxyName As
String, _
ByVal sProxyBypass As String, ByVal lFlags As Long) As Long

Public Declare Function InternetConnect Lib "wininet.dll" Alias
"InternetConnectA" _
(ByVal hInternetSession As Long, ByVal sServerName As String, ByVal
nServerPort As Integer, _
ByVal sUsername As String, ByVal sPassword As String, ByVal lService As
Long, _
ByVal lFlags As Long, ByVal lContext As Long) As Long


Public Declare Function FtpGetFile Lib "wininet.dll" Alias "FtpGetFileA" _
(ByVal hFtpSession As Long, ByVal lpszRemoteFile As String, _
ByVal lpszNewFile As String, ByVal fFailIfExists As Boolean, ByVal
dwFlagsAndAttributes As Long, _
ByVal dwFlags As Long, ByVal dwContext As Long) As Boolean

Declare Function InternetGetLastResponseInfo Lib "wininet.dll" _
Alias "InternetGetLastResponseInfoA" _
(ByRef lpdwError As Long, _
ByVal lpszErrorBuffer As String, _
ByRef lpdwErrorBufferLength As Long) As Boolean

Public Declare Function FtpFindFirstFile Lib "wininet.dll" Alias
"FtpFindFirstFileA" _
(ByVal hInternetSession As Long, ByVal lpszSearchFile As String, _
ByRef lpFindFileData As WIN32_FIND_DATA, ByVal dwFlags As Long, _
ByVal dwContext As Long) As Long


Public Declare Function InternetFindNextFile Lib "wininet.dll" Alias
"InternetFindNextFileA" _
(ByVal hInternetSession As Long, ByRef lpvFindData As WIN32_FIND_DATA) As
Long



Function FTPFile(ByVal HostName As String, _
ByVal UserName As String, _
ByVal Password As String, _
ByVal LocalFileName As String, _
ByVal RemoteFileName As String, _
ByVal sDir As String, _
ByVal sMode As String) As Boolean

On Error GoTo Err_Function

' Declare variables
Dim hConnection, hOpen, hFile As Long ' Used For Handles
Dim iSize As Long ' Size of file for upload
Dim Retval As Variant ' Used for progress meter
Dim iWritten As Long ' Used by InternetWriteFile to report bytes uploaded
Dim iLoop As Long ' Loop for uploading chuncks
Dim iFile As Integer ' Used for Local file handle
Dim FileData(BUFFER_SIZE - 1) As Byte ' buffer array of BUFFER_SIZE (100)
elements 0 to 99

' Open Internet Connecion
hOpen = InternetOpen("FTP", 1, "", vbNullString, 0)

' Connect to FTP
hConnection = InternetConnect(hOpen, HostName, INTERNET_DEFAULT_FTP_PORT,
UserName, Password, INTERNET_SERVICE_FTP, IIf(PassiveConnection,
INTERNET_FLAG_PASSIVE, 0), 0)

' Change Directory
Call FtpSetCurrentDirectory(hConnection, sDir)

' Open Remote File
hFile = FtpOpenFile(hConnection, RemoteFileName, GENERIC_WRITE, IIf(sMode
=
"Binary", FTP_TRANSFER_TYPE_BINARY, FTP_TRANSFER_TYPE_ASCII), 0)

' Check for successfull file handle
If hFile = 0 Then
MsgBox "Internet - Failed!"
ShowError
FTPFile = False
GoTo Exit_Function
End If

' Set Upload Flag to True
FTPFile = True

' Get next file handle number
iFile = FreeFile

' Open local file
Open LocalFileName For Binary Access Read As iFile

' Set file size
iSize = LOF(iFile)

' Iinitialise progress meter
Retval = SysCmd(acSysCmdInitMeter, "Uploading File (" & RemoteFileName &
")", iSize / 1000)

' Loop file size
For iLoop = 1 To iSize \ BUFFER_SIZE

' Update progress meter
Retval = SysCmd(acSysCmdUpdateMeter, (BUFFER_SIZE * iLoop) / 1000)

'Get file data
Get iFile, , FileData

' Write chunk to FTP checking for success
If InternetWriteFile(hFile, FileData(0), BUFFER_SIZE, iWritten) = 0
Then
MsgBox "Upload - Failed!"
ShowError
FTPFile = False
GoTo Exit_Function
Else
' Check buffer was written
If iWritten < BUFFER_SIZE Then
MsgBox "Upload - Failed!"
ShowError
FTPFile = False
GoTo Exit_Function
End If
End If

Next iLoop

' Handle remainder using MOD

' Update progress meter
Retval = SysCmd(acSysCmdUpdateMeter, iSize / 1000)

' Get file data
Get iFile, , FileData

' Write remainder to FTP checking for success
If InternetWriteFile(hFile, FileData(0), iSize Mod BUFFER_SIZE,
iWritten) = 0 Then
MsgBox "Upload - Failed!"
ShowError
FTPFile = False
GoTo Exit_Function
Else
' Check buffer was written
If iWritten < iSize Mod BUFFER_SIZE Then
MsgBox "Upload - Failed!"
ShowError
FTPFile = False
GoTo Exit_Function
End If
End If

Exit_Function:

' remove progress meter
Retval = SysCmd(acSysCmdRemoveMeter)

'close remote file
Call InternetCloseHandle(hFile)

'close local file
Close iFile

' Close Internet Connection
Call InternetCloseHandle(hOpen)
Call InternetCloseHandle(hConnection)

Exit Function

Err_Function:
MsgBox "Error in FTPFile : " & Err.Description
GoTo Exit_Function

End Function

Function FTPGetDir(ByVal HostName As String, ByVal User As String, _
ByVal PassWd As String, ByVal Folder As String)

' Declare variables
Dim hConnection, hOpen As Long ' Used For Handles
Dim lpszCurrentDirectory As String
Dim lpdwCurrentDirectory As Long
Dim lpFindFileData As WIN32_FIND_DATA
Dim hfind As Long

lpszCurrentDirectory = String(1024, Chr(0))
lpdwCurrentDirectory = 1024

' Open Internet Connecion
hOpen = InternetOpen("FTP", 1, "", vbNullString, 0)

' Connect to FTP
hConnection = InternetConnect(hOpen, HostName, INTERNET_DEFAULT_FTP_PORT,
UserName, Password, INTERNET_SERVICE_FTP, IIf(PassiveConnection,
INTERNET_FLAG_PASSIVE, 0), 0)


Status = FtpGetCurrentDirectory(hConnection, _
lpszCurrentDirectory, lpdwCurrentDirectory)

hfind = FtpFindFirstFile(hConnection, lpszCurrentDirectory, _
lpFindFileData, IIf(PassiveConnection, _
INTERNET_FLAG_PASSIVE, 0), 0)

If hfind < 0 Then
Range("A1") = lpFindFileData.cFileName
RowCount = 2
Do While lpFindFileData.cFileName < ""

lpFindFileData.cFileName = String(MAX_PATH, 0)
Status = InternetFindNextFile(hfind, lpFindFileData)

Hi Joel,

thank you for setting me on the right track. I could do everything I wanted
with the Shell-command. I got a little stuck on the code I had, but with
some changes, everything works fine now.

Thanks for your time!
Jos


"Joel" schreef in bericht
...
VBA is visual basic application which is embedded ion microsoft office
applications. the is a compiled version of Visual basic that will produce
executable files like .EXE and DLL. It is a seperate production that you
have to buy from microsoft.

You can launch other programs from VBA using a shell application and pass
the programs control information through a commeand line (provided the
aplicattion supports command line inputs).

The issue with running other applicattions is how to control the
application
and how to pass data to/from the application. Some controls are possible
by
using KEY functions. Like in excel you can save a file by pressing Alt-F
and
then pressing S. these arre shortcut keys. If the applicaiton support
shortcut keys then you can run the shortcuts from VBA pretty easily. Othe
rappliocation accept scripting lanuages that on startup you can give the
program a script file from a command line input.

You have to read the manual for the application and see what feature are
support from a command line. Also check the menus for shortcut keys and
the
characters that are underlines on the main menu. Like excel has F
underlined
on the main menu which indicates a shortcut key of Alt-F.


"Jos Vens" wrote:

thanks Joel,

but that's not exactly what I'm looking for. Let's say the external
application is written by a third party, so there's no possibilty to
convert
it to a dll-file. Code should look like the excel example:

Why is excel "known" by vba? and other programs not??? Can you put a path
into the object reference???

Set xlExcel = New Excel.Application
xlExcel.Visible = True
xlExcel.Workbooks.Open vFile, False, True

Thanks anyway,
Jos


"Joel" schreef in bericht
...
You can make references to DLL. Most executables can be built either
as a
DLL or an EXE. Look at the library defininitions like this one

Public Declare Function FtpSetCurrentDirectory Lib "wininet.dll" Alias
"FtpSetCurrentDirectoryA" _
(ByVal hFtpSession As Long, ByVal lpszDirectory As String) As
Boolean


Here is an FTP application.

Const MAX_PATH = 260


' Set Constants
Const FTP_TRANSFER_TYPE_ASCII = &H1
Const FTP_TRANSFER_TYPE_BINARY = &H2
Const INTERNET_DEFAULT_FTP_PORT = 21
Const INTERNET_SERVICE_FTP = 1
Const INTERNET_FLAG_PASSIVE = &H8000000
Const GENERIC_WRITE = &H40000000
Const BUFFER_SIZE = 100
Const PassiveConnection As Boolean = True


Type WIN32_FIND_DATA
dwFileAttributes As Long
ftCreationTime As Currency
ftLastAccessTime As Currency
ftLastWriteTime As Currency
nFileSizeHigh As Long
nFileSizeLow As Long
dwReserved0 As Long
dwReserved1 As Long
cFileName As String * MAX_PATH
cAlternate As String * 14
End Type

' Declare wininet.dll API Functions
Public Declare Function FtpSetCurrentDirectory Lib "wininet.dll" Alias
"FtpSetCurrentDirectoryA" _
(ByVal hFtpSession As Long, ByVal lpszDirectory As String) As
Boolean

Public Declare Function FtpGetCurrentDirectory Lib "wininet.dll" Alias
"FtpGetCurrentDirectoryA" _
(ByVal hFtpSession As Long, ByVal lpszCurrentDirectory As String,
lpdwCurrentDirectory As Long) As Boolean

Public Declare Function InternetWriteFile Lib "wininet.dll" _
(ByVal hFile As Long, ByRef sBuffer As Byte, ByVal lNumBytesToWite As
Long, _
dwNumberOfBytesWritten As Long) As Integer

Public Declare Function FtpOpenFile Lib "wininet.dll" Alias
"FtpOpenFileA"
_
(ByVal hFtpSession As Long, ByVal sBuff As String, ByVal Access As
Long,
ByVal Flags As Long, ByVal Context As Long) As Long

Public Declare Function FtpPutFile Lib "wininet.dll" Alias
"FtpPutFileA" _
(ByVal hFtpSession As Long, ByVal lpszLocalFile As String, _
ByVal lpszRemoteFile As String, _
ByVal dwFlags As Long, ByVal dwContext As Long) As Boolean

Public Declare Function FtpDeleteFile Lib "wininet.dll" _
Alias "FtpDeleteFileA" (ByVal hFtpSession As Long, _
ByVal lpszFileName As String) As Boolean
Public Declare Function InternetCloseHandle Lib "wininet.dll" _
(ByVal hInet As Long) As Long

Public Declare Function InternetOpen Lib "wininet.dll" Alias
"InternetOpenA" _
(ByVal sAgent As String, ByVal lAccessType As Long, ByVal sProxyName As
String, _
ByVal sProxyBypass As String, ByVal lFlags As Long) As Long

Public Declare Function InternetConnect Lib "wininet.dll" Alias
"InternetConnectA" _
(ByVal hInternetSession As Long, ByVal sServerName As String, ByVal
nServerPort As Integer, _
ByVal sUsername As String, ByVal sPassword As String, ByVal lService As
Long, _
ByVal lFlags As Long, ByVal lContext As Long) As Long


Public Declare Function FtpGetFile Lib "wininet.dll" Alias
"FtpGetFileA" _
(ByVal hFtpSession As Long, ByVal lpszRemoteFile As String, _
ByVal lpszNewFile As String, ByVal fFailIfExists As Boolean, ByVal
dwFlagsAndAttributes As Long, _
ByVal dwFlags As Long, ByVal dwContext As Long) As Boolean

Declare Function InternetGetLastResponseInfo Lib "wininet.dll" _
Alias "InternetGetLastResponseInfoA" _
(ByRef lpdwError As Long, _
ByVal lpszErrorBuffer As String, _
ByRef lpdwErrorBufferLength As Long) As Boolean

Public Declare Function FtpFindFirstFile Lib "wininet.dll" Alias
"FtpFindFirstFileA" _
(ByVal hInternetSession As Long, ByVal lpszSearchFile As String, _
ByRef lpFindFileData As WIN32_FIND_DATA, ByVal dwFlags As Long, _
ByVal dwContext As Long) As Long


Public Declare Function InternetFindNextFile Lib "wininet.dll" Alias
"InternetFindNextFileA" _
(ByVal hInternetSession As Long, ByRef lpvFindData As WIN32_FIND_DATA)
As
Long



Function FTPFile(ByVal HostName As String, _
ByVal UserName As String, _
ByVal Password As String, _
ByVal LocalFileName As String, _
ByVal RemoteFileName As String, _
ByVal sDir As String, _
ByVal sMode As String) As Boolean

On Error GoTo Err_Function

' Declare variables
Dim hConnection, hOpen, hFile As Long ' Used For Handles
Dim iSize As Long ' Size of file for upload
Dim Retval As Variant ' Used for progress meter
Dim iWritten As Long ' Used by InternetWriteFile to report bytes
uploaded
Dim iLoop As Long ' Loop for uploading chuncks
Dim iFile As Integer ' Used for Local file handle
Dim FileData(BUFFER_SIZE - 1) As Byte ' buffer array of BUFFER_SIZE
(100)
elements 0 to 99

' Open Internet Connecion
hOpen = InternetOpen("FTP", 1, "", vbNullString, 0)

' Connect to FTP
hConnection = InternetConnect(hOpen, HostName,
INTERNET_DEFAULT_FTP_PORT,
UserName, Password, INTERNET_SERVICE_FTP, IIf(PassiveConnection,
INTERNET_FLAG_PASSIVE, 0), 0)

' Change Directory
Call FtpSetCurrentDirectory(hConnection, sDir)

' Open Remote File
hFile = FtpOpenFile(hConnection, RemoteFileName, GENERIC_WRITE,
IIf(sMode
=
"Binary", FTP_TRANSFER_TYPE_BINARY, FTP_TRANSFER_TYPE_ASCII), 0)

' Check for successfull file handle
If hFile = 0 Then
MsgBox "Internet - Failed!"
ShowError
FTPFile = False
GoTo Exit_Function
End If

' Set Upload Flag to True
FTPFile = True

' Get next file handle number
iFile = FreeFile

' Open local file
Open LocalFileName For Binary Access Read As iFile

' Set file size
iSize = LOF(iFile)

' Iinitialise progress meter
Retval = SysCmd(acSysCmdInitMeter, "Uploading File (" & RemoteFileName
&
")", iSize / 1000)

' Loop file size
For iLoop = 1 To iSize \ BUFFER_SIZE

' Update progress meter
Retval = SysCmd(acSysCmdUpdateMeter, (BUFFER_SIZE * iLoop) / 1000)

'Get file data
Get iFile, , FileData

' Write chunk to FTP checking for success
If InternetWriteFile(hFile, FileData(0), BUFFER_SIZE, iWritten) = 0
Then
MsgBox "Upload - Failed!"
ShowError
FTPFile = False
GoTo Exit_Function
Else
' Check buffer was written
If iWritten < BUFFER_SIZE Then
MsgBox "Upload - Failed!"
ShowError
FTPFile = False
GoTo Exit_Function
End If
End If

Next iLoop

' Handle remainder using MOD

' Update progress meter
Retval = SysCmd(acSysCmdUpdateMeter, iSize / 1000)

' Get file data
Get iFile, , FileData

' Write remainder to FTP checking for success
If InternetWriteFile(hFile, FileData(0), iSize Mod BUFFER_SIZE,
iWritten) = 0 Then
MsgBox "Upload - Failed!"
ShowError
FTPFile = False
GoTo Exit_Function
Else
' Check buffer was written
If iWritten < iSize Mod BUFFER_SIZE Then
MsgBox "Upload - Failed!"
ShowError
FTPFile = False
GoTo Exit_Function
End If
End If

Exit_Function:

' remove progress meter
Retval = SysCmd(acSysCmdRemoveMeter)

'close remote file
Call InternetCloseHandle(hFile)

'close local file
Close iFile

' Close Internet Connection
Call InternetCloseHandle(hOpen)
Call InternetCloseHandle(hConnection)

Exit Function

Err_Function:
MsgBox "Error in FTPFile : " & Err.Description
GoTo Exit_Function

End Function

Function FTPGetDir(ByVal HostName As String, ByVal User As String, _
ByVal PassWd As String, ByVal Folder As String)

' Declare variables
Dim hConnection, hOpen As Long ' Used For Handles
Dim lpszCurrentDirectory As String
Dim lpdwCurrentDirectory As Long
Dim lpFindFileData As WIN32_FIND_DATA
Dim hfind As Long

lpszCurrentDirectory = String(1024, Chr(0))
lpdwCurrentDirectory = 1024

' Open Internet Connecion
hOpen = InternetOpen("FTP", 1, "", vbNullString, 0)

' Connect to FTP
hConnection = InternetConnect(hOpen, HostName,
INTERNET_DEFAULT_FTP_PORT,
UserName, Password, INTERNET_SERVICE_FTP, IIf(PassiveConnection,
INTERNET_FLAG_PASSIVE, 0), 0)


Status = FtpGetCurrentDirectory(hConnection, _
lpszCurrentDirectory, lpdwCurrentDirectory)

hfind = FtpFindFirstFile(hConnection, lpszCurrentDirectory, _
lpFindFileData, IIf(PassiveConnection, _
INTERNET_FLAG_PASSIVE, 0), 0)

If hfind < 0 Then
Range("A1") = lpFindFileData.cFileName
RowCount = 2
Do While lpFindFileData.cFileName < ""

lpFindFileData.cFileName = String(MAX_PATH, 0)
Status = InternetFindNextFile(hfind, lpFindFileData)

I had a very similar question asked the same day you did. The shell command
in excel doesn't operate in the user environment in just executes the EXE or
BAT file. Yo have to provide the entire path. The shell command doesn't
parse tthe PATH property in windows to locate the executable file. I just
wrote my own parser to locate the file. See the code below

Sub FindMyFile()

Filename = "excel.exe"
Path = Environ("Path")
splitPath = Split(Path, ";")
Found = False
For Each Folder In splitPath
FName = Dir(Folder & "\" & Filename)
If FName < "" Then
MsgBox ("File found in Folder : " & Folder)
Found = True
End If

Next Folder

End Sub



"Jos Vens" wrote:

Hi Joel,

thank you for setting me on the right track. I could do everything I wanted
with the Shell-command. I got a little stuck on the code I had, but with
some changes, everything works fine now.

Thanks for your time!
Jos


"Joel" schreef in bericht
...
VBA is visual basic application which is embedded ion microsoft office
applications. the is a compiled version of Visual basic that will produce
executable files like .EXE and DLL. It is a seperate production that you
have to buy from microsoft.

You can launch other programs from VBA using a shell application and pass
the programs control information through a commeand line (provided the
aplicattion supports command line inputs).

The issue with running other applicattions is how to control the
application
and how to pass data to/from the application. Some controls are possible
by
using KEY functions. Like in excel you can save a file by pressing Alt-F
and
then pressing S. these arre shortcut keys. If the applicaiton support
shortcut keys then you can run the shortcuts from VBA pretty easily. Othe
rappliocation accept scripting lanuages that on startup you can give the
program a script file from a command line input.

You have to read the manual for the application and see what feature are
support from a command line. Also check the menus for shortcut keys and
the
characters that are underlines on the main menu. Like excel has F
underlined
on the main menu which indicates a shortcut key of Alt-F.


"Jos Vens" wrote:

thanks Joel,

but that's not exactly what I'm looking for. Let's say the external
application is written by a third party, so there's no possibilty to
convert
it to a dll-file. Code should look like the excel example:

Why is excel "known" by vba? and other programs not??? Can you put a path
into the object reference???

Set xlExcel = New Excel.Application
xlExcel.Visible = True
xlExcel.Workbooks.Open vFile, False, True

Thanks anyway,
Jos


"Joel" schreef in bericht
...
You can make references to DLL. Most executables can be built either
as a
DLL or an EXE. Look at the library defininitions like this one

Public Declare Function FtpSetCurrentDirectory Lib "wininet.dll" Alias
"FtpSetCurrentDirectoryA" _
(ByVal hFtpSession As Long, ByVal lpszDirectory As String) As
Boolean


Here is an FTP application.

Const MAX_PATH = 260


' Set Constants
Const FTP_TRANSFER_TYPE_ASCII = &H1
Const FTP_TRANSFER_TYPE_BINARY = &H2
Const INTERNET_DEFAULT_FTP_PORT = 21
Const INTERNET_SERVICE_FTP = 1
Const INTERNET_FLAG_PASSIVE = &H8000000
Const GENERIC_WRITE = &H40000000
Const BUFFER_SIZE = 100
Const PassiveConnection As Boolean = True


Type WIN32_FIND_DATA
dwFileAttributes As Long
ftCreationTime As Currency
ftLastAccessTime As Currency
ftLastWriteTime As Currency
nFileSizeHigh As Long
nFileSizeLow As Long
dwReserved0 As Long
dwReserved1 As Long
cFileName As String * MAX_PATH
cAlternate As String * 14
End Type

' Declare wininet.dll API Functions
Public Declare Function FtpSetCurrentDirectory Lib "wininet.dll" Alias
"FtpSetCurrentDirectoryA" _
(ByVal hFtpSession As Long, ByVal lpszDirectory As String) As
Boolean

Public Declare Function FtpGetCurrentDirectory Lib "wininet.dll" Alias
"FtpGetCurrentDirectoryA" _
(ByVal hFtpSession As Long, ByVal lpszCurrentDirectory As String,
lpdwCurrentDirectory As Long) As Boolean

Public Declare Function InternetWriteFile Lib "wininet.dll" _
(ByVal hFile As Long, ByRef sBuffer As Byte, ByVal lNumBytesToWite As
Long, _
dwNumberOfBytesWritten As Long) As Integer

Public Declare Function FtpOpenFile Lib "wininet.dll" Alias
"FtpOpenFileA"
_
(ByVal hFtpSession As Long, ByVal sBuff As String, ByVal Access As
Long,
ByVal Flags As Long, ByVal Context As Long) As Long

Public Declare Function FtpPutFile Lib "wininet.dll" Alias
"FtpPutFileA" _
(ByVal hFtpSession As Long, ByVal lpszLocalFile As String, _
ByVal lpszRemoteFile As String, _
ByVal dwFlags As Long, ByVal dwContext As Long) As Boolean

Public Declare Function FtpDeleteFile Lib "wininet.dll" _
Alias "FtpDeleteFileA" (ByVal hFtpSession As Long, _
ByVal lpszFileName As String) As Boolean
Public Declare Function InternetCloseHandle Lib "wininet.dll" _
(ByVal hInet As Long) As Long

Public Declare Function InternetOpen Lib "wininet.dll" Alias
"InternetOpenA" _
(ByVal sAgent As String, ByVal lAccessType As Long, ByVal sProxyName As
String, _
ByVal sProxyBypass As String, ByVal lFlags As Long) As Long

Public Declare Function InternetConnect Lib "wininet.dll" Alias
"InternetConnectA" _
(ByVal hInternetSession As Long, ByVal sServerName As String, ByVal
nServerPort As Integer, _
ByVal sUsername As String, ByVal sPassword As String, ByVal lService As
Long, _
ByVal lFlags As Long, ByVal lContext As Long) As Long


Public Declare Function FtpGetFile Lib "wininet.dll" Alias
"FtpGetFileA" _
(ByVal hFtpSession As Long, ByVal lpszRemoteFile As String, _
ByVal lpszNewFile As String, ByVal fFailIfExists As Boolean, ByVal
dwFlagsAndAttributes As Long, _
ByVal dwFlags As Long, ByVal dwContext As Long) As Boolean

Declare Function InternetGetLastResponseInfo Lib "wininet.dll" _
Alias "InternetGetLastResponseInfoA" _
(ByRef lpdwError As Long, _
ByVal lpszErrorBuffer As String, _
ByRef lpdwErrorBufferLength As Long) As Boolean

Public Declare Function FtpFindFirstFile Lib "wininet.dll" Alias
"FtpFindFirstFileA" _
(ByVal hInternetSession As Long, ByVal lpszSearchFile As String, _
ByRef lpFindFileData As WIN32_FIND_DATA, ByVal dwFlags As Long, _
ByVal dwContext As Long) As Long


Public Declare Function InternetFindNextFile Lib "wininet.dll" Alias
"InternetFindNextFileA" _
(ByVal hInternetSession As Long, ByRef lpvFindData As WIN32_FIND_DATA)
As
Long



Function FTPFile(ByVal HostName As String, _
ByVal UserName As String, _
ByVal Password As String, _
ByVal LocalFileName As String, _
ByVal RemoteFileName As String, _
ByVal sDir As String, _
ByVal sMode As String) As Boolean

On Error GoTo Err_Function

' Declare variables
Dim hConnection, hOpen, hFile As Long ' Used For Handles
Dim iSize As Long ' Size of file for upload
Dim Retval As Variant ' Used for progress meter
Dim iWritten As Long ' Used by InternetWriteFile to report bytes
uploaded
Dim iLoop As Long ' Loop for uploading chuncks
Dim iFile As Integer ' Used for Local file handle
Dim FileData(BUFFER_SIZE - 1) As Byte ' buffer array of BUFFER_SIZE
(100)
elements 0 to 99

' Open Internet Connecion
hOpen = InternetOpen("FTP", 1, "", vbNullString, 0)

' Connect to FTP
hConnection = InternetConnect(hOpen, HostName,
INTERNET_DEFAULT_FTP_PORT,
UserName, Password, INTERNET_SERVICE_FTP, IIf(PassiveConnection,
INTERNET_FLAG_PASSIVE, 0), 0)

' Change Directory
Call FtpSetCurrentDirectory(hConnection, sDir)

' Open Remote File
hFile = FtpOpenFile(hConnection, RemoteFileName, GENERIC_WRITE,
IIf(sMode
=
"Binary", FTP_TRANSFER_TYPE_BINARY, FTP_TRANSFER_TYPE_ASCII), 0)

' Check for successfull file handle
If hFile = 0 Then
MsgBox "Internet - Failed!"
ShowError
FTPFile = False
GoTo Exit_Function
End If

' Set Upload Flag to True
FTPFile = True

' Get next file handle number
iFile = FreeFile

' Open local file
Open LocalFileName For Binary Access Read As iFile

' Set file size
iSize = LOF(iFile)

' Iinitialise progress meter
Retval = SysCmd(acSysCmdInitMeter, "Uploading File (" & RemoteFileName
&
")", iSize / 1000)

' Loop file size
For iLoop = 1 To iSize \ BUFFER_SIZE

' Update progress meter
Retval = SysCmd(acSysCmdUpdateMeter, (BUFFER_SIZE * iLoop) / 1000)

'Get file data
Get iFile, , FileData

' Write chunk to FTP checking for success
If InternetWriteFile(hFile, FileData(0), BUFFER_SIZE, iWritten) = 0
Then
MsgBox "Upload - Failed!"
ShowError
FTPFile = False
GoTo Exit_Function
Else
' Check buffer was written
If iWritten < BUFFER_SIZE Then
MsgBox "Upload - Failed!"
ShowError
FTPFile = False
GoTo Exit_Function
End If
End If

Next iLoop

' Handle remainder using MOD

' Update progress meter
Retval = SysCmd(acSysCmdUpdateMeter, iSize / 1000)

' Get file data
Get iFile, , FileData

' Write remainder to FTP checking for success
If InternetWriteFile(hFile, FileData(0), iSize Mod BUFFER_SIZE,
iWritten) = 0 Then
MsgBox "Upload - Failed!"
ShowError
FTPFile = False
GoTo Exit_Function
Else
' Check buffer was written
If iWritten < iSize Mod BUFFER_SIZE Then
MsgBox "Upload - Failed!"
ShowError
FTPFile = False
GoTo Exit_Function
End If
End If

Exit_Function:

' remove progress meter