Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sample VBA code to open an Visual-Studio-2008-created VBA DLL
2003, 2007
Possible for someone to provide a few examples written in Excel VBA, which call a DLL (Excel VBA-compiled in VS 2008 Visual Basic)? In short, I have the myExcelVBA.DLL but cannot find VBA examples to "call" it properly. How/Where do I declare the libraries necessary? (Assume I need to use all libraries/references used to run the VBA from the VBA editor window. TIA EagleOne |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sample VBA code to open an Visual-Studio-2008-created VBA DLL
Hre is code the call the FTP dll. You need to Alis which is the entry point
into the dll. the entry poit for basic is usally the function nmae (FTP) with an "A" at the end. 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 " wrote: 2003, 2007 Possible for someone to provide a few examples written in Excel VBA, which call a DLL (Excel VBA-compiled in VS 2008 Visual Basic)? In short, I have the myExcelVBA.DLL but cannot find VBA examples to "call" it properly. How/Where do I declare the libraries necessary? (Assume I need to use all libraries/references used to run the VBA from the VBA editor window. TIA EagleOne |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sample VBA code to open an Visual-Studio-2008-created VBA DLL
Interesting Joel.
So, if I have current VBA code that performs a series worksheet procedures called FixSheetsDll() is the appropriate method: (BTW there are no variables or Constants passing into/out of the DLL.) Private Declare Sub FixSheetsDll Lib "????" () 'What do I use as the Lib "????" Sub CallMyDll() Call FixSheetsDll() End Sub EagleOne Joel wrote: Hre is code the call the FTP dll. You need to Alis which is the entry point into the dll. the entry poit for basic is usally the function nmae (FTP) with an "A" at the end. 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 " wrote: 2003, 2007 Possible for someone to provide a few examples written in Excel VBA, which call a DLL (Excel VBA-compiled in VS 2008 Visual Basic)? In short, I have the myExcelVBA.DLL but cannot find VBA examples to "call" it properly. How/Where do I declare the libraries necessary? (Assume I need to use all libraries/references used to run the VBA from the VBA editor window. TIA EagleOne |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sample VBA code to open an Visual-Studio-2008-created VBA DLL
Would someone confirm the correct syntax if the Directory is "C:\MiscDllFiles"
Private Declare Sub FixSheetsDll Lib "C:\MiscDllFiles" Sub CallMyDll() Call FixSheetsDll() End Sub Joel wrote: Hre is code the call the FTP dll. You need to Alis which is the entry point into the dll. the entry poit for basic is usally the function nmae (FTP) with an "A" at the end. 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 " wrote: 2003, 2007 Possible for someone to provide a few examples written in Excel VBA, which call a DLL (Excel VBA-compiled in VS 2008 Visual Basic)? In short, I have the myExcelVBA.DLL but cannot find VBA examples to "call" it properly. How/Where do I declare the libraries necessary? (Assume I need to use all libraries/references used to run the VBA from the VBA editor window. TIA EagleOne |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sample VBA code to open an Visual-Studio-2008-created VBA DLL
which call a DLL (Excel VBA-compiled in VS 2008 Visual Basic) What, exactly, does that mean? Did you create an Excel 2007 code-behind workbook or did you create Class Library DLL? If you're referring to a DLL created as a class library, that assembly must have been created as a COM visible assembly. That allows COM-based applications (VB, VBA, VB6, etc) to call its functions. If it isn't COM-visible, you won't be able to call its functions. You set this property from the Assembly Information dialog on the Application page of the My Project member of your project. Once you have compiled the DLL as COM-visible, open the VBA editor, go to the Tools menu, choose References and then Browse. Locate the typelib file for the DLL (in the same folder as the DLL itself) and load that typelib. Use the typelib file (assm_name.tlb not assem_name.dll) rather than the DLL file. Once you do that, you can call members of the one or more classes that make up the DLL. For example, if your DLL is named MyExcelDLL.dll and the class that contains the functions is called MyFunctionClass, you can call a function, MyRealFunction with code like Dim MF As MyExcelDLL.MyFunctionClass Set MF = New MyExcelDLL.MyFunctionClass Result = MF.MyRealFunction(1234) With NET dlls, you don't use the Declare statement. That is used only for Win32 DLLs, such as those created with __stdcall in VC++. Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group, 1998 - 2009 Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Fri, 22 May 2009 09:58:38 -0400, wrote: 2003, 2007 Possible for someone to provide a few examples written in Excel VBA, which call a DLL (Excel VBA-compiled in VS 2008 Visual Basic)? In short, I have the myExcelVBA.DLL but cannot find VBA examples to "call" it properly. How/Where do I declare the libraries necessary? (Assume I need to use all libraries/references used to run the VBA from the VBA editor window. TIA EagleOne |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sample VBA code to open an Visual-Studio-2008-created VBA DLL
Chip,
Obviously, this is virgin territory for me. The purpose for the DLL was to compile the VBA code do as to secure same. Therefore, I thought that, in VBA, I could call the DLL to process the compiled VBA code. It appears that it is not that simple - therefore professionals like you exist. That said, I am attempting to piece together a learning moment. I am not sure of: "Use the typelib file (assm_name.tlb not assem_name.dll) rather than the DLL file." Does "assm" mean Assembly? i.e. If I "compiled" a VBA Sub "Test()" (w/s processing proceedure); into a .DLL with VS 2008 Visual Basic Express, how could I execute the VBA code in the DLL with VBA? In short, for security I compiled Test() sub into a DLL. How can I execute, using VBA, the contents of myExcelVBA.DLL? Sorry, if I am not getting it. Hindsight is not available to me now. Thanks EagleOne Chip Pearson wrote: which call a DLL (Excel VBA-compiled in VS 2008 Visual Basic) What, exactly, does that mean? Did you create an Excel 2007 code-behind workbook or did you create Class Library DLL? If you're referring to a DLL created as a class library, that assembly must have been created as a COM visible assembly. That allows COM-based applications (VB, VBA, VB6, etc) to call its functions. If it isn't COM-visible, you won't be able to call its functions. You set this property from the Assembly Information dialog on the Application page of the My Project member of your project. Once you have compiled the DLL as COM-visible, open the VBA editor, go to the Tools menu, choose References and then Browse. Locate the typelib file for the DLL (in the same folder as the DLL itself) and load that typelib. Use the typelib file (assm_name.tlb not assem_name.dll) rather than the DLL file. Once you do that, you can call members of the one or more classes that make up the DLL. For example, if your DLL is named MyExcelDLL.dll and the class that contains the functions is called MyFunctionClass, you can call a function, MyRealFunction with code like Dim MF As MyExcelDLL.MyFunctionClass Set MF = New MyExcelDLL.MyFunctionClass Result = MF.MyRealFunction(1234) With NET dlls, you don't use the Declare statement. That is used only for Win32 DLLs, such as those created with __stdcall in VC++. Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group, 1998 - 2009 Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Fri, 22 May 2009 09:58:38 -0400, wrote: 2003, 2007 Possible for someone to provide a few examples written in Excel VBA, which call a DLL (Excel VBA-compiled in VS 2008 Visual Basic)? In short, I have the myExcelVBA.DLL but cannot find VBA examples to "call" it properly. How/Where do I declare the libraries necessary? (Assume I need to use all libraries/references used to run the VBA from the VBA editor window. TIA EagleOne |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sample VBA code to open an Visual-Studio-2008-created VBA DLL
Forgot to mention that Test() does not pass variables either in/out.but just i.e., formats the
activesheet. Thus it is not a function per se. Really I am trying to learn about VBA and DLLs. I got the VBA lightly down - I am just trying to learn how to protect the code. I would like to have the DLL work in 2003 and 2007 Excel VBA. I believe that the main difference between a DLL and a Com Addin is Installing/Regestering the DLL to be called in Office. I previously thought it was fine to have a DLL called vis VBA. Chip, just shoot me. Chip Pearson wrote: which call a DLL (Excel VBA-compiled in VS 2008 Visual Basic) What, exactly, does that mean? Did you create an Excel 2007 code-behind workbook or did you create Class Library DLL? If you're referring to a DLL created as a class library, that assembly must have been created as a COM visible assembly. That allows COM-based applications (VB, VBA, VB6, etc) to call its functions. If it isn't COM-visible, you won't be able to call its functions. You set this property from the Assembly Information dialog on the Application page of the My Project member of your project. Once you have compiled the DLL as COM-visible, open the VBA editor, go to the Tools menu, choose References and then Browse. Locate the typelib file for the DLL (in the same folder as the DLL itself) and load that typelib. Use the typelib file (assm_name.tlb not assem_name.dll) rather than the DLL file. Once you do that, you can call members of the one or more classes that make up the DLL. For example, if your DLL is named MyExcelDLL.dll and the class that contains the functions is called MyFunctionClass, you can call a function, MyRealFunction with code like Dim MF As MyExcelDLL.MyFunctionClass Set MF = New MyExcelDLL.MyFunctionClass Result = MF.MyRealFunction(1234) With NET dlls, you don't use the Declare statement. That is used only for Win32 DLLs, such as those created with __stdcall in VC++. Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group, 1998 - 2009 Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Fri, 22 May 2009 09:58:38 -0400, wrote: 2003, 2007 Possible for someone to provide a few examples written in Excel VBA, which call a DLL (Excel VBA-compiled in VS 2008 Visual Basic)? In short, I have the myExcelVBA.DLL but cannot find VBA examples to "call" it properly. How/Where do I declare the libraries necessary? (Assume I need to use all libraries/references used to run the VBA from the VBA editor window. TIA EagleOne |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sample VBA code to open an Visual-Studio-2008-created VBA DLL
Chip, I found the following but do not fully comprehend its impact. Imports System Imports System.Runtime.InteropServices <Assembly: ComVisibleAttribute(False) Namespace InteroperabilityLibrary <ComVisibleAttribute(False) _ Public Class BaseClass Sub SomeSub(valueOne As Integer) End Sub End Class ' This class violates the rule. <ComVisibleAttribute(True) _ Public Class DerivedClass Inherits BaseClass Sub AnotherSub(valueOne As Integer, valueTwo As Integer) End Sub End Class End Namespace I learn best if I have a "Template" that can be modified. One of my challenges is that I never had assess to VB6. Do you have a template that I could review so that I can get perspective to the task? I am aware of the following on your site: http://www.cpearson.com/excel/workbooktimebomb.aspx http://www.cpearson.com/excel/creatingcomaddin.aspx http://www.cpearson.com/Excel/creati...nctionlib.aspx Since I do not have any .NET nor VB6 (just VS 2008 Visual Basic Express), my mind can not make the leap. EagleOne Chip Pearson wrote: which call a DLL (Excel VBA-compiled in VS 2008 Visual Basic) What, exactly, does that mean? Did you create an Excel 2007 code-behind workbook or did you create Class Library DLL? If you're referring to a DLL created as a class library, that assembly must have been created as a COM visible assembly. That allows COM-based applications (VB, VBA, VB6, etc) to call its functions. If it isn't COM-visible, you won't be able to call its functions. You set this property from the Assembly Information dialog on the Application page of the My Project member of your project. Once you have compiled the DLL as COM-visible, open the VBA editor, go to the Tools menu, choose References and then Browse. Locate the typelib file for the DLL (in the same folder as the DLL itself) and load that typelib. Use the typelib file (assm_name.tlb not assem_name.dll) rather than the DLL file. Once you do that, you can call members of the one or more classes that make up the DLL. For example, if your DLL is named MyExcelDLL.dll and the class that contains the functions is called MyFunctionClass, you can call a function, MyRealFunction with code like Dim MF As MyExcelDLL.MyFunctionClass Set MF = New MyExcelDLL.MyFunctionClass Result = MF.MyRealFunction(1234) With NET dlls, you don't use the Declare statement. That is used only for Win32 DLLs, such as those created with __stdcall in VC++. Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group, 1998 - 2009 Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Fri, 22 May 2009 09:58:38 -0400, wrote: 2003, 2007 Possible for someone to provide a few examples written in Excel VBA, which call a DLL (Excel VBA-compiled in VS 2008 Visual Basic)? In short, I have the myExcelVBA.DLL but cannot find VBA examples to "call" it properly. How/Where do I declare the libraries necessary? (Assume I need to use all libraries/references used to run the VBA from the VBA editor window. TIA EagleOne |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sample VBA code to open an Visual-Studio-2008-created VBA DLL
I meant to add that if you want a copy of the entire VB.NET project, send me an email at and I'll zip up a copy for you. Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group, 1998 - 2009 Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Fri, 22 May 2009 14:08:58 -0400, wrote: Chip, I found the following but do not fully comprehend its impact. Imports System Imports System.Runtime.InteropServices <Assembly: ComVisibleAttribute(False) Namespace InteroperabilityLibrary <ComVisibleAttribute(False) _ Public Class BaseClass Sub SomeSub(valueOne As Integer) End Sub End Class ' This class violates the rule. <ComVisibleAttribute(True) _ Public Class DerivedClass Inherits BaseClass Sub AnotherSub(valueOne As Integer, valueTwo As Integer) End Sub End Class End Namespace I learn best if I have a "Template" that can be modified. One of my challenges is that I never had assess to VB6. Do you have a template that I could review so that I can get perspective to the task? I am aware of the following on your site: http://www.cpearson.com/excel/workbooktimebomb.aspx http://www.cpearson.com/excel/creatingcomaddin.aspx http://www.cpearson.com/Excel/creati...nctionlib.aspx Since I do not have any .NET nor VB6 (just VS 2008 Visual Basic Express), my mind can not make the leap. EagleOne Chip Pearson wrote: which call a DLL (Excel VBA-compiled in VS 2008 Visual Basic) What, exactly, does that mean? Did you create an Excel 2007 code-behind workbook or did you create Class Library DLL? If you're referring to a DLL created as a class library, that assembly must have been created as a COM visible assembly. That allows COM-based applications (VB, VBA, VB6, etc) to call its functions. If it isn't COM-visible, you won't be able to call its functions. You set this property from the Assembly Information dialog on the Application page of the My Project member of your project. Once you have compiled the DLL as COM-visible, open the VBA editor, go to the Tools menu, choose References and then Browse. Locate the typelib file for the DLL (in the same folder as the DLL itself) and load that typelib. Use the typelib file (assm_name.tlb not assem_name.dll) rather than the DLL file. Once you do that, you can call members of the one or more classes that make up the DLL. For example, if your DLL is named MyExcelDLL.dll and the class that contains the functions is called MyFunctionClass, you can call a function, MyRealFunction with code like Dim MF As MyExcelDLL.MyFunctionClass Set MF = New MyExcelDLL.MyFunctionClass Result = MF.MyRealFunction(1234) With NET dlls, you don't use the Declare statement. That is used only for Win32 DLLs, such as those created with __stdcall in VC++. Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group, 1998 - 2009 Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Fri, 22 May 2009 09:58:38 -0400, wrote: 2003, 2007 Possible for someone to provide a few examples written in Excel VBA, which call a DLL (Excel VBA-compiled in VS 2008 Visual Basic)? In short, I have the myExcelVBA.DLL but cannot find VBA examples to "call" it properly. How/Where do I declare the libraries necessary? (Assume I need to use all libraries/references used to run the VBA from the VBA editor window. TIA EagleOne |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Is Visual Basic the same as Visual Studio 2008? | Excel Worksheet Functions | |||
Working with Excel via Visual Studio 2008 | Excel Programming | |||
Working with Excel via Visual Studio 2008 | Excel Programming | |||
browse for a .xls file then open in visual studio 2005 express edi | Excel Programming | |||
VC7.1++ Excel add-in sample code? Visual Studio 2003 | Excel Programming |