Using Open Statement

[JillE]

Hi,

I'm trying to export data from Excel to a text file using the open statement.

My problem is that I have to create multiple files using some of the data
itself to make up the file name. I can get the basic functionality to work
and have used it before but haven't had any luck inserting variables for the
filename itself.

I can't find any documentation that specifically states it's possible or not
possible but I haven't found any examples either.

No matter what variation I try, I get a bad file name error.

Here's the code I'm using:

vFilename = vInternalExternal & "-" & vLastName4Char & "-" & vAdNumber & "-"
& vDateTime & "-" & vRequistionNumber & ".txt"
vFullFilename = ActiveWorkbook.Path & vFilename
Open vFullFilename For Append As #1
Print #1, vAllFields
Close #1

Any thoughts would be appreciated.

Thanks

 

[Jay Freedman]

I don't see anything wrong with the way you're constructing the vFilename
variable (although you might want to use an On Error statement to catch the
possibility that the result contains characters that aren't valid in a
filename). Also, try single-stepping through the macro with the F8 key and
look at the values of the variables in the Locals window to be sure they're
what you expect.

I think the problem is in the next statement. There is no backslash at the
end of the value of ActiveWorkbook.Path, so it's running together with the
vFilename value to create a name that can't exist. Change that statement to

vFullFilename = ActiveWorkbook.Path & "\" & vFilename

--
Regards,
Jay Freedman
Microsoft Word MVP
Email cannot be acknowledged; please post all follow-ups to the newsgroup so
all may benefit.

 

[JillE]

Hi Jay,

Yes, I found and corrected the error you are referring to after I posted the
question, but I am still getting the same bad file error. I had originally
used a variable for the path too and it did contain the backslash.

Any other ideas?

 

[Jay Freedman]

The "other idea" is the one I mentioned before: single-step into the code to
find out exactly what the value of vFullFilename is, just before the Open
statement executes. Copy that value and try to use it in Windows Explorer to
open the file. Does the string correspond _exactly_ to the valid filename?

If there's a bad character in the string, isolate it and trace back into the
other variables to find out where it came from.

If the string does open the file outside Word, then there's something else going
on. Is the path on a network drive? Could there be a permission problem? I don't
know what else to suggest.