Home |
Search |
Today's Posts |
|
#1
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
I believe this will work to remove the quotes from the first line of data in
the CSV file (but if you had other data in row 1 of your file when it was saved as a CSV file, say cell B1, then I think that will be a problem and we'll need to do more parsing of the first line of data). Is this closer to what you're looking for? Sub test3() Const strPathIn As String = "I:\04 Production Files\IPEP Records\Cleaningfiles\" Const strPathOut As String = "I:\04 Production Files\IPEP Records\Cleaned\" Dim strFileIn As String Dim strFileOut As String Dim FSO As Scripting.FileSystemObject Dim fDest As Scripting.TextStream Dim strData As String Dim lngFNum1 As Long ChDir strPathIn strFileIn = Application.GetOpenFilename If strFileIn = "" Or strFileIn = "False" _ Then Exit Sub strFileOut = Replace(strFileIn, ".csv", _ "Temp.csv", 1, 1, vbTextCompare) strFileOut = strPathOut & Right(strFileOut, _ Len(strFileOut) - InStrRev(strFileOut, _ "\", -1, vbTextCompare)) Set FSO = New Scripting.FileSystemObject Set fDest = FSO.CreateTextFile(strFileOut, True) lngFNum1 = FreeFile() Open strFileIn For Input As #lngFNum1 Do While Not EOF(lngFNum1) If Seek(lngFNum1) = 1 Then Line Input #lngFNum1, strData strData = Replace(strData, """", "", 1, -1, vbTextCompare) Else: Line Input #lngFNum1, strData End If fDest.WriteLine strData Loop Close fDest.Close End Sub "Help?" wrote: JMB, Here is the code I am trying to use: Sub test2() Const strFileIn As String = "I:\04 Production Files\IPEP Records\Cleaning files\" ActiveWorkbook.SaveAs filename:=ActiveWorkbook.FullName Const strFileOut As String = "I:\04 Production Files\IPEP Records\Cleaned\" ActiveWorkbook.SaveAs filename:=ActiveWorkbook.FullName Dim FSO As Scripting.FileSystemObject Dim fDest As Scripting.TextStream Dim strData As String Dim lngFNum1 As Long Set FSO = New Scripting.FileSystemObject Set fDest = FSO.CreateTextFile(strFileOut, True)<Problem lngFNum1 = FreeFile() Open strFileIn For Input As #lngFNum1<Problem Do While Not EOF(lngFNum1) Line Input #lngFNum1, strData strData = Replace(strData, """", "", 1, -1, vbTextCompare) fDest.WriteLine strData Loop Close fDest.Close End Sub I liked the way you had everything directed in paths in the first set of coding, but I am having a hard time get the areas above to recognize the filenames. I have marked the ares with the word "<Problems". Do you have any suggestions? "JMB" wrote: I changed two lines in the variable declaration (marked "Change") and added 4 new lines (marked "New", however one of the lines takes up 2 lines. In VBA the underscore _ is the line continuation character). The NG often wraps things funny, so hopefully you will be able to sort it out. It creates a temporary file using the original's name plus "Temp". Then when it is done it deletes the original data file and renames the temporary file to the original data filename. Sub test2() Dim strFileIn As String '<Change Dim strFileOut As String '<Change Dim FSO As Scripting.FileSystemObject Dim fDest As Scripting.TextStream Dim strData As String Dim lngFNum1 As Long strFileIn = Application.GetOpenFilename '<NEW strFileOut = Replace(strFileIn, ".csv", _ "Temp.csv", 1, 1, vbTextCompare) '<NEW Set FSO = New Scripting.FileSystemObject Set fDest = FSO.CreateTextFile(strFileOut, True) lngFNum1 = FreeFile() Open strFileIn For Input As #lngFNum1 Do While Not EOF(lngFNum1) Line Input #lngFNum1, strData strData = Replace(strData, """", "", 1, -1, vbTextCompare) fDest.WriteLine strData Loop Close fDest.Close Kill strFileIn '<NEW Name strFileOut As strFileIn '<NEW End Sub "Help?" wrote: Okay, I've got it working. But I have one problem. I need the below to except variable filenames and keep that file name consistant. Right now I have the filename pulling from columns in the excel for bothe csv and excel pieces that is automatically saved. How can I make the below to except variable filename? Const strFileIn As String = "I:\Excel\CSVData.csv" Const strFileOut As String = "I:\Excel\CSVClean.csv" "JMB" wrote: This appeared to work. I've found TextStreamObjects will write the data to file w/o enclosing it in quotes. You'll need to test to see if it works for what you are doing. You will need to set up a reference to the Scripting library (Tools/References - Microsoft Scripting Runtime). Change strFileIn and out for your filenames. To check, I changed the extension on the CSVClean file to .txt so it would open in Notepad. I'm sure you know that if you open the cleaned file in Excel, the (,) will split the data into two columns. Sub test2() Const strFileIn As String = "I:\Excel\CSVData.csv" Const strFileOut As String = "I:\Excel\CSVClean.csv" Dim FSO As Scripting.FileSystemObject Dim fDest As Scripting.TextStream Dim strData As String Dim lngFNum1 As Long Set FSO = New Scripting.FileSystemObject Set fDest = FSO.CreateTextFile(strFileOut, True) lngFNum1 = FreeFile() Open strFileIn For Input As #lngFNum1 Do While Not EOF(lngFNum1) Line Input #lngFNum1, strData strData = Replace(strData, """", "", 1, -1, vbTextCompare) fDest.WriteLine strData Loop Close fDest.Close End Sub "Help?" wrote: I am trying to find a macro that will force the "" around the below to not happen. Because the below has a comma in it, on csv convertion it automatically insert the "". Can I force this through VBA macro to not do this? This will be the third time I have asked for help, but not received yet. Can anyone help me in this? Coming out as: "%! XGF 9999 SETBUFSIZE (,) SETDBSEP QSTRIP_on (GuntherIPEP.jdt) SETJDT (IPEP.dbm) STARTDBM" I need it to be: %! XGF 9999 SETBUFSIZE (,) SETDBSEP QSTRIP_on (GuntherIPEP.jdt) SETJDT (IPEP.dbm) STARTDBM |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Excel puts quotes around cells with commas in tab delimited files | Setting up and Configuration of Excel | |||
change straight quotes to curly quotes | Excel Discussion (Misc queries) | |||
How do I output csv files from Excel with quotes round text fields | Excel Discussion (Misc queries) | |||
How do i get historical stock quotes using MSN Money Stock Quotes | Excel Discussion (Misc queries) | |||
Quotes | Excel Programming |