Stranded Maiden needs help with macro to convert excel to csv

[victoriataylor007]

Hi

I really need help with a macro to convert excel into a specific csv file format. I have recorded a macro which will make an excel input sheet go into the correct format I need in excel - but what I really need is for it to be a CSV upload file. So once the excel file is in the correct format I then have to go through a process of saving the file as CSV type, re-opening the file in text, deleting all the extra commas and then resaving it as a text file before I can use the upload file. What I really want is for the macro to save the file as a proper CSV file so I dont have to go through those extra steps:

Here is my macro please can someone help me!

Sub CPASupload()
'
' CPASupload Macro
'
'
Sheets("Sheet2").Select
Range("A1").Select
ActiveCell.FormulaR1C1 = "H"
Range("B1").Select
Selection.NumberFormat = "@"
Range("C1").Select
ActiveCell.FormulaR1C1 = "1"
Range("D1").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[9]C[2]"
Range("E1").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[12]C[2]"
Range("F1").Select
ActiveCell.FormulaR1C1 = "=RC[1]"
Range("G1").Select
ActiveCell.FormulaR1C1 = _
"=CONCATENATE(RIGHT(Sheet1!R8C2,4),LEFT(Sheet1!R8C2,2),R1C9)"
Range("H1").Select
ActiveCell.FormulaR1C1 = "=LEFT(Sheet1!R[7]C[-6], LEN(Sheet1!R[7]C[-6])-5)"
Range("I1").Select
ActiveCell.FormulaR1C1 = "=RIGHT(RC[-1],2)"
Range("J1").Select
ActiveCell.FormulaR1C1 = _
"=IF(Sheet1!R[16]C[-9]>0,CONCATENATE(RIGHT(Sheet1!R11C6,4),LEFT(Sheet1!R11C6,2),R1C12),"""")"
Range("J1").Select
ActiveCell.FormulaR1C1 = _
"=IF(Sheet1!R[16]C[-9]>0,CONCATENATE(RIGHT(Sheet1!R11C6,4),LEFT(Sheet1!R11C6,2),R1C12),"""")"
Range("K1").Select
ActiveCell.FormulaR1C1 = "=LEFT(Sheet1!R[10]C[-5], LEN(Sheet1!R[10]C[-5])-5)"
Range("L1").Select
ActiveCell.FormulaR1C1 = "=RIGHT(RC[-1],2)"
Range("M1").Select
ActiveCell.FormulaR1C1 = _
"=IF(Sheet1!R[16]C[-9]>0,CONCATENATE(RIGHT(Sheet1!R11C3,4),LEFT(Sheet1!R11C3,2),R1C12),"""")"
Range("M2").Select
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 6
Range("N1").Select
ActiveCell.FormulaR1C1 = "=LEFT(Sheet1!R[10]C[-11], LEN(Sheet1!R[10]C[-11])-5)"
Range("O1").Select
ActiveCell.FormulaR1C1 = "=RIGHT(RC[-1],2)"
Range("T2").Select
ActiveCell.FormulaR1C1 = "=IF(Sheet1!R[15]C1>0,Sheet1!R[15]C4,"""")"
Range("S2").Select
ActiveCell.FormulaR1C1 = "=IF(Sheet1!R[15]C1>0,Sheet1!R[15]C2,"""")"
Range("R2").Select
ActiveCell.FormulaR1C1 = "=IF(Sheet1!R[15]C1>0,Sheet1!R[15]C3,"""")"
Range("N2").Select
ActiveCell.FormulaR1C1 = _
"=IF(Sheet1!R[15]C[-13]>0,CONCATENATE(RIGHT(Sheet1!R11C6,4),LEFT(Sheet1!R11C6,2),R1C12),"""")"
Range("M2").Select
ActiveCell.FormulaR1C1 = _
"=IF(Sheet1!R[15]C[-12]>0,CONCATENATE(RIGHT(Sheet1!R11C3,4),LEFT(Sheet1!R11C3,2),R1C15),"""")"
Range("L2").Select
ActiveCell.FormulaR1C1 = "=IF(Sheet1!R[15]C[-11]>0,""D1"","""")"
Range("K2").Select
ActiveCell.FormulaR1C1 = "=IF(Sheet1!R[15]C[-10]>0,""D"","""")"
Range("J2").Select
ActiveCell.FormulaR1C1 = "=IF(Sheet1!R[15]C[-9]>0,Sheet1!R[15]C[-2],"""")"
Range("I2").Select
ActiveCell.FormulaR1C1 = "=IF(Sheet1!R[15]C[-8]>0,Sheet1!R[15]C[-2],"""")"
Range("H2").Select
ActiveCell.FormulaR1C1 = "=IF(Sheet1!R[15]C[-7]>0,Sheet1!R[15]C[-2],"""")"
Range("G2").Select
ActiveCell.FormulaR1C1 = "=IF(Sheet1!R[15]C[-6]>0,Sheet1!R[15]C[-2],"""")"
Range("G3").Select
ActiveWindow.SmallScroll ToRight:=-9
Range("B2").Select
ActiveCell.FormulaR1C1 = "=IF(Sheet1!R[15]C[-1]>0,Sheet1!R[15]C[-1],"""")"
Range("A2").Select
ActiveCell.FormulaR1C1 = "=IF(Sheet1!R[15]C>0,""D"","""")"
Range("A2:T2").Select
Selection.AutoFill Destination:=Range("A2:T108"), Type:=xlFillDefault
Range("A2:T108").Select
ActiveWindow.ScrollRow = 82
ActiveWindow.ScrollRow = 69
ActiveWindow.ScrollRow = 33
ActiveWindow.ScrollRow = 19
ActiveWindow.ScrollRow = 13
ActiveWindow.ScrollRow = 4
ActiveWindow.ScrollRow = 1
ActiveWindow.ScrollRow = 2
ActiveWindow.ScrollRow = 3
ActiveWindow.ScrollRow = 5
ActiveWindow.ScrollRow = 8
ActiveWindow.ScrollRow = 10
ActiveWindow.ScrollRow = 11
ActiveWindow.ScrollRow = 15
ActiveWindow.ScrollRow = 21
ActiveWindow.ScrollRow = 32
ActiveWindow.ScrollRow = 41
ActiveWindow.ScrollRow = 55
ActiveWindow.ScrollRow = 62
ActiveWindow.ScrollRow = 68
ActiveWindow.ScrollRow = 75
ActiveWindow.ScrollRow = 78
ActiveWindow.ScrollRow = 83
ActiveWindow.ScrollRow = 84
ActiveWindow.SmallScroll Down:=12
Selection.AutoFill Destination:=Range("A2:T200"), Type:=xlFillDefault
Range("A2:T200").Select
ActiveWindow.ScrollRow = 193
ActiveWindow.ScrollRow = 170
ActiveWindow.ScrollRow = 135
ActiveWindow.ScrollRow = 72
ActiveWindow.ScrollRow = 26
ActiveWindow.ScrollRow = 18
ActiveWindow.ScrollRow = 14
ActiveWindow.ScrollRow = 4
ActiveWindow.ScrollRow = 1
Range("M10").Select
ActiveWindow.SmallScroll ToRight:=-12
Cells.Select
Selection.Copy
Sheets("Sheet3").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("G1").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = ""
Range("H1").Select
ActiveCell.FormulaR1C1 = ""
Range("I1").Select
ActiveCell.FormulaR1C1 = ""
Range("J1").Select
ActiveCell.FormulaR1C1 = ""
Range("K1").Select
ActiveCell.FormulaR1C1 = ""
Range("L1").Select
ActiveCell.FormulaR1C1 = ""
Range("M1").Select
ActiveCell.FormulaR1C1 = ""
Range("N1").Select
ActiveCell.FormulaR1C1 = ""
Range("O1").Select
ActiveCell.FormulaR1C1 = ""
Range("Q2").Select
ActiveWindow.SmallScroll ToRight:=-12
Sheets("Sheet1").Select
Sheets("Sheet1").Name = "delete"
Sheets("Sheet2").Select
Sheets("Sheet2").Name = "delete2"
Sheets("Sheet3").Select
Sheets("Sheet3").Name = "upload"
Range("E18").Select
Sheets("delete2").Select
ActiveCell.FormulaR1C1 = "0001"
Range("B1").Select
Selection.Copy
Sheets("upload").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("F10").Select
ActiveWindow.SmallScroll Down:=-15
Sheets("delete2").Select
ActiveWindow.SelectedSheets.Delete
Sheets("delete").Select
ActiveWindow.SelectedSheets.Delete
Range("D14").Select
End Sub

 

[Gerald Higgins]

Hi, welcome to the board.

I can't see anything in your code that actually saves the file as a csv.

When I tried recording a macro to do that, it came up with something like this

ActiveWorkbook.SaveAs Filename:="C:\CSV test.csv", _
        FileFormat:=xlCSV, CreateBackup:=False

Does that do what you want ?

 

[victoriataylor007]

Hi Gerald - Thank you so much! That is almost exactly what I need. I see that your piece of code saves the file as a "xlCSV" is their a way that I can get it to save as a text csv file?

Unfortunately it needs to be saved as a txt file for it to upload to our system but it also needs to be comma delimited.

Really appreciate your help on this!

Thanks

 

[Asator]

CSV is plaintext as it is, so just change .csv to .txt

 

[victoriataylor007]

Hi Gerald - Thank you so much! That actually is almost exactly what I need. Now that I have set my default programme to open these files as txt I can upload.

Now I just have to figure out how to get rid of all the extra commas... but that is another days work!

Thanks