A little excel vb help ?

[Isis]

I am trying to read each row in an excel sheet and write i tout to a CSV
file - got most of that working.

While I am reading each cell, I am reformatting some cells (concatenating
etc) before writing it out. I was using the following line to get the
Text value from a cell;

Selection.Cells(RowCount, ColumnCount).Text

but I now don't want to use a Selection - how do I just refer to a single
cell ?

I am now going to loop through the Rows only - then I will gather the
contents of each cell in a Row into one long string.

So I want something like;

strVariable = Cell(RowCount, 1)
strVariable = Trim(strVariable) & Cell(RowCount, 2) & _
Cell(RowCount, 3) & _
Cell(RowCount, 4) & ","
Print #FileNum, strVariable;

but I don't think Print Cell(RowCount, 1) is the right VB code.

Any help appreciated.
Thanks

 

[Bob Phillips]

Post your whole code and tell us the overall objective, this post is very
confusing.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Isis]

Sorry Bob,

Code at the bottom - but basically;

I want to loop through the rows in a spreadsheet - and write out each row
to a line in a CSV - I want to combine some cells while I am at it. I was
looping through each cell but I realised that I can actually just
assemble all the cells in each row in one statement if I can find a way
to refer to a cell that is

So before, I was testing which ColumnCount I was processing and then
doing something based on that - I don't want the user to have to enter a
Range or Selection, just run the code.

So, what I want to do - pseudo code;

Get the Filename
Loop through Rows
strVariable = (RowCount,1) & ","
strVariable = Trim(strVariable) & (RowCount,2) & _
Trim(strVariable) & (RowCount,3) & _
Trim(strVariable) & (RowCount,4) & "," _
Trim(strVariable) & "MYTEXT" & ","
Print #FileNum,
Next RowCount
Close the File
Done !


Thanks for responding Bob.

Regards




Current Code Below

Sub ProcessNames()
Dim DestFile As String
Dim FileNum As Integer
Dim ColumnCount As Integer
Dim RowCount As Integer
Dim strVariable As String

' Prompt user for destination file name.
DestFile = InputBox("Enter the destination filename" & _
Chr(10) & "(with complete path and extension):", _
"Quote-Comma Exporter")
' Obtain next free file handle number.
FileNum = FreeFile()

' Turn error checking off.
On Error Resume Next

' Attempt to open destination file for output.
Open DestFile For Output As #FileNum
' If an error occurs report it and end.
If Err <> 0 Then
MsgBox "Cannot open filename " & DestFile
End
End If

' Turn error checking on.
On Error GoTo 0

' Loop for each row in selection.
For RowCount = 1 To Selection.Rows.Count
' Loop for each column in selection.
For ColumnCount = 1 To Selection.Columns.Count
' Write current cell's text to file with quotation marks.
If ColumnCount = 3 Then
strVariable = "MYCHILD,"
strVariable = Trim(strVariable) & _
Selection.Cells(RowCount, 3).Text & " " & _
Selection.Cells(RowCount, 4) & " " & _
Selection.Cells(RowCount, 5)
ColumnCount = 5
Print #FileNum, strVariable;
Else
Print #FileNum, Selection.Cells(RowCount, ColumnCount).Text;
' Check if this cell is in last column
End If
If ColumnCount = Selection.Columns.Count Then
' If so then write a non-terminated line
Print #FileNum,
Else
' Otherwise, write a comma seperator
Print #FileNum, ",";
End If
' Start next iteration of ColumnCount loop.
Next ColumnCount
' Start next iteration of RowCount loop.
Next RowCount

' Close Output file.
Close #FileNum
End Sub

 

[Isis]

Bob,

Using;

For RowCount = 3 To Selection.Rows.Count ' Start at Row 3

I can loop through selected Rows - Can I just loop through ALL the Rows
that exist (starting at Row 3) ? so the user does not have to select the
rows first ?

Thanks

PS - I think I have found that Cells.(x,y) seems to reference individual
cells as per my last question ? Is this the best way.

Thanks again

 

[JLatham]

Your use of Cells(x,y) is as good a way as any.

As for working through rows without the user having to select them all
first, you can do that also. First step is to determine a column that will
always have some entry in a cell in it in the last used row. For this
example we'll assume it is column B. Then you can use this as your FOR
statement:

For RowCount = 3 To _
Range("B" & Rows.Count).End(xlUp).Row

 

[Isis]

For RowCount = 3 To _
Range("B" & Rows.Count).End(xlUp).Row

Not quite sure whom I am thanking, but Thank You - that seems to work.
Could I also have used SpecialCells somehow - just asking out of interest.

Thanks

 

[JLatham]

I think the thanks should go to Bob, he provided the meat of the solution
while I just gave you one minor change.

You might or might not be able to use one of the SpecialCells, but that's
iffy at times depending the worksheet. The way I provided is a 'sure thing'
although if row 3 is empty in that column and nothing below it in the column,
then you will get an error when it attempts to execute.