|
Getting file name from the file path error
I created a macro that imports a file into Excel 2007 and with Bob Phillips
help was able to extract the file name from the path. The code works fine on my computer (I have VB6 installed), but when other users run the macro they get an object library error (they do not have VB6 installed). Is there a way to modify the Function GetFileName so that it will work using Excel VBA code? Below is the code so far. any and all suggestions are greatly welcomed. Dim myFileName As Variant Sub ImportRunData() 1 ImportDisplayRDFile 2 If myFileName < False Then GetFileName (myFileName) End Sub Sub ImportDisplayRDFile() ' 'ImportRunDataFile Macro ' 'This macro will import and display a DSS Furnace RD file in Excel ' 'THIS SECTION ALLOWS THE USER TO NAVIGATE TO THE FILE LOCATION AND IMPORT IT CORRECTLY 1 myFileName = Application.GetOpenFilename(FileFilter:="Text Files, *.csv") 2 If myFileName = False Then 3 Exit Sub 'user hit cancle 4 End If 5 Sheets("Sheet1").Select 6 With ActiveSheet 7 With .QueryTables.Add(Connection:="Text;" & myFileName, Destination:=.Range("$AG$1")) 8 .Name = "Pick Place for JRB001078B DDU" 9 .FieldNames = True 10 .RowNumbers = False 11 .FillAdjacentFormulas = False 12 .PreserveFormatting = True 13 .RefreshOnFileOpen = False 14 .RefreshStyle = xlInsertDeleteCells 15 .SavePassword = False 16 .SaveData = True 17 .AdjustColumnWidth = True 18 .RefreshPeriod = 0 19 .TextFilePromptOnRefresh = False 20 .TextFilePlatform = 437 21 .TextFileStartRow = 1 22 .TextFileParseType = xlDelimited 23 .TextFileTextQualifier = xlTextQualifierDoubleQuote 24 .TextFileConsecutiveDelimiter = False 25 .TextFileTabDelimiter = True 26 .TextFileSemicolonDelimiter = False 27 .TextFileCommaDelimiter = True 28 .TextFileSpaceDelimiter = False 29 .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _ 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1 _ , 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1) 30 .TextFileTrailingMinusNumbers = True 31 .Refresh BackgroundQuery:=False 32 End With 33 Application.ScreenUpdating = False 34 Sheets("Sheet1").Select 35 Range("P7").Select 36 Selection.NumberFormat = "[h]:mm" 37 Range("G9").Select 38 Selection.NumberFormat = "[h]:mm" 39 Range("L9").Select 40 Selection.NumberFormat = "[h]:mm" 41 Range("Q9").Select 42 Selection.NumberFormat = "[h]:mm" 43 Range("V9").Select 44 Selection.NumberFormat = "[h]:mm" 45 Range("V13").Select 46 Selection.NumberFormat = "[h]:mm" 47 Range("AA9").Select 48 Selection.NumberFormat = "[h]:mm" 49 Range("AG1:DM4200").Select 50 Selection.Copy 51 Sheets("RUN DATA FILE").Select 52 Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False 53 Application.CutCopyMode = False 54 Range("A1").Select 55 Sheets("Sheet1").Select 56 Range("B1").Select 57 End With End Sub Function GetFileName(stFullName As String) As String GetFileName = Right$(stFullName, Len(stFullName) - InStrRev(stFullName, Application.PathSeparator)) Range("F5") = GetFileName End Function |
Getting file name from the file path error
it should work but its not coded correctly ...nor do i see where its used.
as a function:= Function GetFileName(stFullName As String) As String GetFileName = Right$(stFullName, Len(stFullName) - InStrRev(stFullName, Application.PathSeparator)) End Function as a procedu= Sub GetFileName(stFullName As String) As String Range("F5") = MID$(stFullName ,InStrRev(stFullName, "/")+1) End Sub "Tom" wrote in message ... I created a macro that imports a file into Excel 2007 and with Bob Phillips help was able to extract the file name from the path. The code works fine on my computer (I have VB6 installed), but when other users run the macro they get an object library error (they do not have VB6 installed). Is there a way to modify the Function GetFileName so that it will work using Excel VBA code? Below is the code so far. any and all suggestions are greatly welcomed. Dim myFileName As Variant Sub ImportRunData() 1 ImportDisplayRDFile 2 If myFileName < False Then GetFileName (myFileName) End Sub Sub ImportDisplayRDFile() ' 'ImportRunDataFile Macro ' 'This macro will import and display a DSS Furnace RD file in Excel ' 'THIS SECTION ALLOWS THE USER TO NAVIGATE TO THE FILE LOCATION AND IMPORT IT CORRECTLY 1 myFileName = Application.GetOpenFilename(FileFilter:="Text Files, *.csv") 2 If myFileName = False Then 3 Exit Sub 'user hit cancle 4 End If 5 Sheets("Sheet1").Select 6 With ActiveSheet 7 With .QueryTables.Add(Connection:="Text;" & myFileName, Destination:=.Range("$AG$1")) 8 .Name = "Pick Place for JRB001078B DDU" 9 .FieldNames = True 10 .RowNumbers = False 11 .FillAdjacentFormulas = False 12 .PreserveFormatting = True 13 .RefreshOnFileOpen = False 14 .RefreshStyle = xlInsertDeleteCells 15 .SavePassword = False 16 .SaveData = True 17 .AdjustColumnWidth = True 18 .RefreshPeriod = 0 19 .TextFilePromptOnRefresh = False 20 .TextFilePlatform = 437 21 .TextFileStartRow = 1 22 .TextFileParseType = xlDelimited 23 .TextFileTextQualifier = xlTextQualifierDoubleQuote 24 .TextFileConsecutiveDelimiter = False 25 .TextFileTabDelimiter = True 26 .TextFileSemicolonDelimiter = False 27 .TextFileCommaDelimiter = True 28 .TextFileSpaceDelimiter = False 29 .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _ 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1 _ , 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1) 30 .TextFileTrailingMinusNumbers = True 31 .Refresh BackgroundQuery:=False 32 End With 33 Application.ScreenUpdating = False 34 Sheets("Sheet1").Select 35 Range("P7").Select 36 Selection.NumberFormat = "[h]:mm" 37 Range("G9").Select 38 Selection.NumberFormat = "[h]:mm" 39 Range("L9").Select 40 Selection.NumberFormat = "[h]:mm" 41 Range("Q9").Select 42 Selection.NumberFormat = "[h]:mm" 43 Range("V9").Select 44 Selection.NumberFormat = "[h]:mm" 45 Range("V13").Select 46 Selection.NumberFormat = "[h]:mm" 47 Range("AA9").Select 48 Selection.NumberFormat = "[h]:mm" 49 Range("AG1:DM4200").Select 50 Selection.Copy 51 Sheets("RUN DATA FILE").Select 52 Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False 53 Application.CutCopyMode = False 54 Range("A1").Select 55 Sheets("Sheet1").Select 56 Range("B1").Select 57 End With End Sub Function GetFileName(stFullName As String) As String GetFileName = Right$(stFullName, Len(stFullName) - InStrRev(stFullName, Application.PathSeparator)) Range("F5") = GetFileName End Function |
Getting file name from the file path error
Patrick.
I verified the function call and my code is the same as yours for function:= .. I have had other users try it and still get an error. I copied your code for as a procedure and I get a compile error: Expected: end of statement with the As highlighted after the(stFullName As String). The function call in my code is used after the file is imported. I have seperated the various subs and functions below which may make it clear. Still not sure why the function does not work. Tom "Patrick Molloy" wrote: it should work but its not coded correctly ...nor do i see where its used. as a function:= Function GetFileName(stFullName As String) As String GetFileName = Right$(stFullName, Len(stFullName) - InStrRev(stFullName, Application.PathSeparator)) End Function as a procedu= Sub GetFileName(stFullName As String) As String Range("F5") = MID$(stFullName ,InStrRev(stFullName, "/")+1) End Sub "Tom" wrote in message ... I created a macro that imports a file into Excel 2007 and with Bob Phillips help was able to extract the file name from the path. The code works fine on my computer (I have VB6 installed), but when other users run the macro they get an object library error (they do not have VB6 installed). Is there a way to modify the Function GetFileName so that it will work using Excel VBA code? Below is the code so far. any and all suggestions are greatly welcomed. Dim myFileName As Variant Sub ImportRunData() 1 ImportDisplayRDFile 2 If myFileName < False Then GetFileName (myFileName) End Sub Sub ImportDisplayRDFile() ' 'ImportRunDataFile Macro ' 'This macro will import and display a DSS Furnace RD file in Excel ' 'THIS SECTION ALLOWS THE USER TO NAVIGATE TO THE FILE LOCATION AND IMPORT IT CORRECTLY 1 myFileName = Application.GetOpenFilename(FileFilter:="Text Files, *.csv") 2 If myFileName = False Then 3 Exit Sub 'user hit cancle 4 End If 5 Sheets("Sheet1").Select 6 With ActiveSheet 7 With .QueryTables.Add(Connection:="Text;" & myFileName, Destination:=.Range("$AG$1")) 8 .Name = "Pick Place for JRB001078B DDU" 9 .FieldNames = True 10 .RowNumbers = False 11 .FillAdjacentFormulas = False 12 .PreserveFormatting = True 13 .RefreshOnFileOpen = False 14 .RefreshStyle = xlInsertDeleteCells 15 .SavePassword = False 16 .SaveData = True 17 .AdjustColumnWidth = True 18 .RefreshPeriod = 0 19 .TextFilePromptOnRefresh = False 20 .TextFilePlatform = 437 21 .TextFileStartRow = 1 22 .TextFileParseType = xlDelimited 23 .TextFileTextQualifier = xlTextQualifierDoubleQuote 24 .TextFileConsecutiveDelimiter = False 25 .TextFileTabDelimiter = True 26 .TextFileSemicolonDelimiter = False 27 .TextFileCommaDelimiter = True 28 .TextFileSpaceDelimiter = False 29 .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _ 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1 _ , 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1) 30 .TextFileTrailingMinusNumbers = True 31 .Refresh BackgroundQuery:=False 32 End With 33 Application.ScreenUpdating = False 34 Sheets("Sheet1").Select 35 Range("P7").Select 36 Selection.NumberFormat = "[h]:mm" 37 Range("G9").Select 38 Selection.NumberFormat = "[h]:mm" 39 Range("L9").Select 40 Selection.NumberFormat = "[h]:mm" 41 Range("Q9").Select 42 Selection.NumberFormat = "[h]:mm" 43 Range("V9").Select 44 Selection.NumberFormat = "[h]:mm" 45 Range("V13").Select 46 Selection.NumberFormat = "[h]:mm" 47 Range("AA9").Select 48 Selection.NumberFormat = "[h]:mm" 49 Range("AG1:DM4200").Select 50 Selection.Copy 51 Sheets("RUN DATA FILE").Select 52 Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False 53 Application.CutCopyMode = False 54 Range("A1").Select 55 Sheets("Sheet1").Select 56 Range("B1").Select 57 End With End Sub Function GetFileName(stFullName As String) As String GetFileName = Right$(stFullName, Len(stFullName) - InStrRev(stFullName, Application.PathSeparator)) Range("F5") = GetFileName End Function |
Getting file name from the file path error
Tom,
His SUB shouldn't have " As String" as part of the declaration, and it could/should use the same Right$() function that the function did. VB6 really shouldn't have anything to do with it. This is all self-contained within Excel and Excel's VBA engine. Aren't you using Excel's VB Editor to maintain the code? It is part of a code module in an Excel workbook or add-in, isn't it? The other users may not have a reference to the Excel Object Library set on their machines, and that could be why the code is failing. Check your list of Tools -- References and you'll probably find it in your list, but not on their machines. I had that situation a long time ago on some work I did for (now) AT&T - but with Outlook being used within Excel code - I had to do some error trapping and then if an error took place I had to go hunt down and set the library reference in code before continuing. Heaven only knows where that code/those applications are archived away - that was all done back around 1999-2000. "Tom" wrote: Patrick. I verified the function call and my code is the same as yours for function:= . I have had other users try it and still get an error. I copied your code for as a procedure and I get a compile error: Expected: end of statement with the As highlighted after the(stFullName As String). The function call in my code is used after the file is imported. I have seperated the various subs and functions below which may make it clear. Still not sure why the function does not work. Tom "Patrick Molloy" wrote: it should work but its not coded correctly ...nor do i see where its used. as a function:= Function GetFileName(stFullName As String) As String GetFileName = Right$(stFullName, Len(stFullName) - InStrRev(stFullName, Application.PathSeparator)) End Function as a procedu= Sub GetFileName(stFullName As String) As String Range("F5") = MID$(stFullName ,InStrRev(stFullName, "/")+1) End Sub "Tom" wrote in message ... I created a macro that imports a file into Excel 2007 and with Bob Phillips help was able to extract the file name from the path. The code works fine on my computer (I have VB6 installed), but when other users run the macro they get an object library error (they do not have VB6 installed). Is there a way to modify the Function GetFileName so that it will work using Excel VBA code? Below is the code so far. any and all suggestions are greatly welcomed. Dim myFileName As Variant Sub ImportRunData() 1 ImportDisplayRDFile 2 If myFileName < False Then GetFileName (myFileName) End Sub Sub ImportDisplayRDFile() ' 'ImportRunDataFile Macro ' 'This macro will import and display a DSS Furnace RD file in Excel ' 'THIS SECTION ALLOWS THE USER TO NAVIGATE TO THE FILE LOCATION AND IMPORT IT CORRECTLY 1 myFileName = Application.GetOpenFilename(FileFilter:="Text Files, *.csv") 2 If myFileName = False Then 3 Exit Sub 'user hit cancle 4 End If 5 Sheets("Sheet1").Select 6 With ActiveSheet 7 With .QueryTables.Add(Connection:="Text;" & myFileName, Destination:=.Range("$AG$1")) 8 .Name = "Pick Place for JRB001078B DDU" 9 .FieldNames = True 10 .RowNumbers = False 11 .FillAdjacentFormulas = False 12 .PreserveFormatting = True 13 .RefreshOnFileOpen = False 14 .RefreshStyle = xlInsertDeleteCells 15 .SavePassword = False 16 .SaveData = True 17 .AdjustColumnWidth = True 18 .RefreshPeriod = 0 19 .TextFilePromptOnRefresh = False 20 .TextFilePlatform = 437 21 .TextFileStartRow = 1 22 .TextFileParseType = xlDelimited 23 .TextFileTextQualifier = xlTextQualifierDoubleQuote 24 .TextFileConsecutiveDelimiter = False 25 .TextFileTabDelimiter = True 26 .TextFileSemicolonDelimiter = False 27 .TextFileCommaDelimiter = True 28 .TextFileSpaceDelimiter = False 29 .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _ 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1 _ , 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1) 30 .TextFileTrailingMinusNumbers = True 31 .Refresh BackgroundQuery:=False 32 End With 33 Application.ScreenUpdating = False 34 Sheets("Sheet1").Select 35 Range("P7").Select 36 Selection.NumberFormat = "[h]:mm" 37 Range("G9").Select 38 Selection.NumberFormat = "[h]:mm" 39 Range("L9").Select 40 Selection.NumberFormat = "[h]:mm" 41 Range("Q9").Select 42 Selection.NumberFormat = "[h]:mm" 43 Range("V9").Select 44 Selection.NumberFormat = "[h]:mm" 45 Range("V13").Select 46 Selection.NumberFormat = "[h]:mm" 47 Range("AA9").Select 48 Selection.NumberFormat = "[h]:mm" 49 Range("AG1:DM4200").Select 50 Selection.Copy 51 Sheets("RUN DATA FILE").Select 52 Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False 53 Application.CutCopyMode = False 54 Range("A1").Select 55 Sheets("Sheet1").Select 56 Range("B1").Select 57 End With End Sub Function GetFileName(stFullName As String) As String GetFileName = Right$(stFullName, Len(stFullName) - InStrRev(stFullName, Application.PathSeparator)) Range("F5") = GetFileName End Function |
Getting file name from the file path error
Ok, I tried the sub without the "As String" and still when other users run
it the error Cannot find project of library occurs with the Right$ highlighted. I checked one users tools - References and it appears that it appears normal. Comparing their reference list aginst mine the only difference is that I have the VB6 references added. Any other suggestions as this is starting to become frustrating. "JLatham" wrote: Tom, His SUB shouldn't have " As String" as part of the declaration, and it could/should use the same Right$() function that the function did. VB6 really shouldn't have anything to do with it. This is all self-contained within Excel and Excel's VBA engine. Aren't you using Excel's VB Editor to maintain the code? It is part of a code module in an Excel workbook or add-in, isn't it? The other users may not have a reference to the Excel Object Library set on their machines, and that could be why the code is failing. Check your list of Tools -- References and you'll probably find it in your list, but not on their machines. I had that situation a long time ago on some work I did for (now) AT&T - but with Outlook being used within Excel code - I had to do some error trapping and then if an error took place I had to go hunt down and set the library reference in code before continuing. Heaven only knows where that code/those applications are archived away - that was all done back around 1999-2000. "Tom" wrote: Patrick. I verified the function call and my code is the same as yours for function:= . I have had other users try it and still get an error. I copied your code for as a procedure and I get a compile error: Expected: end of statement with the As highlighted after the(stFullName As String). The function call in my code is used after the file is imported. I have seperated the various subs and functions below which may make it clear. Still not sure why the function does not work. Tom "Patrick Molloy" wrote: it should work but its not coded correctly ...nor do i see where its used. as a function:= Function GetFileName(stFullName As String) As String GetFileName = Right$(stFullName, Len(stFullName) - InStrRev(stFullName, Application.PathSeparator)) End Function as a procedu= Sub GetFileName(stFullName As String) As String Range("F5") = MID$(stFullName ,InStrRev(stFullName, "/")+1) End Sub "Tom" wrote in message ... I created a macro that imports a file into Excel 2007 and with Bob Phillips help was able to extract the file name from the path. The code works fine on my computer (I have VB6 installed), but when other users run the macro they get an object library error (they do not have VB6 installed). Is there a way to modify the Function GetFileName so that it will work using Excel VBA code? Below is the code so far. any and all suggestions are greatly welcomed. Dim myFileName As Variant Sub ImportRunData() 1 ImportDisplayRDFile 2 If myFileName < False Then GetFileName (myFileName) End Sub Sub ImportDisplayRDFile() ' 'ImportRunDataFile Macro ' 'This macro will import and display a DSS Furnace RD file in Excel ' 'THIS SECTION ALLOWS THE USER TO NAVIGATE TO THE FILE LOCATION AND IMPORT IT CORRECTLY 1 myFileName = Application.GetOpenFilename(FileFilter:="Text Files, *.csv") 2 If myFileName = False Then 3 Exit Sub 'user hit cancle 4 End If 5 Sheets("Sheet1").Select 6 With ActiveSheet 7 With .QueryTables.Add(Connection:="Text;" & myFileName, Destination:=.Range("$AG$1")) 8 .Name = "Pick Place for JRB001078B DDU" 9 .FieldNames = True 10 .RowNumbers = False 11 .FillAdjacentFormulas = False 12 .PreserveFormatting = True 13 .RefreshOnFileOpen = False 14 .RefreshStyle = xlInsertDeleteCells 15 .SavePassword = False 16 .SaveData = True 17 .AdjustColumnWidth = True 18 .RefreshPeriod = 0 19 .TextFilePromptOnRefresh = False 20 .TextFilePlatform = 437 21 .TextFileStartRow = 1 22 .TextFileParseType = xlDelimited 23 .TextFileTextQualifier = xlTextQualifierDoubleQuote 24 .TextFileConsecutiveDelimiter = False 25 .TextFileTabDelimiter = True 26 .TextFileSemicolonDelimiter = False 27 .TextFileCommaDelimiter = True 28 .TextFileSpaceDelimiter = False 29 .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _ 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1 _ , 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1) 30 .TextFileTrailingMinusNumbers = True 31 .Refresh BackgroundQuery:=False 32 End With 33 Application.ScreenUpdating = False 34 Sheets("Sheet1").Select 35 Range("P7").Select 36 Selection.NumberFormat = "[h]:mm" 37 Range("G9").Select 38 Selection.NumberFormat = "[h]:mm" 39 Range("L9").Select 40 Selection.NumberFormat = "[h]:mm" 41 Range("Q9").Select 42 Selection.NumberFormat = "[h]:mm" 43 Range("V9").Select 44 Selection.NumberFormat = "[h]:mm" 45 Range("V13").Select 46 Selection.NumberFormat = "[h]:mm" 47 Range("AA9").Select 48 Selection.NumberFormat = "[h]:mm" 49 Range("AG1:DM4200").Select 50 Selection.Copy 51 Sheets("RUN DATA FILE").Select 52 Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False 53 Application.CutCopyMode = False 54 Range("A1").Select 55 Sheets("Sheet1").Select 56 Range("B1").Select 57 End With End Sub Function GetFileName(stFullName As String) As String GetFileName = Right$(stFullName, Len(stFullName) - InStrRev(stFullName, Application.PathSeparator)) Range("F5") = GetFileName End Function |
Getting file name from the file path error
Can you send me the file as an email attachment? I don't think I need the
file that it reads, as I can just stub out the code after the line that extracts the filename. I'll see if it runs on my system(s) or not and try to help pin down the problem if I can. Change [] items as needed for real email: HelpFrom[at]JLathamSite[dot]com will get the email to me. JLatham "Tom" wrote: Ok, I tried the sub without the "As String" and still when other users run it the error Cannot find project of library occurs with the Right$ highlighted. I checked one users tools - References and it appears that it appears normal. Comparing their reference list aginst mine the only difference is that I have the VB6 references added. Any other suggestions as this is starting to become frustrating. "JLatham" wrote: Tom, His SUB shouldn't have " As String" as part of the declaration, and it could/should use the same Right$() function that the function did. VB6 really shouldn't have anything to do with it. This is all self-contained within Excel and Excel's VBA engine. Aren't you using Excel's VB Editor to maintain the code? It is part of a code module in an Excel workbook or add-in, isn't it? The other users may not have a reference to the Excel Object Library set on their machines, and that could be why the code is failing. Check your list of Tools -- References and you'll probably find it in your list, but not on their machines. I had that situation a long time ago on some work I did for (now) AT&T - but with Outlook being used within Excel code - I had to do some error trapping and then if an error took place I had to go hunt down and set the library reference in code before continuing. Heaven only knows where that code/those applications are archived away - that was all done back around 1999-2000. "Tom" wrote: Patrick. I verified the function call and my code is the same as yours for function:= . I have had other users try it and still get an error. I copied your code for as a procedure and I get a compile error: Expected: end of statement with the As highlighted after the(stFullName As String). The function call in my code is used after the file is imported. I have seperated the various subs and functions below which may make it clear. Still not sure why the function does not work. Tom "Patrick Molloy" wrote: it should work but its not coded correctly ...nor do i see where its used. as a function:= Function GetFileName(stFullName As String) As String GetFileName = Right$(stFullName, Len(stFullName) - InStrRev(stFullName, Application.PathSeparator)) End Function as a procedu= Sub GetFileName(stFullName As String) As String Range("F5") = MID$(stFullName ,InStrRev(stFullName, "/")+1) End Sub "Tom" wrote in message ... I created a macro that imports a file into Excel 2007 and with Bob Phillips help was able to extract the file name from the path. The code works fine on my computer (I have VB6 installed), but when other users run the macro they get an object library error (they do not have VB6 installed). Is there a way to modify the Function GetFileName so that it will work using Excel VBA code? Below is the code so far. any and all suggestions are greatly welcomed. Dim myFileName As Variant Sub ImportRunData() 1 ImportDisplayRDFile 2 If myFileName < False Then GetFileName (myFileName) End Sub Sub ImportDisplayRDFile() ' 'ImportRunDataFile Macro ' 'This macro will import and display a DSS Furnace RD file in Excel ' 'THIS SECTION ALLOWS THE USER TO NAVIGATE TO THE FILE LOCATION AND IMPORT IT CORRECTLY 1 myFileName = Application.GetOpenFilename(FileFilter:="Text Files, *.csv") 2 If myFileName = False Then 3 Exit Sub 'user hit cancle 4 End If 5 Sheets("Sheet1").Select 6 With ActiveSheet 7 With .QueryTables.Add(Connection:="Text;" & myFileName, Destination:=.Range("$AG$1")) 8 .Name = "Pick Place for JRB001078B DDU" 9 .FieldNames = True 10 .RowNumbers = False 11 .FillAdjacentFormulas = False 12 .PreserveFormatting = True 13 .RefreshOnFileOpen = False 14 .RefreshStyle = xlInsertDeleteCells 15 .SavePassword = False 16 .SaveData = True 17 .AdjustColumnWidth = True 18 .RefreshPeriod = 0 19 .TextFilePromptOnRefresh = False 20 .TextFilePlatform = 437 21 .TextFileStartRow = 1 22 .TextFileParseType = xlDelimited 23 .TextFileTextQualifier = xlTextQualifierDoubleQuote 24 .TextFileConsecutiveDelimiter = False 25 .TextFileTabDelimiter = True 26 .TextFileSemicolonDelimiter = False 27 .TextFileCommaDelimiter = True 28 .TextFileSpaceDelimiter = False 29 .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _ 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1 _ , 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1) 30 .TextFileTrailingMinusNumbers = True 31 .Refresh BackgroundQuery:=False 32 End With 33 Application.ScreenUpdating = False 34 Sheets("Sheet1").Select 35 Range("P7").Select 36 Selection.NumberFormat = "[h]:mm" 37 Range("G9").Select 38 Selection.NumberFormat = "[h]:mm" 39 Range("L9").Select 40 Selection.NumberFormat = "[h]:mm" 41 Range("Q9").Select 42 Selection.NumberFormat = "[h]:mm" 43 Range("V9").Select 44 Selection.NumberFormat = "[h]:mm" 45 Range("V13").Select 46 Selection.NumberFormat = "[h]:mm" 47 Range("AA9").Select 48 Selection.NumberFormat = "[h]:mm" 49 Range("AG1:DM4200").Select 50 Selection.Copy 51 Sheets("RUN DATA FILE").Select 52 Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False 53 Application.CutCopyMode = False 54 Range("A1").Select 55 Sheets("Sheet1").Select 56 Range("B1").Select 57 End With End Sub Function GetFileName(stFullName As String) As String GetFileName = Right$(stFullName, Len(stFullName) - InStrRev(stFullName, Application.PathSeparator)) Range("F5") = GetFileName End Function |
| All times are GMT +1. The time now is 01:39 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com