Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
A little excel vb help ?
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
A little excel vb help ?
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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
A little excel vb help ?
"Bob Phillips" wrote in
: 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
A little excel vb help ?
"Bob Phillips" wrote in
: 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
A little excel vb help ?
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" wrote: "Bob Phillips" wrote in : 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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
A little excel vb help ?
?B?SkxhdGhhbQ==?= <HelpFrom @ Jlathamsite.com.(removethis) wrote in
: 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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
A little excel vb help ?
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. "Isis" wrote: ?B?SkxhdGhhbQ==?= <HelpFrom @ Jlathamsite.com.(removethis) wrote in : 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|