Help with text file output

March 11, 2009 at 13:55:45
Specs: Windows XP
I have a sample code I can modify for my needs, which is to extract data from a specific excel spreadsheet column and write it to a text file. My problem is I want the output to be one long text string separated by commas. Like this:
data1,data2,data3,data4, etc.

Instead of the normal output of:
data1
data2
data3
data4

How can I do that?


See More: Help with text file output

Report •


#1
March 11, 2009 at 14:07:22
Gosh, I guess it would be helpful if I said this was a simple vb script huh? DOH!

Report •

#2
March 11, 2009 at 14:43:40
I've modified your script as follows:

Report •

#3
March 11, 2009 at 20:43:13
Razor, i dont see any scipt.

Report •

Related Solutions

#4
March 11, 2009 at 21:31:21
Yes, reno, that's the point.

I swear, subtleness has been scarified to the Internet God. Probably on the same alter as spelling and grammar.


Report •

#5
March 12, 2009 at 01:38:32
"Oh mighty internet god

Take thy grammar.
Take thy subtlety.
Take thy etiquette.

Beseech thy torrents.
Beseech thy porn.
Beseech thy advertising.

Curse dial-up, download limits and all that is unholy."


Report •

#6
March 12, 2009 at 09:11:26
Oh sorry, well like I said it is a sample code so here it is:

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Set objWorkbook = objExcel.Workbooks.Open("C:\Scripts\Test.xls")
Set objWorksheet = objWorkbook.Worksheets(1)
objWorksheet.Activate

i = 1

Do While True
strValue = objWorksheet.Cells(i,3)
If strValue = "" Then
Exit Do
End If
strText = strText & strValue & vbCrLf
i = i + 1
Loop

objExcel.Quit

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.CreateTextFile("C:\Scripts\ExcelData.txt")

objFile.Write strText
objFile.Close


Report •

#7
March 12, 2009 at 09:19:10
LOL, i thought something went wrong with my eyes.
i cant see any code in post#1, yet razor can modify the code. amazing.

Report •

#8
March 14, 2009 at 00:53:59
Replace this line:
strText = strText & strValue & vbCrLf

With this line:
strText = strText & strValue & ","


Report •

#9
March 14, 2009 at 08:30:03
Awesome! Thank you, I knew it was simple but as I'm sure you can see I'm very much a novice right now. Thanks again, I'll give it a shot

Report •

#10
March 17, 2009 at 15:14:13
Ok, so I'm up and running....sort of. The script is basically doing what I want it to which is simply pulling data out of one column of an excel spreadsheet and writing it to a text file as previously explained. The problem I am having now is that it does not pull ALL of the data out, it is stopping for some reason which I don't understand. I thought perhaps it came to a blank cell and it stopped there but that is not the case. Here's the simple code:

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Set objWorkbook = objExcel.Workbooks.Open("C:\Scripts\Notebook_Info.xls")
Set objWorksheet = objWorkbook.Worksheets(3)
objWorksheet.Activate

i = 2

Do While True
strValue = objWorksheet.Cells(i,9)
If strValue = "" Then
Exit Do
End If
strText = strText & strValue & ","
i = i + 1
Loop

objExcel.Quit

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.CreateTextFile("C:\Scripts\ExcelData.txt")

objFile.Write strText
objFile.Close

mmmm so do you think maybe I need to include the range of the excel column? Will that make it run until the end of the range maybe? Thanks so much


Report •

#11
March 17, 2009 at 17:26:58
I thought perhaps it came to a blank cell and it stopped there but that is not the case.
That's not what your code says:
If strValue = "" Then Exit Do

But whatever! I have nothing better to do, so here's how I might solve the problem. It's far from perfect (I hate relying on the clipboard), but it gets the job done.

Set book = GetObject("C:\Scripts\Notebook_Info.xls")
With book
  .Application.Visible = True
  .Application.DisplayAlerts = False
  Set sheet = .Worksheets(3)
  .Worksheets.Add .Worksheets(1)
  Set tempSheet = .Worksheets(1)
End With

With sheet
  .Application.Intersect(.Range("I:I"), .Range(.Cells(1), .Cells.SpecialCells(11))).Copy
End With

With tempSheet
  On Error Resume Next
  .Cells(1).PasteSpecial , , True, True
  On Error GoTo 0
  .Cells(1).Delete
  .SaveAs "C:\Scripts\ExcelData.txt", 6, , , , , False
  .Delete
End With
If you have questions, you know where to find me.

Report •


Ask Question