But I think as always with these sorts of things there is usually a “File Systems Object” Alternative way of doing it.
#FOR LOOP IN EXCEL MAC MACRO CODE#
I do not understand exactly how this code works.
( A quick Google through MrExcel Threads is always worth a go ! ) With Application.FileDialog(msoFileDialogOpen) Optional sFilter As String = "*.xls") As String Optional sDesc As String = "Excel (*.xls)", _ MsgBox FileOpen("C:\", "Documents", "*.xls *.xlsx *.xlsm")Įnd Subįunction FileOpen(initialFilename As String, _ when you pass a workbook name to the Workbooks collection you should always include the file extension. dotĢ60 ' 'Normally you might want to do stuff hereĢ90 ' Workbooks("" & NameOnly & ".xls").Close 'When referrencing Worksheets collection always use the bit after the. Dot )Ģ30 Dim NameOnly As StringĢ40 Let NameOnly = Left(NameOnly, (InStrRev(NameOnly, ".") - 1)) 'To Take off the bit after the. String is mostly OK, but Variant allows for "False" answer below.ġ10 Let FullFilePathAndName = Application.GetOpenFilename(StrOpenFileTypesDrpBx, 1, "Name up left in Dialogue box",, False) 'All optional Argumentsġ20 ''("DrpBxMsg1(DisplydFileType1),*.DisplydFileType1, DspBxMsg2(.etc.", Default DrpBx Index, "DialogueWindow(Form)Name", Button-Mac Only, MultipleFileSelectionOption )ġ30 If FullFilePathAndName = False Then 'Application.GetOpenFilename returns Boolean False for no File selection.ġ40 MsgBox "You did't select a file!", vbExclamation, "Canceled"ġ50 Exit Sub ' user cancelled, so get out of Subġ70 End Ifġ90 'Open File, Play around a bit with the name and path stringsĢ00 Workbooks.Open Filename:="" & FullFilePathAndName & ""Ģ10 Dim FullNameOnly As String 'File name as typically seen displayed with last bit after dotĢ20 Let FullNameOnly = Right(FullFilePathAndName, Len(FullFilePathAndName) - InStrRev(FullFilePathAndName, "\")) 'Full File including extension ( Bit after.
Sub GetFileSyedAzeemOpenIt() 'ġ0 'perform a ChDir before the GetOpenFilename # CHANGE strDefPath to suit the Path to folder you are interested inĢ0 Dim strDefPath As String: Let strDefPath = ThisWorkbook.path '# Any Path / Folder to test this code! here we simply use the Path where the File with this code in isģ0 Dim strMyDrive As String: Let strMyDrive = Left(strDefPath, 2) ', the drive ( C: E: etc )Ĥ0 ChDrive (strMyDrive): ChDir (strDefPath) 'Changing the Drive and Directory may be needed for check of Filename to workĦ0 'Application.GetOpenFilenameħ0 Dim StrOpenFileTypesDrpBx As String 'The Drop Box options in GetOpenFilename Dialogue WindowĨ0 Let StrOpenFileTypesDrpBx = "Excel (*.xlsx),*.xlsx,OpenOffice (*.ods),*.ods,All Files (*.*),*.*,ExcelMacros (*.xlsm).xlsm"ĩ0 '"DrpBxMsg1(DisplydFileType1),*.DisplydFileType1, DrpBxMsg1(DisplydFileType2),*.DisplydFileType2, DspBxMsg3(.etc."ġ00 Dim FullFilePathAndName As Variant 'Complete Path on Computer string.
0 kommentar(er)
