Excel VBA write to a file

Hi all,

I have a small macro that writes to a textfile, it works fine except for one thing. If the path of the file contains a space, the macro completes with no errors, yet no file is created.

 
OutputFile = "c:\Level\Level Files\myfile.txt"
iFileNum = FreeFile()
Open OutputFile For Output As #iFileNum

For i = 2 To lastrow
    If Range("K" & i).Value <> "" Then Print #iFileNum,Range("K" & i).Value
Next i

Close #iFileNum

Open in new window


How do I get it to work with a space in the path? Thanks.
LVL 8
ragnarok89Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Arno KosterCommented:
instead of "c:\level\level files\" you could use "c:\level\levelf~1"
0
Arno KosterCommented:
although at my workplace (office 2003) the file does seem to be created and is actually being written to.
are you sure that cells in the K column contain value ?
0
Arno KosterCommented:
that would be 2007 running at my workplace, sorry
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

ragnarok89Author Commented:
akoster,

I tried the same thing, it give me a runtime error 76, Path not found.

The path does exist, col K does have values. If I set my path to be "c:\level" (no spaces), it works perfectly.
0
ragnarok89Author Commented:
We're running Excel 2003 SP3 by the way
0
Arno KosterCommented:
hmm, you could add single quotes to the outputfile declaration

OutputFile = "'c:\Level\Level Files\myfile.txt'"

Open in new window


otherwise i guess you could switch to using the filesystem object (you'll need to reference the microsoft scripting runtime for this to work)

Set fso = New FileSystemObject
Set outputStream = fso.OpenTextFile(OutputFile, ForAppending, True)
outputStream.Write Range("K" & i).Value
'-- or use outputStream.WriteLine Range("K" & i).Value
outputStream.Close

Open in new window


0
ragnarok89Author Commented:
Great ideas, but still no joy. Using the single quotes results in runtime error 52: Bad filename or number

The fso method does not produce files (with ot without single quotes, using write or writeline). If i remove the space from the path, however, I do get an empty 0k file.

I must be missing something very basic, because all these methods should work...
0
Arno KosterCommented:
strange. Can you try writing a fixed string to the file to test if you are able to write to it at all, eg. by

outputstream.write "something"

Open in new window


if this does not work I expect some other problems to intervene (such as user rights set for the directory preventing editing of files)

if this does work however, writing is possible and you could try either

OutputFile = "c:\Level\Level%20Files\myfile.txt"

Open in new window


or

OutputFile = """c:\Level\Level Files\myfile.txt"""

Open in new window

0
ragnarok89Author Commented:
I think you're on the right track...       

outputstream.write "something"

did not produce anything. I verified that I have full control over the folder as well. The %20 and triple quotes produce errors.

Maybe I should go about this a different way?although it's "klunky," I suppose I could put all the values I want on a new sheet and do a SaveAs...
0
borgunitCommented:
Not that this does anything different really. It does work on mine.
Public Sub QUICKTEST()
Dim OutputFile As String
Dim iFileNum As Variant
Dim i As Integer
OutputFile = "c:\Temp\Test one\myfile.txt"
iFileNum = FreeFile()
Open OutputFile For Output As #iFileNum

For i = 2 To 4
    If Range("K" & i).Value <> "" Then Print #iFileNum, Range("K" & i).Value
    Debug.Print Range("K" & i).Value 'checking from the immediate window
Next i

Close #iFileNum
End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
borgunitCommented:
Just a thought, are the values that you pull, values or formulas. It should not matter if you can write to another folder (as you mentioned), but I do know calculated values are not output.
0
ragnarok89Author Commented:
That works! I think it's the Dim as Variant that fixed it...
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.