Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Two really easy questions
I have this code in excel (written in excel 97)
MyFile = Application.GetOpenFilename Myfile2 = Left(MyFile, Len(MyFile) - 2) & "rat" ActiveWorkbook.SaveAs filename:=Myfile2, FileFormat:=xlText There's a bit more in the middle but that is irrelevant, the two questions I have a 1) now I have upgraded to XP the left function doesn't work, what can I use instead? (or what reference do I need to tick) 2) Not really excel, but how do I do the equivalent in word? (the getopenfilename doesn't work in word). |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Two really easy questions
Hi James,
I not using XP but I can assure you the LEFT function is still there. Your problem may be that the workbook has not been saved before, and getopenfilename will return nothing. I'm not real familiar with Word, but you can try Application.ActiveDocument.Name -- John johnf202 at hotmail dot com "James" wrote in message ... I have this code in excel (written in excel 97) MyFile = Application.GetOpenFilename Myfile2 = Left(MyFile, Len(MyFile) - 2) & "rat" ActiveWorkbook.SaveAs filename:=Myfile2, FileFormat:=xlText There's a bit more in the middle but that is irrelevant, the two questions I have a 1) now I have upgraded to XP the left function doesn't work, what can I use instead? (or what reference do I need to tick) 2) Not really excel, but how do I do the equivalent in word? (the getopenfilename doesn't work in word). |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Two really easy questions
James,
1) Are you getting an error, or is it just not returning what you expect? If the former, look in ToolsReferences in the VB IDE, and if there are any missing links, fix them 2) Try this alternative version which works in Word as well Create a class module called clsGetOpenFileName and paste this code in 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 the 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 = ThisDocument.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 the 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 a string separated by nulls, split off 1 item, and shorten ' the 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)) TrimNull = IIf(pos, Left$(item, pos - 1), item) End Function and this is an example of how to run it, using Excel files, but modify to suit, Sub OpenFiles() Dim cFileOpen As clsGetOpenFileName Set cFileOpen = New clsGetOpenFileName With cFileOpen .FileName = "Ex*.xls" .FileType = "Excel Files" .DialogTitle = "Class File Browser Demo" .MultiFile = "N" .SelectFile If .SelectedFiles.Count 0 Then MsgBox (.SelectedFiles(1)) End If End With Set cFileOpen = Nothing End Sub -- HTH ------- Bob Phillips ... looking out across Poole Harbour to the Purbecks "James" wrote in message ... I have this code in excel (written in excel 97) MyFile = Application.GetOpenFilename Myfile2 = Left(MyFile, Len(MyFile) - 2) & "rat" ActiveWorkbook.SaveAs filename:=Myfile2, FileFormat:=xlText There's a bit more in the middle but that is irrelevant, the two questions I have a 1) now I have upgraded to XP the left function doesn't work, what can I use instead? (or what reference do I need to tick) 2) Not really excel, but how do I do the equivalent in word? (the getopenfilename doesn't work in word). |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Two really easy questions
James,
It's actually a lot more flexible that GetOpenFileName as you can put in a string in the filename and it will select only files that match that criteria. So as well as specifying the file type, you can say that you only want files beginning with say "Production". I use it all the time to limit the umber of files that are shown to just those I am interested in. -- HTH ------- Bob Phillips ... looking out across Poole Harbour to the Purbecks "James" wrote in message ... Cheers Bob The word code works a treat, seems a long way round 1 line of code in excel. The LEFT function comes up with the error message: Compile Error: Can't find project or library Tried selecting different references but no luck as of yet. I can work around it, but is a usefull function. -----Original Message----- James, 1) Are you getting an error, or is it just not returning what you expect? If the former, look in ToolsReferences in the VB IDE, and if there are any missing links, fix them 2) Try this alternative version which works in Word as well Create a class module called clsGetOpenFileName and paste this code in 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 the 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 = ThisDocument.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 the 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 a string separated by nulls, split off 1 item, and shorten ' the 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)) TrimNull = IIf(pos, Left$(item, pos - 1), item) End Function and this is an example of how to run it, using Excel files, but modify to suit, Sub OpenFiles() Dim cFileOpen As clsGetOpenFileName Set cFileOpen = New clsGetOpenFileName With cFileOpen .FileName = "Ex*.xls" .FileType = "Excel Files" .DialogTitle = "Class File Browser Demo" .MultiFile = "N" .SelectFile If .SelectedFiles.Count 0 Then MsgBox (.SelectedFiles(1)) End If End With Set cFileOpen = Nothing End Sub -- HTH ------- Bob Phillips ... looking out across Poole Harbour to the Purbecks "James" wrote in message ... I have this code in excel (written in excel 97) MyFile = Application.GetOpenFilename Myfile2 = Left(MyFile, Len(MyFile) - 2) & "rat" ActiveWorkbook.SaveAs filename:=Myfile2, FileFormat:=xlText There's a bit more in the middle but that is irrelevant, the two questions I have a 1) now I have upgraded to XP the left function doesn't work, what can I use instead? (or what reference do I need to tick) 2) Not really excel, but how do I do the equivalent in word? (the getopenfilename doesn't work in word). . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
View Questions and Answer to questions I created | Excel Discussion (Misc queries) | |||
call Module from MS Excel Object - Probably very easy VB questions | Excel Discussion (Misc queries) | |||
Dividing in excel easy questions | Excel Discussion (Misc queries) | |||
Easy (?) questions for setting up a database | Excel Discussion (Misc queries) | |||
Couple of hopefully easy questions | Excel Worksheet Functions |