Export result to excel (space delimited)

Hi there,

I have a listbox (lstResults) that displays a query result with 5 fields and I would like it to be saved. So far I have no problem saving it to an excel file, but all 5 fields only saved in a column.

I would like to know how to make those 5 files to be saved in column A, B, C, D, E, F.

Thanks

Here's my script so far that I found from this site :p

Private Sub cmdSave_Click()
Dim obExcel As Excel.Application
Dim i As Long
Dim FileName As String
Set obExcel = New Excel.Application
obExcel.Workbooks.Add
FileName = obExcel.GetSaveAsFilename
For i = 1 To lstResults.ListCount
obExcel.cells(i, 1).Value = lstResults.List(i - 1)
Next
obExcel.Visible = True
End Sub

Hi aReX876 and welcome to the forum!

Please be sure to read the posting guidelines (http://www.xtremevbtalk.com/faq.php?faq=evbf_faq#faq_evbf_rules).

That's good work so far! :)

So, if I understand you right, you have five records in your single-column listbox. You wish to put these values in Excel in 5 columns along a single row.

How about taking this line:
obExcel.cells(i, 1).Value = lstResults.List(i - 1)
and switching around the i and the 1:
obExcel.cells(1, i).Value = lstResults.List(i - 1)

Let us know if you get stuck.

And just before the end of your procedure, add in this line:

Set objExcel = Nothing

Colin

Hey,

Thanks for the quick reply. What does Set obExcel = Nothing do?
I added at the end of my procedure but nothing has changed :(

Here's an example of my query result from my listbox

5/11/2007 0.12 2.26 5.06 2.2
5/10/2007 3.14 2.11 144.1 12.22


Right now my excel would put all those result in
A1 5/11/2007 0.12 2.26 5.06 2.2
A2 5/10/2007 3.14 2.11 144.1 12.22


I tried to switch around to obExcel.cells(1, i).Value = lstResults.List(i - 1). But i got a debug error :(

Hey,

Thanks for the quick reply. What does Set obExcel = Nothing do?
I added at the end of my procedure but nothing has changed :(

I am a beginner of VB and I haven't touch this for 6 years :D

That line won't change the "appearance" of anything. It's a defensive bit of programming to ensure that object variables are released to avoid memory leaks.

I recommend Mike_R's tutorial on automating Excel from VB6:
http://www.xtremevbtalk.com/showthread.php?t=135815

To get your output going along a row instead of down a row, switch around the i and the 1 like I mentioned in post#2. I think that should cover it.

You are using VB6 and not VBA here, I assume?
Colin

Oh okay. I misunderstood your question!! :)

So once you've put the values into Excel (as you were already doing - ignore my earlier suggestion), you can use the range objects TextToColumns Method (delimited spaces) to parse the text into each column.

Colin

Hi again,

can you please tell me where to put Range.TextToColumns? I have no idea where I should put it :-\

TextToColumn is a new step, because it has to use a range as input, it can't operate directly on the (string) ListBox item.

Example:
sheetTest.Cells(2, 2).Value = "A B C"
'now B2 contains "A B C"

Call sheetTest.Cells(2, 2).TextToColumns(sheetTest.Cells(2, 3), xlDelimited, Space:=True)
'now C2 thru E2 contain "A", "B", "C"

You will want to refer to the VB help entry for the TextToColumns method, because there are lots of parameters and the syntax can get quite complicated, depending on the formatting of the data to be parsed.

TextToColumns is a nice neat way to do this, but here's another option you might want to become familiar with:
(this one does work directly on strings rather than requiring a range object)

Dim str1 As String 'declare a variable
Dim arrONE() As String 'declare an array
Dim i As Integer

str1 = "text to be split at the spaces" 'you would use your list results instead

arrONE = Split(str1, " ")
'split the contents of the str1 variable at the spaces and store the results in arrONE
'(see keyword "split" in VB help)

For i = 0 To UBound(arrONE) 'see VB help for Ubound and Lbound
Cells(1, i + 1).Value = arrONE(i)
Next

Good call, geodekl. Since we're using VBA anyway, we might as well use the VB function, unless we actually need any of the added complexity of Excel's functionality. We can still assign the array in one step, making it even simpler:
sheetTest.Cells(2, 3).Resize(1, UBound(arrONE) - LBound(arrONE) + 1).Value = arrONE

If you're comfortable using arrays then by all means... :)
I wonder which way would be more efficient.... (hint, I already know the answer to this)?

What d'ya reckon Geodekl - want to do some testing for us?! ;)

Colin

Given that Cas appears to be replacing my loop with a single operation, I'd say it's pretty obvious which is more efficient. But I'm going to have to go play with that anyway, until I have a clear picture of what resize does rather than just the general idea I can infer from the code. :chuckle:



-Geodekl

until I have a clear picture of what resize does rather than just the general idea I can infer from the code. :chuckle:
It's a very poorly chosen designator, as far as I'm concerned. Especially since it's a property; "Resize" is a verb and should thus be the name of a method or function, by MS's own naming rules. :rolleyes:

Given that Cas appears to be replacing my loop with a single operation, I'd say it's pretty obvious which is more efficient. But I'm going to have to go play with that anyway, until I have a clear picture of what resize does rather than just the general idea I can infer from the code. :chuckle:



-Geodekl

Ah, I wasn't totally clear what I meant. :)
I meant for a comparison between "Text To Columns" vs "Split."
So, optimise your "split" concept and then test it against the TTC idea with varying numbers of records - which routine do you find is the best?

.... it's a property; "Resize" is a verb and should thus be the name of a method or function, by MS's own naming rules. :rolleyes:
Yes, I agree, it sounds like it should be a method!

hey guys,

Right now i am using geodekl's method and it is working perfectly.

Thanks for all your help