Hi, I need to export a sheet to CSV format, so I can use that csv file as input to a access table SQL insert statement.
My problem is, I have one column that has data in it with quotes that cannot be altered.
e.g. The "location" field has building, room, cubicle such as "Building 208, Room 19, Cubicle 9"
This causes the building, room and cubicle to be placed in a separate cell. I need them as a single cell in the output. Placing double quotes in that cell before export solves the problem.
I have a macro that will insert the double quotes but there are some rows that are blank in the column.
This is what I've tried:
Line 2 when enabled selects all rows until it reaches a blank cell
Line 3 when enabled selects all rows in the sheet, well below the last line of real data
Here's the problem, using the code as shown works until the column G has a blank cell (at row 10), then the writing of the double quotes terminates before the bottom of the sheet (200 rows).
When I use the 2nd line instead of the third to define the range, I get the whole column double quoted well past the 200 rows of data.
How can I just get the double quotes on the "count of rows" in column G.
Note: if it helps, I can reference another row that has no empty cells for the 200 row count.
Also note the row count figure 200 is really variable, one week it may be 200, the next may be 375, the next may be 225.
My problem is, I have one column that has data in it with quotes that cannot be altered.
e.g. The "location" field has building, room, cubicle such as "Building 208, Room 19, Cubicle 9"
This causes the building, room and cubicle to be placed in a separate cell. I need them as a single cell in the output. Placing double quotes in that cell before export solves the problem.
I have a macro that will insert the double quotes but there are some rows that are blank in the column.
This is what I've tried:
Line 2 when enabled selects all rows until it reaches a blank cell
Line 3 when enabled selects all rows in the sheet, well below the last line of real data
Code:
Sub AddQuotes()
'With Range(Range("G1"), Range("G1").End(xlDown)).Select
With Range("G:G").Select
On Error Resume Next
Dim myCell As Range
For Each myCell In Selection
myCell.Value = Chr(34) & myCell.Value & Chr(34)
Next myCell
End With
End SubWhen I use the 2nd line instead of the third to define the range, I get the whole column double quoted well past the 200 rows of data.
How can I just get the double quotes on the "count of rows" in column G.
Note: if it helps, I can reference another row that has no empty cells for the 200 row count.
Also note the row count figure 200 is really variable, one week it may be 200, the next may be 375, the next may be 225.
