Solved: Allocations for CSV

[Klartigue]

I have to put my allocations for one broker in a specific format. See attached document with my questions and comments.

Thanks

[Tinbendr]

I little more info in the message body would be appreciated.

Do you want the sheet formatted to the transposed look, or do you want a CSV file with the data?

e.g.
666,6666666666,S,55000,93974CGH7,118.097,,,VS,2940,,

[Klartigue]

Well I have to upload this file as .CSV so a CSV file with the data would be awesome. In column A, I would place the CSV Fields: And then in Column B, C, D, etc.. I would place the data for each allocation row (so from row 5 to lastrow). Data needs to be vertical so see below:

CSV FIELDS
OFC #
ACCT #
B/S
QUANTITY
SYMBOL
XPRICE
LIMIT
SETT
BKR
DLRAMT
DEALER
VSP
OCS/ACS
FI_DOLLAR_COMM

the limit, dlramt, vs, ocs/acs, and FI_Dollar_Comm will stay blank for all the allocations. SETT is the settlement date, which has to be in yyyy-mm-dd or yyyy/mm/dd format

Thanks for the help!

[Tinbendr]

OK this is what I've come up with.

[vba]Option Explicit
Sub Export2CSV()
Dim aSheet As Worksheet
Dim LastRow As Long
Dim aCell As Range
Set aSheet = ActiveSheet
Open "Allocation.csv" For Output As #1
Print #1, "OFC #,ACCT #,B/S,QUANTITY,SYMBOL,XPRICE,LIMIT,SETT,BKR VS,DLRAMT,DEALER,VSP,OCS/ACS,FI_DOLLAR_COMM"
LastRow = aSheet.Range("A65536").End(xlUp).Row
For Each aCell In aSheet.Range("A5:A" & LastRow)
Print #1, Left(aCell.Offset(0, 6), 3) & "," & _
aCell.Offset(0, 6) & "," & _
aCell.Offset(0, 3) & "," & _
aCell.Offset(0, 7) & "," & _
aCell.Offset(0, 2) & "," & _
aCell.Offset(0, 4) & ",," & _
Format(aCell, "YYYY/MM/DD") & "," & _
aCell.Offset(0, 13) & ",," & _
aCell.Offset(0, 14) & ",,,"
Next
Close #1
End Sub

[/vba]

This will output all rows to a CSV file.

If you look at this with Notepad, you will see it is like the example I gave earlier.

Hope this helps.

[Klartigue]

When I try that it highlights Offset (see red below) and says ambiguous name detected.

[VBA]

Option Explicit
Sub Export2CSV()
Dim aSheet As Worksheet
Dim LastRow As Long
Dim aCell As Range
Set aSheet = ActiveSheet
Open "Allocation.csv" For Output As #1
Print #1, "OFC #,ACCT #,B/S,QUANTITY,SYMBOL,XPRICE,LIMIT,SETT,BKR VS,DLRAMT,DEALER,VSP,OCS/ACS,FI_DOLLAR_COMM"
LastRow = aSheet.Range("A65536").End(xlUp).Row
For Each aCell In aSheet.Range("A5:A" & LastRow)
Print #1, Left(aCell.Offset(0, 6), 3) & "," & _
aCell.Offset(0, 6) & "," & _
aCell.Offset(0, 3) & "," & _
aCell.Offset(0, 7) & "," & _
aCell.Offset(0, 2) & "," & _
aCell.Offset(0, 4) & ",," & _
Format(aCell, "YYYY/MM/DD") & "," & _
aCell.Offset(0, 13) & ",," & _
aCell.Offset(0, 14) & ",,,"
Next
Close #1
End Sub

[/VBA]

[Tinbendr]

I copied your message code right off the page and it ran as described.

[Klartigue]

what excel are you using? Im using Excel 2010

[Klartigue]

I still get .offset as ambiguous name detected..hmm.

[Klartigue]

