Getting file name from the file path error

T

Tom

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
 
P

Patrick Molloy

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 procedure:=

Sub GetFileName(stFullName As String) As String
Range("F5") = MID$(stFullName ,InStrRev(stFullName, "/")+1)
End Sub



Tom said:
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
Click to expand...
 
T

Tom

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 said:
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 procedure:=

Sub GetFileName(stFullName As String) As String
Range("F5") = MID$(stFullName ,InStrRev(stFullName, "/")+1)
End Sub



Tom said:
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
Click to expand...
Click to expand...
 
J

JLatham

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 said:
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 said:
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 procedure:=

Sub GetFileName(stFullName As String) As String
Range("F5") = MID$(stFullName ,InStrRev(stFullName, "/")+1)
End Sub



Tom said:
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
Click to expand...
Click to expand...
Click to expand...
 
T

Tom

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 said:
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 said:
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 said:
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 procedure:=

Sub GetFileName(stFullName As String) As String
Range("F5") = MID$(stFullName ,InStrRev(stFullName, "/")+1)
End Sub



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
Click to expand...
Sub ImportRunData()
1 ImportDisplayRDFile
2 If myFileName <> False Then GetFileName (myFileName)
End Sub
Click to expand...
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
Click to expand...
Function GetFileName(stFullName As String) As String
GetFileName = Right$(stFullName, Len(stFullName) - InStrRev(stFullName,
Application.PathSeparator))
Range("F5") = GetFileName
End Function
Click to expand...
Click to expand...
Click to expand...
 
J

JLatham

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 said:
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 said:
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 said:
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

:

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 procedure:=

Sub GetFileName(stFullName As String) As String
Range("F5") = MID$(stFullName ,InStrRev(stFullName, "/")+1)
End Sub



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
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Help in Getting the File Name from a path 3
Excel VBA help: Text file formatting 19
Run-time error 1004 paste method of worksheet class failed 3
Run two macros for two different sheets 11
VBA Pivot Table Error 1
My Never ending ARRAY code problems 18
Create Detail Sheets from Pivot Table 1
Error with calling Format sub 7

Top