MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Need help with a script

  Asked By: Kiswar    Date: Sep 10    Category: MS Office    Views: 1699

I am having some trouble with the code below. I have a list with two
columns like the following:

ABC 12345
XYZ 45678

I used the following code to export each row into a text file, such
that the name of the txt file was the name of the first column, ie
ABC.txt, and the single entry within it was the second column, ie
12345. The code I use is below:

Sub Example()
Const kPath As String = "C:\users\adrian\documents\notes"
Dim oCell As Range
Dim iFile As Integer
For Each oCell In Range("A1:A200")
iFile = FreeFile
Open kPath & oCell.Value & ".txt" For Output As #iFile
Print #iFile, oCell.Offset(0, 1).Value
Close #iFile
Next oCell
End Sub

This works great in earlier versions of Excel, but in Excel 2007,
nothing is printed to a file, and no errors in the script are
reported. I have checked my 'trusted locations' to ensure the
directory above is in the list.

Can anyone provide any pointers on what Excel 2007 needs to make this



2 Answers Found

Answer #1    Answered By: Von Fischer     Answered On: Sep 10

Check your resulting Filename string  (kPath)
debug.print kPath & oCell.Value & ".txt"

You'll see that your file  names are:

You'll also find your missing files in:
called notesABC.txt, notesXYZ.txt, etc.

In other words, you need to add a "\" to the end of:

If I were you, I'd build the filename string as:

FName = kPath & ocell.value & ".txt"
you can the do things like fileexists(fname) and such
to handle overwriting existing files, etc.

Answer #2    Answered By: Lenora Green     Answered On: Sep 10

It was the last backslash that prevented writing into the correct
destination. Now it works  a treat.

Didn't find what you were looking for? Find more on Need help with a script Or get search suggestion and latest updates.