Do i have to have that option explicit in there?

[Tinbendr]

what excel are you using? Im using Excel 2010

I wrote it in 2007, but I've tried it since I'm home with 2010 and it runs fine.

That error usually means that there are two subs, variables, etc with the same name referenced in the code.

Try this instead.
[VBA]Sub Export2CSV()
Dim aSheet As Worksheet
Dim LastRow As Long
Dim aCell As Range
Set aSheet = ActiveSheet
Open "Allocation.csv" For Output As #1
Print #1, "OFC #,ACCT #,B/S,QUANTITY,SYMBOL,XPRICE,LIMIT,SETT,BKR VS,DLRAMT,DEALER,VSP,OCS/ACS,FI_DOLLAR_COMM"
LastRow = aSheet.Range("A65536").End(xlUp).Row
For Each aCell In aSheet.Range("A5:A" & LastRow)
Print #1, Left(aCell.Offset(0, 6), 3) & ",";
Print #1, aCell.Offset(0, 6) & ",";
Print #1, aCell.Offset(0, 3) & ",";
Print #1, aCell.Offset(0, 7) & ",";
Print #1, aCell.Offset(0, 2) & ",";
Print #1, aCell.Offset(0, 4) & ",,";
Print #1, Format(aCell, "YYYY/MM/DD") & ",";
Print #1, aCell.Offset(0, 13) & ",,";
Print #1, aCell.Offset(0, 14) & ",,,"
Next
Close #1
End Sub[/VBA]

[Bob Phillips]

[vba]Sub CreateCSV()
Dim wb As Workbook
Dim target As Worksheet
Dim source As Worksheet
Dim lastrow As Long
Dim nextrow As Long
Dim i As Long

Set source = ActiveSheet

Set wb = Workbooks.Add
Set target = wb.Worksheets(1)

With source

lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
nextrow = 1
For i = 5 To lastrow

target.Cells(nextrow, "A").Resize(14) = Application.Transpose(Array( _
"OFC #", "ACCT #", "B/S", "QUANTITY", "SYMBOL", _
"XPRICE", "LIMIT", "ETT", "BKR", "DLRAMT", _
"DEALER", "VSP", "OCS/ACS", "FI_DOLLAR_COMM"))
target.Cells(nextrow, "B").Value = Left$(.Cells(i, "G").Value, 3)
target.Cells(nextrow + 1, "B").Value = .Cells(i, "G").Value
target.Cells(nextrow + 2, "B").Value = .Cells(i, "D").Value
target.Cells(nextrow + 3, "B").Value = .Cells(i, "H").Value
target.Cells(nextrow + 4, "B").Value = .Cells(i, "C").Value
target.Cells(nextrow + 5, "B").Value = .Cells(i, "E").Value
target.Cells(nextrow + 8, "B").Value = .Cells(i, "N").Value
target.Cells(nextrow + 10, "B").Value = .Cells(i, "O").Value

nextrow = nextrow + 14
Next i
End With

Application.DisplayAlerts = False
wb.SaveAs "C:\users\klartigue\desktop\klartigue", xlCSV
wb.Close
End Sub
[/vba]

[Klartigue]

The above code works great! Although I see the fields start over starting in row 15, because they are doing the second allocation. Instead of having the fields start over, can I have the second allocation go in column C? See the red on the excel document. I would like to move those entries to column C starting in row 1 and build across rather than build down?

[Klartigue]

David - I did try your code as well but i got an ambiguous error for the word "format" in this part: Print #1, Format(aCell, "YYYY/MM/DD") & ",";

Xld - your code works! Now if i can just get the second allocation to go to column c inside of going below the first allocation..hmm,!

[Tinbendr]

I'm curious as a cat, now.

I've u/l the sample file I was working with. See if it errors on that.

Look in Module 1.

[Bob Phillips]

