Filename problem

G

Grace

Is there something wrong with the following code?

myFilename = MGR_SHORT_NAME + Sheets("Inputs").Range("E11").Value + "SUMPRF"
+ ".L00"

It keeps bombing out when it goes to find this file, because the actual
file, of course, was named so that it has the MGR_short_name in the front
part of its name, but the macro doesn't seem to recognize it, i.e., when it
responds that it cannot find the file, the name it says it is looking for,
does not have this first part.

Thanks,
Grace
 
R

Rob van Gelder

Could you tell us how you declare MGR_SHORT_NAME?

Also, use & instead of + when concatenating strings.
 
F

Frank Kabel

Hi
try
myFilename = MGR_SHORT_NAME & Sheets("Inputs").Range("E11").Value &
"SUMPRF"
& ".L00"

Also check the content of the variable 'MGR_SHORT_NAME'
you may include the line
msgbox myFilename
and check the concatenation
 
G

Grace

Per Norman, I have dimensioned it as a string and renamed it
strMGR_SHORT_NAME. The subroutine (where the variable IS picked up in
another such filename) calls another subroutine and it is not re-dimensioned
there and does not seem to be picked up there. Would that help?

I tried the & instead of +, but it doesn't help. Does the & also apply to
the + "*.xls", or just the strings in the name?

Any other ideas?

Dean
 
G

Grace

Rob suggested the ampersands too, but it did not help. I don't know what
you mean by

Also check the content of the variable 'MGR_SHORT_NAME'
you may include the line
msgbox myFilename
and check the concatenation
Click to expand...

other than that I should check the spelling. What else did you mean? I
did. In fact, I used that same string in another prior filename ands it
works there. As I just asked Rob, could it be that it is not re-dimensioned
in a subroutine that the main subroutine calls (which is also the first
place the macro bombs)?

Thanks
Grace
 
G

Grace

Rob and Frank,

I tried the re-dimensioning and it did not help. I also changed the name of
the filename to start with "Joe" instead of this string, which I input, and
changed the subroutine call to use a hard-wired "Joe" instead of this
starting string, and it found the file just fine with that.

As I mentioned earlier, the file has no trouble naming a file that it "saves
as" with this same problematic string. It just can't seem to understand it,
when it is trying to find the file to open. What could this be? I used
edit search to make sure the spelling was identical and it was.
 
N

Norman Jones

Hi Grace,

(1) Use the variable: strMGR_SHORT_NAME)
(2) Replace the + )maths plus sign) with the & (string concatenation
character)
(3) Unless it is your intention not to use the standard Excel xls suffix,
change ".L00" to (say) "-L00"

This would give you :
myFilename = StrMGR_SHORT_NAME & _
Sheets("Inputs").Range("E11").Value & "SUMPRF" & "L00" & ".xls"

(note that this is a single line of code, the underscore character, which
precedes Sheets("Inputs"), is a line continuation character).

If you have a saved file with the extension: "L00), as opposed to .xls,
then you will have to type *.L00 in the FileOpen dialog box to display that
and similarly extensioned files.
 
F

Frank Kabel

Hi
you may post your complete macro. It sounds like you haven't assigned a
value to this variable. So its empty
 
R

Rob van Gelder

I don't know what the problem is. You'll need some debugging info to figure
out what's going on.

So your code will read something like:

myFilename = MGR_SHORT_NAME & Sheets("Inputs").Range("E11").Value & "SUMPRF"
& ".L00"
MsgBox myFilename

(or even better than MsgBox, Debug.Print)

Then you'll see if myFilename is getting set correctly.
 
D

Dave Peterson

One more:

Option Explicit
Sub testme01()

Dim myFilename As String
Dim MGR_SHORT_NAME As String
Dim testStr As String

MGR_SHORT_NAME = "something that you set???"

myFilename = MGR_SHORT_NAME & Sheets("Inputs").Range("E11").Value & _
"SUMPRF" & ".L00"

MsgBox "|" & myFilename & "|"
'| may help see extra spaces in the filename

testStr = ""
On Error Resume Next
testStr = Dir(myFilename)
On Error GoTo 0

If testStr = "" Then
MsgBox "Filename: " & myFilename & " doesn't exist"
End If

End Sub

This assumes that sheets("Inputs") exists in the activeworkbook. (If you have
doubts, you may want to include a check for that, too.)

Option Explicit

Sub a()

Dim myFilename As String
Dim MGR_SHORT_NAME As String
Dim testStr As String
Dim testWks As Worksheet

MGR_SHORT_NAME = "something that you set???"

Set testWks = Nothing
On Error Resume Next
Set testWks = ActiveWorkbook.Worksheets("inputs")
On Error GoTo 0
If testWks Is Nothing Then
MsgBox "It doesn't exist!"
Exit Sub '?
End If

