PDA

View Full Version : Add double quotes to cells that contain spaces



nicluc88
05-14-2018, 09:25 AM
Hi,
how can I find the cells that contain the spaces and optionally add the double quotes?

I'm writing a script to extract a sheet from an excel file, and save in a new CSV:

Workbook.SaveAs FileName:=FileName, FileFormat:=xlCSVUTF8, Local:=True

Thanks

nicluc88
05-21-2018, 03:12 AM
no idea?

rlv
05-21-2018, 08:27 PM
no idea?

I can only speak for myself, but I think you are not getting any responses because your question is missing too much information. There's nothing there for a responder to engage with so they move on. In a perfect world, someone would ask you a lot of questions about what you are trying to do and slowly, after enough back and forth posting, enough details about what you want would emerge to allow the problem to be worked. But that takes energy and time, and sometimes people are just not in the mood, and so your question is skipped over. I think you would get a better result if you were to start again and create a new and more detailed description of the problem you are having, what your specific desired outcome is (perhaps along with an example to illustrate), and a description of any steps taken so far. People who's posts contain a clear problem description along with relevant data, sample inputs, and sample outputs usually get more attention. You should not assume that we know anything about what you are doing and so you should explain everything. Something that seems "obvious" to you is not necessarily obvious to anyone else.

If you have it, post code that you are having trouble with. Or a sample workbook showing some sample data.

nicluc88
05-22-2018, 02:43 AM
I can only speak for myself, but I think you are not getting any responses because your question is missing too much information. There's nothing there for a responder to engage with so they move on. In a perfect world, someone would ask you a lot of questions about what you are trying to do and slowly, after enough back and forth posting, enough details about what you want would emerge to allow the problem to be worked. But that takes energy and time, and sometimes people are just not in the mood, and so your question is skipped over. I think you would get a better result if you were to start again and create a new and more detailed description of the problem you are having, what your specific desired outcome is (perhaps along with an example to illustrate), and a description of any steps taken so far. People who's posts contain a clear problem description along with relevant data, sample inputs, and sample outputs usually get more attention. You should not assume that we know anything about what you are doing and so you should explain everything. Something that seems "obvious" to you is not necessarily obvious to anyone else.

If you have it, post code that you are having trouble with. Or a sample workbook showing some sample data.

Hi rlv, thanks for your reply.
I agree with your message; the request was not a support in the code, but some ideas about how to solve it.

Anyway, I follow your advice :)

Goal: If a cell contains space (" "), I want to put it on the top before and after.



Sub Macro1()
For Each myCell In ActiveSheet.Range("A2:W7957")

If InStr(myCell.Value, " ") > 0 Then

myCell.Value = Chr(34) & myCell.Value & Chr(34)

End If

Next myCell


ActiveWorkbook.SaveAs Filename:="C:\Cartel1.csv" _
, FileFormat:=xlCSVUTF8, CreateBackup:=False
End Sub


my simple script, on a few cells responds quickly, on about 8000 lines and 23 columns the script stays running for too long (about 20 minutes).

Is there a more optimized alternative?

rlv
05-22-2018, 07:31 AM
I created a worksheet with data in cells A2:W7945, each cell containing a string with a space. I ran your code on it. It took just 7.6 seconds to complete all the cells, so if yours takes 20 minutes, it suggests that the root cause of the slowness lies elsewhere. What else are you doing in your worksheet that could be causing it? Have you tried turning off calculation while looping through the cells?


Sub Macro1()
Dim ST As Single
ST = Timer
Dim MyCell As Range

Application.Calculation = xlCalculationManual
For Each MyCell In ActiveSheet.Range("A2:W7957")
If InStr(MyCell.Value, " ") > 0 Then
MyCell.Value = Chr(34) & MyCell.Value & Chr(34)
End If
Next MyCell
Debug.Print "Elaped time for X = " & Timer - ST
Application.Calculation = xlCalculationAutomatic

ActiveWorkbook.SaveAs FileName:="C:\Cartel1.csv", FileFormat:=xlCSVUTF8, CreateBackup:=False
End Sub

nicluc88
05-22-2018, 08:17 AM
I created a worksheet with data in cells A2:W7945, each cell containing a string with a space. I ran your code on it. It took just 7.6 seconds to complete all the cells, so if yours takes 20 minutes, it suggests that the root cause of the slowness lies elsewhere. What else are you doing in your worksheet that could be causing it? Have you tried turning off calculation while looping through the cells?


Sub Macro1()
Dim ST As Single
ST = Timer
Dim MyCell As Range

Application.Calculation = xlCalculationManual
For Each MyCell In ActiveSheet.Range("A2:W7957")
If InStr(MyCell.Value, " ") > 0 Then
MyCell.Value = Chr(34) & MyCell.Value & Chr(34)
End If
Next MyCell
Debug.Print "Elaped time for X = " & Timer - ST
Application.Calculation = xlCalculationAutomatic

ActiveWorkbook.SaveAs FileName:="C:\Cartel1.csv", FileFormat:=xlCSVUTF8, CreateBackup:=False
End Sub


Thanks rlv,
adding the following 2 lines, the script runs in few seconds

Application.Calculation = xlCalculationManual
[...]
Application.Calculation = xlCalculationAutomatic

Now, I go to study this new (for me) command.

rlv, your contribution is really appreciated! Thanks again

nicluc88
05-22-2018, 08:30 AM
Now, in the output csv, the double quotes increase:

"""Phase 1"""

I expected to find only 2 symbol, 1 before and 1 after string value... :saywhat:

"Phase 1"

rlv
05-22-2018, 02:58 PM
For I = 5 To 2 Step -1
With ActiveSheet.Range("A2:W7957")
.Replace What:=String(I, 34), Replacement:=Chr(34), LookAt:=xlPart
End With
Next I

nicluc88
05-24-2018, 12:38 AM
For I = 5 To 2 Step -1
With ActiveSheet.Range("A2:W7957")
.Replace What:=String(I, 34), Replacement:=Chr(34), LookAt:=xlPart
End With
Next I


Hi rlv,
in the sheet, the cell value is without double quotes, only in the output csv; so this procedure is to run on the csv, right?
Thanks