Is this what you mean?
[vba]Sub CreateCSV()
Dim wb As Workbook
Dim target As Worksheet
Dim source As Worksheet
Dim lastrow As Long
Dim nextcol As Long
Dim i As Long

Set source = ActiveSheet

Set wb = Workbooks.Add
Set target = wb.Worksheets(1)

With source

target.Cells(1, "A").Resize(14) = Application.Transpose(Array( _
"OFC #", "ACCT #", "B/S", "QUANTITY", "SYMBOL", _
"XPRICE", "LIMIT", "ETT", "BKR", "DLRAMT", _
"DEALER", "VSP", "OCS/ACS", "FI_DOLLAR_COMM"))

lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
nextcol = 2
For i = 5 To lastrow

target.Cells(1, nextcol).Value = Left$(.Cells(i, "G").Value, 3)
target.Cells(2, nextcol).Value = .Cells(i, "G").Value
target.Cells(3, nextcol).Value = .Cells(i, "D").Value
target.Cells(4, nextcol).Value = .Cells(i, "H").Value
target.Cells(5, nextcol).Value = .Cells(i, "C").Value
target.Cells(6, nextcol).Value = .Cells(i, "E").Value
target.Cells(9, nextcol).Value = .Cells(i, "N").Value
target.Cells(11, nextcol).Value = .Cells(i, "O").Value

nextcol = nextcol + 1
Next i
End With

Application.DisplayAlerts = False
wb.SaveAs "C:\users\bob\desktop\klartigue", xlCSV
wb.Close
End Sub
[/vba]

[Klartigue]

Yes, that is exactly what I mean! Thank you soo much!

[Klartigue]

The below code is what I am using right now. However, I found out it actually has to go horizontally, rather than vertically. So is there a way to just make everything horizontal, so column A is OFC #, column B is Acct #, column C is B/S, etc..and then have the allocation details below?

[VBA]Sub CreateCSV()
Dim wb As Workbook
Dim target As Worksheet
Dim source As Worksheet
Dim lastrow As Long
Dim nextcol As Long
Dim i As Long

Set source = ActiveSheet

Set wb = Workbooks.Add
Set target = wb.Worksheets(1)

With source

target.Cells(1, "A").Resize(14) = Application.Transpose(Array( _
"OFC #", "ACCT #", "B/S", "QUANTITY", "SYMBOL", _
"XPRICE", "LIMIT", "ETT", "BKR", "DLRAMT", _
"DEALER", "VSP", "OCS/ACS", "FI_DOLLAR_COMM"))

lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
nextcol = 2
For i = 5 To lastrow

target.Cells(1, nextcol).Value = Left$(.Cells(i, "G").Value, 3)
target.Cells(2, nextcol).Value = .Cells(i, "G").Value
target.Cells(3, nextcol).Value = .Cells(i, "D").Value
target.Cells(4, nextcol).Value = .Cells(i, "H").Value
target.Cells(5, nextcol).Value = .Cells(i, "C").Value
target.Cells(6, nextcol).Value = .Cells(i, "E").Value
target.Cells(9, nextcol).Value = .Cells(i, "N").Value
target.Cells(11, nextcol).Value = .Cells(i, "O").Value

nextcol = nextcol + 1
Next i
End With

End Sub[/VBA]

[Klartigue]

Part of the above post..see the excel document attached.

[Klartigue]

Any ideas on how to alter the above code so the results go across the columns rather than down columns?

[Klartigue]

I just ended up doing it like this to get things to go in rows rather than columns:

