Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to catch the workbook names...
Hi List,
I am using OpenDialog box to let user open more than one Excel file at the same time. How can I catch the file names user choosed? I tried the function Application.GetOpenFilename("Excel Files (*.xls), *.xls"). But first, it didn't open any file, and secondly, it only allows user to choose one at a time. Any help will be appreciated. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to catch the workbook names...
LJi,
This is directly out of VBA Help Sub UseFileDialogOpen() Dim lngCount As Long ' Open the file dialog With Application.FileDialog(msoFileDialogOpen) .AllowMultiSelect = True .Show ' Display paths of each file selected For lngCount = 1 To .SelectedItems.Count MsgBox .SelectedItems(lngCount) Next lngCount End With End Sub The Help files are at times helpfull :) Ben -- When you lose your mind, you free your life. "LJi" wrote: Hi List, I am using OpenDialog box to let user open more than one Excel file at the same time. How can I catch the file names user choosed? I tried the function Application.GetOpenFilename("Excel Files (*.xls), *.xls"). But first, it didn't open any file, and secondly, it only allows user to choose one at a time. Any help will be appreciated. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to catch the workbook names...
GetOpenFilename doesn't support multiple files. Ben's solution requires XP
or above. If you have 2000 or below, post back, I have an API solution. -- HTH Bob Phillips "LJi" wrote in message ups.com... Hi List, I am using OpenDialog box to let user open more than one Excel file at the same time. How can I catch the file names user choosed? I tried the function Application.GetOpenFilename("Excel Files (*.xls), *.xls"). But first, it didn't open any file, and secondly, it only allows user to choose one at a time. Any help will be appreciated. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to catch the workbook names...
I am working on 2000.
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to catch the workbook names...
that's not entirely true instead of using the constant msofiledialogopen you
can use the value I believe it's 1 (I have a program that runs on both 2003 / and 2000 office so found that out the hard way.) Ben -- When you lose your mind, you free your life. "LJi" wrote: I am working on 2000. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to catch the workbook names...
Here is the code.
First a test routine Sub GetFiles() Dim cFileOpen As clsGetOpenFileName Dim i As Long Set cFileOpen = New clsGetOpenFileName With cFileOpen .FileName = "*.xls" .FileType = "Excel Files" .DialogTitle = "Class GetOpenFileName Demo" .MultiFile = "Y" .SelectFile If .SelectedFiles.Count 0 Then For i = 1 To .SelectedFiles.Count MsgBox (.SelectedFiles(i)) Next i End If End With Set cFileOpen = Nothing End Sub and then the working code. Put this in a class module and call it clsGetOpenFileName, Option Explicit '-----------------------------*------------------------------*-------------- ' Win32 API Declarations '-----------------------------*------------------------------*-------------- Private Declare Function GetOpenFileName Lib "comdlg32.dll" _ Alias "GetOpenFileNameA" _ (pOpenfilename As OPENFILENAME) As Long Private Declare Function GetSaveFileName Lib "comdlg32.dll" _ Alias "GetSaveFileNameA" _ (pOpenfilename As OPENFILENAME) As Long Private Declare Function GetShortPathName Lib "kernel32" _ Alias "GetShortPathNameA" _ (ByVal lpszLongPath As String, _ ByVal lpszShortPath As String, _ ByVal cchBuffer As Long) As Long Private Type OPENFILENAME nStructSize As Long hWndOwner As Long hInstance As Long sFilter As String sCustomFilter As String nMaxCustFilter As Long nFilterIndex As Long sFile As String nMaxFile As Long sFileTitle As String nMaxTitle As Long sInitialDir As String sDialogTitle As String flags As Long nFileOffset As Integer nFileExtension As Integer sDefFileExt As String nCustData As Long fnHook As Long sTemplateName As String End Type '-----------------------------*------------------------------*-------------- ' Private Variables '-----------------------------*------------------------------*-------------- Private OFN As OPENFILENAME Private sFileType As String 'Type of file narrative Private sFileName As String 'Filename string to restrict list Private sReadOnly As String 'Y/N flag Private sMultiFile As String 'Allow selection of multiple files Private sTitle As String 'Title in file dialog box '-----------------------------*------------------------------*-------------- ' Private Constants '-----------------------------*------------------------------*-------------- Private Const OFN_ALLOWMULTISELECT As Long = &H200 Private Const OFN_CREATEPROMPT As Long = &H2000 Private Const OFN_ENABLEHOOK As Long = &H20 Private Const OFN_ENABLETEMPLATE As Long = &H40 Private Const OFN_ENABLETEMPLATEHANDLE As Long = &H80 Private Const OFN_EXPLORER As Long = &H80000 Private Const OFN_EXTENSIONDIFFERENT As Long = &H400 Private Const OFN_FILEMUSTEXIST As Long = &H1000 Private Const OFN_HIDEREADONLY As Long = &H4 Private Const OFN_LONGNAMES As Long = &H200000 Private Const OFN_NOCHANGEDIR As Long = &H8 Private Const OFN_NODEREFERENCELINKS As Long = &H100000 Private Const OFN_NOLONGNAMES As Long = &H40000 Private Const OFN_NONETWORKBUTTON As Long = &H20000 Private Const OFN_NOREADONLYRETURN As Long = &H8000& '*see comments Private Const OFN_NOTESTFILECREATE As Long = &H10000 Private Const OFN_NOVALIDATE As Long = &H100 Private Const OFN_OVERWRITEPROMPT As Long = &H2 Private Const OFN_PATHMUSTEXIST As Long = &H800 Private Const OFN_READONLY As Long = &H1 Private Const OFN_SHAREAWARE As Long = &H4000 Private Const OFN_SHAREFALLTHROUGH As Long = 2 Private Const OFN_SHAREWARN As Long = 0 Private Const OFN_SHARENOWARN As Long = 1 Private Const OFN_SHOWHELP As Long = &H10 Private Const OFS_MAXPATHNAME As Long = 260 'OFS_FILE_OPEN_FLAGS and OFS_FILE_SAVE_FLAGS below are mine to save long 'statements; they're not a standard Win32 type. Private Const OFS_FILE_OPEN_FLAGS = OFN_EXPLORER Or _ OFN_LONGNAMES Or _ OFN_CREATEPROMPT Or _ OFN_NODEREFERENCELINKS Private Const OFS_FILE_SAVE_FLAGS = OFN_EXPLORER Or _ OFN_LONGNAMES Or _ OFN_OVERWRITEPROMPT Or _ OFN_HIDEREADONLY '-----------------------------*------------------------------*-- ' Class Properties '-----------------------------*------------------------------*-- Public SelectedFiles As New Collection Public Property Let FileType(FileType As String) sFileType = FileType End Property Public Property Let FileName(FileName As String) sFileName = FileName End Property Public Property Let MultiFile(MultiFile As String) sMultiFile = UCase(MultiFile) End Property Public Property Let DialogTitle(Title As String) sTitle = Title End Property Public Property Get ReadOnly() ReadOnly = sReadOnly End Property '-----------------------------*------------------------------*-- ' Class Methods '-----------------------------*------------------------------*-- Public Function SelectFile() As Long '-----------------------------*------------------------------*-- Dim i Dim sFilters As String Dim sBuffer As String Dim sLongname As String Dim sShortname As String If ValidInput Then 'create a string of filters for the dialog sFilters = sFileType & vbNullChar & vbNullChar With OFN .nStructSize = Len(OFN) 'Size of the OFN structure .sFilter = sFilters 'Filters for dropdown combo .nFilterIndex = 1 'Index to the initial filter 'Default filename, plus additional padding for user 's final ' selection(s). Must be double-null terminated .sFile = sFileName & Space$(1024) & vbNullChar & vbNullChar .nMaxFile = Len(.sFile) 'the size of the buffer 'Default if file has no extension .sDefFileExt = sFileName & vbNullChar & vbNullChar 'Make space for file title if single selection made, ' double-null terminated, and its size .sFileTitle = vbNullChar & Space$(512) & _ vbNullChar & vbNullChar .nMaxTitle = Len(OFN.sFileTitle) 'Starting folder, double-null terminated .sInitialDir = ActiveWorkbook.Path & vbNullChar .sDialogTitle = sTitle 'the dialog title string 'Default open flags and multiselect .flags = OFS_FILE_OPEN_FLAGS Or _ OFN_NOCHANGEDIR If sMultiFile = "Y" Then .flags = .flags Or _ OFN_ALLOWMULTISELECT End With SelectFile = GetOpenFileName(OFN) If SelectFile Then 'Remove trailing pair of terminating nulls and ' trim returned file string sBuffer = Trim$(Left$(OFN.sFile, Len(OFN.sFile) - 2)) 'If multiple-select, first member is Path , remaining ' members are the files under that path Do While Len(sBuffer) 3 SelectedFiles.Add StripDelimitedItem(sBuffer, vbNullChar) Loop sReadOnly = Abs((OFN.flags And OFN_READONLY)) End If End If End Function Private Sub Class_Initialize() sTitle = "GetOpenFileName" End Sub Private Sub Class_Terminate() Set SelectedFiles = Nothing End Sub '-----------------------------*------------------------------*------ Private Function ValidInput() As Boolean '-----------------------------*------------------------------*------ Dim i As Integer ValidInput = True i = 1 If IsEmpty(sFileName) Then sFileName = " - a file description must be supplied" i = i + 1 ValidInput = False End If If IsEmpty(sFileType) Then sFileType = " - a file extension must be supplied" i = i + 1 ValidInput = False End If If sMultiFile < "Y" And sMultiFile < "N" Then sMultiFile = "Multiple files must be Y or N" i = i + 1 ValidInput = False End If End Function '-----------------------------*------------------------------*------ Private Function StripDelimitedItem(startStrg As String, _ delimiter As String) As String '-----------------------------*------------------------------*------ 'take string separated by nulls, split off 1 item, and shorten string ' so the next item is ready for removal. Dim pos As Long Dim item As String pos = InStr(1, startStrg, delimiter) If pos Then StripDelimitedItem = Mid$(startStrg, 1, pos) startStrg = Mid$(startStrg, pos + 1, Len(startStrg)) End If End Function '-----------------------------*------------------------------*------ Private Function TrimNull(item As String) As String '-----------------------------*------------------------------*------ Dim pos As Integer pos = InStr(item, Chr$(0)) If pos Then TrimNull = Left$(item, pos - 1) Else TrimNull = item End If End Function -- HTH Bob Phillips "LJi" wrote in message oups.com... I am working on 2000. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to catch the workbook names...
Hi,
what about this kb: (no consideration for canceling) XL2000: How to Create a Macro That Opens Multiple Selected Files http://support.microsoft.com/kb/213635/EN-US/ XL: How to Create a Macro That Opens Multiple Selected Files http://support.microsoft.com/kb/141574/EN-US/ -- HTH okaizawa LJi wrote: Hi List, I am using OpenDialog box to let user open more than one Excel file at the same time. How can I catch the file names user choosed? I tried the function Application.GetOpenFilename("Excel Files (*.xls), *.xls"). But first, it didn't open any file, and secondly, it only allows user to choose one at a time. Any help will be appreciated. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
HOW TO CATCH A DIFFERENT/INCONSISTENT FORMULA | Excel Discussion (Misc queries) | |||
how to catch the second or more cell with vlookup | Excel Worksheet Functions | |||
how do I find names in a workbook full of names | Excel Discussion (Misc queries) | |||
how to catch errors from outlook | Excel Programming | |||
Catch-22 with Error 59 | Excel Programming |