myFilename = MGR_SHORT_NAME & testWks.Range("E11").Value & _
"SUMPRF" & ".L00"

MsgBox "|" & myFilename & "|"
'| may help see extra spaces in the filename

testStr = ""
On Error Resume Next
testStr = Dir(myFilename)
On Error GoTo 0

If testStr = "" Then
MsgBox "Filename: " & myFilename & " doesn't exist"
End If

End Sub
 
G

Grace

<< If you have a saved file with the extension: "L00), as opposed to
..xls,then you will have to type *.L00 in the FileOpen dialog box to display
that and similarly extensioned files >>

You are saying that I cannot somehow, by macro, make sure that, when it
tries to open a file, it will look at files of all extension types and, if I
want a .L00 (or whatever) file type, I cannot get it to find it?

If so, I am starting to think that, if it could not find the file, the error
message in the dialog box may just be abbreviating the filename that it
can't find. It does has a long path. In that case, it IS recognizing the
string after all. And this makes sense because it is recognizing that
string, just before this point in the macro, when it uses it for a save file
as command. If so, I guess the key is to not believe everything you read!

So, let me just ask specifically: If you are in EXCEL and want to have a
macro open a file of some other file type, for which you know the filename
and type, isn't there a way to do this? Actually, what would be even better
would be if it could be of any 'text type' file type as I am not sure this
L00 extension always gets assigned.

Thanks,
Grace
 
N

Norman Jones

Hi Grace,
You are saying that I cannot somehow, by macro, make sure that, when it
tries to open a file, it will look at files of all extension types and, if I
want a .L00 (or whatever) file type, I cannot get it to find it?
Click to expand...

No not at all. You can, of course, specify any (valid) file extension you
wish. Using the default xls extension enables a search to return some or all
workbooks in a given path. Personally, I use an appropriate folder
configuration coupled with a naming convention to store/find my files.

That is not to say that I never use a non-standard extension. If, for
example, I wish to back up my xlb (toolbar) files I will often use a revised
extension.

In my reply to you I said:

The choice is yours, my intention was simply to to alert you .
 
G

Grace

The message box does return the right string. Actually, I am now having a
problem with the earlier, similar, save file as an EXCEL file command

fname = StrMGR_SHORT_NAME & Sheets("INPUTS").Range("B45").Value &
Sheets("INPUTS").Range("E11").Value & ".xls"

It says object variable or with block variable not set.

Can anyone explain this?

Thanks,
Grace
 
G

Grace

Actually, let me clarify my last post (see below). The name is correct on
the first call (a file which is then saved as an XLS file), the one that was
working fine and now is not, so please consider the error msg noted below.
On the second call (a file open as a non EXCEL file), the one that never
worked, the name is blank. So, at least in this second instance, it seems
that the strSHORT_MGR_NAME is not being passed from the subroutine where it
was defined (via a message box input) to a later subroutine. How do I
ensure stuff like this gets passed, anyway.

In summary, please answer this question and check the error message in the
prior post below.

Thanks a lot!
Dean
 
G

Grace

Thanks!

Dave Peterson said:
One more:

Option Explicit
Sub testme01()

Dim myFilename As String
Dim MGR_SHORT_NAME As String
Dim testStr As String

MGR_SHORT_NAME = "something that you set???"

myFilename = MGR_SHORT_NAME & Sheets("Inputs").Range("E11").Value & _
"SUMPRF" & ".L00"

MsgBox "|" & myFilename & "|"
'| may help see extra spaces in the filename

testStr = ""
On Error Resume Next
testStr = Dir(myFilename)
On Error GoTo 0

If testStr = "" Then
MsgBox "Filename: " & myFilename & " doesn't exist"
End If

End Sub

This assumes that sheets("Inputs") exists in the activeworkbook. (If you have
doubts, you may want to include a check for that, too.)

Option Explicit

Sub a()

Dim myFilename As String
Dim MGR_SHORT_NAME As String
Dim testStr As String
Dim testWks As Worksheet

MGR_SHORT_NAME = "something that you set???"

Set testWks = Nothing
On Error Resume Next
Set testWks = ActiveWorkbook.Worksheets("inputs")
On Error GoTo 0
If testWks Is Nothing Then
MsgBox "It doesn't exist!"
Exit Sub '?
End If

myFilename = MGR_SHORT_NAME & testWks.Range("E11").Value & _
"SUMPRF" & ".L00"

MsgBox "|" & myFilename & "|"
'| may help see extra spaces in the filename

testStr = ""
On Error Resume Next
testStr = Dir(myFilename)
On Error GoTo 0

If testStr = "" Then
MsgBox "Filename: " & myFilename & " doesn't exist"
End If

End Sub
Click to expand...
 
Top