[VBA]
Sub MSSVCSV()
Call LabelColumns
Call Sheet1
Call Pasteaccountnumbers
Call ExpandColumns
Call Macro3
Call MoveData
Call Dealer
Call SaveMSSBCSV
End Sub
Sub LabelColumns()
'
Sheets("Sheet2").Select
Application.Run "BLPLinkReset"
ActiveCell.FormulaR1C1 = "OFC #"
Range("B1").Select
ActiveCell.FormulaR1C1 = "ACCT #"
Range("C1").Select
ActiveCell.FormulaR1C1 = "B/S"
Range("D1").Select
ActiveCell.FormulaR1C1 = "QUANTITY"
Range("E1").Select
ActiveCell.FormulaR1C1 = "SYMBOL"
Range("F1").Select
ActiveCell.FormulaR1C1 = "XPRICE"
Range("G1").Select
ActiveCell.FormulaR1C1 = "LIMIT"
Range("H1").Select
ActiveCell.FormulaR1C1 = "SETT"
Range("I1").Select
ActiveCell.FormulaR1C1 = "BKR"
Range("J1").Select
ActiveCell.FormulaR1C1 = "DLRAMT"
Range("K1").Select
ActiveCell.FormulaR1C1 = "DEALER"
Range("L1").Select
ActiveCell.FormulaR1C1 = "VSP"
Range("M1").Select
ActiveCell.FormulaR1C1 = "OCS/ACS"
Range("N1").Select
ActiveCell.FormulaR1C1 = "FI_DOLLAR_COMM"
Range("A2").Select
End Sub
Sub Sheet1()
Sheets("Sheet1").Select
Application.Run "BLPLinkReset"
End Sub
Sub Pasteaccountnumbers()
'
Range("G5:G36").Select
Selection.Copy
Sheets("Sheet2").Select
Application.Run "BLPLinkReset"
Range("B2").Select
ActiveSheet.Paste

End Sub
Sub ExpandColumns()
'
Columns("B:B").Select
Columns("B:B").EntireColumn.Autofit
Selection.ColumnWidth = 13
End Sub
Sub Macro3()
'
Dim lastrow As Long
Dim i As Long
With ActiveSheet

lastrow = .Cells(.Rows.Count, "B").End(xlUp).Row
For i = 2 To lastrow

.Cells(i, "A").Select
ActiveCell.FormulaR1C1 = "=LEFT(Sheet1!R[3]C[6],3)"

Next i
End With

End Sub
Sub MoveData()
'
Sheets("Sheet1").Select
Application.Run "BLPLinkReset"
Range("D539").Select
Selection.Copy
Sheets("Sheet2").Select
Application.Run "BLPLinkReset"
Range("C2").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
Application.Run "BLPLinkReset"
Range("H5:H39").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
Application.Run "BLPLinkReset"
Range("D2").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
Application.Run "BLPLinkReset"
Range("C5:C39").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
Application.Run "BLPLinkReset"
Range("E2").Select
ActiveSheet.Paste
Columns("E:E").EntireColumn.Autofit
Columns("D").ColumnWidth = 10.57
Columns("E:E").ColumnWidth = 10.86
Sheets("Sheet1").Select
Application.Run "BLPLinkReset"
Range("E5:E39").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
Application.Run "BLPLinkReset"
Range("F2").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
Application.Run "BLPLinkReset"
Range("A5:A39").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
Application.Run "BLPLinkReset"
Range("H2").Select
ActiveSheet.Paste
Columns("H:H").ColumnWidth = 11.86
Sheets("Sheet1").Select
Application.Run "BLPLinkReset"
Range("N5:N39").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
Application.Run "BLPLinkReset"
Range("I2").Select
ActiveSheet.Paste
End Sub
Sub Dealer()
'
Sheets("Sheet1").Select
Application.Run "BLPLinkReset"
Range("O5:O39").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
Application.Run "BLPLinkReset"
Range("K2").Select
ActiveSheet.Paste
End Sub
Sub SaveMSSBCSV()
ChDir "G:\Katherine Lartigue\Allocations\MSSB CSV UPLOAD"
ActiveWorkbook.Saveas Filename:= _
"G:\Katherine Lartigue\Allocations\MSSB CSV UPLOAD\Avalon Trades.csv", _
FileFormat:=xlCSV, CreateBackup:=False

End Sub
[/VBA]