?
Solved

Excel VBA write to a file

Posted on 2011-10-21
12
Medium Priority
?
437 Views
Last Modified: 2012-05-12
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.
0
Comment
Question by:ragnarok89
  • 5
  • 5
  • 2
12 Comments
 
LVL 19

Expert Comment

by:Arno Koster
ID: 37006140
instead of "c:\level\level files\" you could use "c:\level\levelf~1"
0
 
LVL 19

Expert Comment

by:Arno Koster
ID: 37006153
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
 
LVL 19

Expert Comment

by:Arno Koster
ID: 37006159
that would be 2007 running at my workplace, sorry
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 8

Author Comment

by:ragnarok89
ID: 37006175
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
 
LVL 8

Author Comment

by:ragnarok89
ID: 37006192
We're running Excel 2003 SP3 by the way
0
 
LVL 19

Expert Comment

by:Arno Koster
ID: 37006315
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
 
LVL 8

Author Comment

by:ragnarok89
ID: 37006647
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
 
LVL 19

Expert Comment

by:Arno Koster
ID: 37006752
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
 
LVL 8

Author Comment

by:ragnarok89
ID: 37006906
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
 
LVL 10

Accepted Solution

by:
borgunit earned 300 total points
ID: 37007521
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
 
LVL 10

Expert Comment

by:borgunit
ID: 37007591
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
 
LVL 8

Author Closing Comment

by:ragnarok89
ID: 37008314
That works! I think it's the Dim as Variant that fixed it...
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question