Hallo,
I would like to Use my Excel VBA program to do this:
User Selectable: Open a CSV file using wordpad.
Once the program is opened I would like to search each row in the document for a name (value of 5 as a unique identifier) if the name exists then I would like to delete the entire row.
After the whole document is done I would like to close it and save changes.
The reason that I cant do this using excel is because the file gets screwed up, whenever I try to do the above in excel, even when I save in csv format and everything. The file has to be procesed through a nother program once I delete the unvanted info that is very picky about commas and any alteration in excel results in the file being incorrectly used.
The only method that I found that works is when I MANUALY open the csv in wordpad delete the lines I dont want and then close and save changes. Using this method the other program acepts the chaged file without a problem.
I would really appreciate some help with this as I have been battling with this issue for months. Only now I finaly found the manual solution and I still cant write this sort of thing by myself.
I can write the code to use excel to do it and it seams preaty simple. If what I need requires too complex an answer then please dont worry as I probably wouldnt understand it anyway.
I have included the code that does what I want in excel but results in the csv file being unusable.
'Read in path from setup area on front sheet and open "Open" box
DPath = Cells(2, "C").Value & Cells(2, "D").Value
ChDrive (Cells(2, "C").Value)
ChDir (DPath)
strFile = Application.GetOpenFilename(" (*.csv), *.csv", 1, "Select your System CSV file in Price Folder (Double Click on it)")
If strFile = False Then GoTo 1001
'Open the file
Workbooks.Open (strFile)
ourfile = ActiveWorkbook.Name
'Count the rows
SearchRowCount = Range("A1").CurrentRegion.Rows.Count
'Loop through all records
For A = 2 To SearchRowCount
Application.StatusBar = "PROCESING Line " & A & " From " & SearchRowCount
unwanted = Cells(A, "AJ").Value
If unwanted = 5 Then 'If this cell contains the unwanted info (5) then delete
Cells(A, "A").EntireRow.Delete 'Delete the row with 5yo horse
SearchRowCount = SearchRowCount - 1 'Reduce the total number of row count
A = A - 1 'Take the A count one back so that when the loop executes again it will read the new (next ) line
End If
Next A
'Tidy up and close everything
Application.DisplayAlerts = False
Workbooks(ourfile).Close True
Application.DisplayAlerts = False
GoTo 1002
1001 'Code comes here if no CSV was selected (user pressed cancel)
MsgBox ("You must chose a system")
1002I hope that I have obayed all the required bylaws of this forum. I reaklise that this question is 3 fold but each is tied into each other
Imnot sure that spliting them into each section would have been a workable solution. Please let me know if I posted incorectly.
Michal