Changing Data Source - .CSV to .xlsx

sholland · Mar 22, 2018

Hi,

I am trying to adjust a previous code to pull data from a .xlsx instead of the .csv file. The .csv file was rounding decimals to the first decimal place and the .xlsx is fixing that issue.

Could you please explain why removing the bolded section below won't allow the data to be pulled from the new .xlsx file?
It allows my to select the .xlsx file when running the macro.

Code:

Private Function Get_VCON_Filename() As String
 Dim VCON_Filename As String
 
    ChDrive "S:"
    ChDir "S:\Investment Operations\Trade Support\Master Files"
 
    VCON_Filename = ""
    
    VCON_Filename = Application.GetOpenFilename[B]("Text Files (*.csv), *.csv")[/B]
    If VCON_Filename = "False" Then
        Get_VCON_Filename = VCON_Filename
        Exit Function
    End If    Get_VCON_Filename = VCON_Filename
End Function

Public Sub Read_VCON_File()

Thank you, I really appreciate the help.

p45cal · Mar 22, 2018

It rather depends on what you try to do with Get_VCON_Filename once you've assigned a file name to it.
It would be useful to see that code.

sholland · Mar 22, 2018

p45cal said:
It rather depends on what you try to do with Get_VCON_Filename once you've assigned a file name to it.
It would be useful to see that code.

Code:

Function FindingLastRow(shtName As String) As Long
'PURPOSE: Find the last row number of a range
'SOURCE: [URL="http://www.TheSpreadsheetGuru.com"]www.TheSpreadsheetGuru.com[/URL]
Dim sht As Worksheet
Dim LastRow As Long
Set sht = ThisWorkbook.Worksheets(shtName)
  LastRow = sht.Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row
  
  FindingLastRow = LastRow
  
End Function
Public Sub Save_VCON_Data()
Dim worksheet_saved_row As Integer
Dim lrow As Long
Dim row_accepted As String
Dim row_is_dupe As String
Dim sequence As String
Dim seqnum As Long
Dim row_count As Integer
Dim ImportSheet As String
Dim SaveSheet As String
ImportSheet = "VCON Import"
SaveSheet = "VCON Saved Values"
'Delete Current Contents of the "VCON Import" Worksheet
'Worksheets("VCON Saved Values").Activate
'Range("A1:Z10000").Select
'Selection.ClearContents
'Range("A1").Select
' Copy Data
Worksheets(ImportSheet).Activate
row_count = FindingLastRow(ImportSheet)
row_count = row_count + 1
Range("A4:AD" & row_count).Select
Selection.Copy
Sheets(SaveSheet).Select
row_count = FindingLastRow(SaveSheet)
row_count = row_count + 1
Range("A" & row_count).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
Range("A1").Select
Worksheets(ImportSheet).Activate
Range("A1").Select

' Delete rows where Not Accepted
Worksheets(SaveSheet).Activate
worksheet_saved_row = 4
sequence = Trim(Worksheets(SaveSheet).Range("A" & worksheet_saved_row))
Do While sequence <> ""
    row_accepted = Worksheets(SaveSheet).Range("U" & worksheet_saved_row)
    If row_accepted <> "OK" Then
        ' Delete the row
        Rows(worksheet_saved_row & ":" & worksheet_saved_row).Select
        Selection.Delete Shift:=xlUp
    Else
        worksheet_saved_row = worksheet_saved_row + 1
    End If
    
    sequence = Trim(Worksheets(SaveSheet).Range("A" & worksheet_saved_row))
Loop
If row_count > 4 Then
' Delete rows that were previously validated
    worksheet_saved_row = row_count
    old_row_count = row_count - 1
    sequence = Trim(Worksheets(SaveSheet).Range("A" & worksheet_saved_row))
    Do While sequence <> ""
    
        seqnum = CLng(sequence)
        If IsNumeric(Application.Match(seqnum, Range("A4:A" & old_row_count), 0)) Then
        ' Delete the row
            Rows(worksheet_saved_row & ":" & worksheet_saved_row).Select
            Selection.Delete Shift:=xlUp
        Else
            worksheet_saved_row = worksheet_saved_row + 1
        End If
    
        sequence = Trim(Worksheets(SaveSheet).Range("A" & worksheet_saved_row))
    Loop
End If

    
End Sub
 
 Private Function Get_VCON_Filename() As String
 Dim VCON_Filename As String
 
    ChDrive "S:"
    ChDir "S:\Investment Operations\Trade Support\Master Files"
 
    VCON_Filename = ""
    
    VCON_Filename = Application.GetOpenFilename("Text Files (*.csv), *.csv")
    If VCON_Filename = "False" Then
        Get_VCON_Filename = VCON_Filename
        Exit Function
    End If
    Get_VCON_Filename = VCON_Filename
End Function

Public Sub Read_VCON_File()
'-------------------------------------------------------------------------------------------
' Purpose: To read the data from the S:\IT TMC Shared\RepoBrokerProcess\150819MM.csv file
'          into the "VCON Import" Worksheet.
'-------------------------------------------------------------------------------------------
Dim worksheet_row As Integer
Dim data_line As String
Dim SplitArray() As String
Dim VCON_Filename_WithPath As String
On Error GoTo Errorhandler

VCON_Filename_WithPath = Get_VCON_Filename()
'MsgBox VCON_Filename_WithPath
'Delete Current Contents of the "VCON Import" Worksheet
Worksheets("VCON Import").Activate
Range("A4:Z10000").Select
Selection.ClearContents
Range("A1").Select
' Set the start row, the header row is static in Row 3
worksheet_row = 4
' Open the data file, the output from AVALU094.CUS
Open VCON_Filename_WithPath For Input As #2
' Main Loop for Reading the data file
Do While Not EOF(2)    ' Loop until end of file.
    Line Input #2, data_line    ' Read line into variable.
    
    SplitArray() = Split(data_line, ",")
    
    
    
    ' Data Validation
    If Trim(data_line) = "" Then GoTo get_next_record
        
    ' Data Validation
    If Left(Trim(data_line), 22) = "BLOT Download For User" Then GoTo get_next_record
        
    ' Data Validation
    If Left(Trim(data_line), 6) = ",,,,,," Then GoTo get_next_record
        
    ' Data Validation
    If Left(Trim(data_line), 4) = "Seq#" Then GoTo get_next_record
           
    
    ' Write the parsed data, iff we have the correct number of fields
    If UBound(SplitArray()) >= 13 Then
    
        ' Column Data {Seq#,Side,Brkr,Trader Name,Cusip,Security,Price,Yield,Qty (M),Rate,SetDt Yr,Coll Mat Dt,Term Days,Settl Money,Term Money}
    
        ' Parse the data line
       Worksheets("VCON Import").Range("A" & worksheet_row) = SplitArray(0)
       Worksheets("VCON Import").Range("B" & worksheet_row) = SplitArray(1)
       Worksheets("VCON Import").Range("C" & worksheet_row) = SplitArray(2)
       Worksheets("VCON Import").Range("D" & worksheet_row) = SplitArray(3)
       Worksheets("VCON Import").Range("E" & worksheet_row) = Chr(39) & SplitArray(4) 'Add a single quote to the front of CUSIP to prevent mistranslation
       Worksheets("VCON Import").Range("F" & worksheet_row) = SplitArray(5)
       Worksheets("VCON Import").Range("G" & worksheet_row) = SplitArray(6)
       Worksheets("VCON Import").Range("H" & worksheet_row) = SplitArray(7)
       Worksheets("VCON Import").Range("I" & worksheet_row) = SplitArray(8)
       Worksheets("VCON Import").Range("J" & worksheet_row) = SplitArray(9)
       Worksheets("VCON Import").Range("K" & worksheet_row) = SplitArray(10)
       Worksheets("VCON Import").Range("L" & worksheet_row) = SplitArray(11)
       Worksheets("VCON Import").Range("M" & worksheet_row) = SplitArray(12)
       Worksheets("VCON Import").Range("N" & worksheet_row) = SplitArray(13)
       Worksheets("VCON Import").Range("O" & worksheet_row) = SplitArray(14)
       Worksheets("VCON Import").Range("P" & worksheet_row) = SplitArray(15)
       Worksheets("VCON Import").Range("Q" & worksheet_row) = SplitArray(16)
       Worksheets("VCON Import").Range("R" & worksheet_row) = SplitArray(17)
       Worksheets("VCON Import").Range("S" & worksheet_row) = SplitArray(18)
            
       ' Bloomberg Bid Price        (PX_BID)
       Worksheets("VCON Import").Range("V" & worksheet_row) = "=IF($E" & worksheet_row & "="""","""",BDP($E" & worksheet_row & "&"" Govt"",""PX_BID""))"
       ' Bloomberg Bid Ask Price    (PX_ASK)
       Worksheets("VCON Import").Range("W" & worksheet_row) = "=IF($E" & worksheet_row & "="""","""",BDP($E" & worksheet_row & "&"" Govt"",""PX_ASK""))"
       ' Bloomberg Bid Yield        (YLD_YTM_BID)
       Worksheets("VCON Import").Range("X" & worksheet_row) = "=IF($E" & worksheet_row & "="""","""",BDP($E" & worksheet_row & "&"" Govt"",""YLD_YTM_BID""))"
       ' Bloomberg Ask Yield        (YLD_YTM_ASK)
       Worksheets("VCON Import").Range("Y" & worksheet_row) = "=IF($E" & worksheet_row & "="""","""",BDP($E" & worksheet_row & "&"" Govt"",""YLD_YTM_ASK""))"
       ' Bloomberg Maturity       (MATURITY)
       Worksheets("VCON Import").Range("Z" & worksheet_row) = "=IF($E" & worksheet_row & "="""","""",BDP($E" & worksheet_row & "&"" Govt"",""MATURITY""))"
                   
       ' Check Maturity (5yr Max)
       Worksheets("VCON Import").Range("AA" & worksheet_row) = "=IF(A" & worksheet_row & "="""","""",IF(Z" & worksheet_row & "+0<=AA3,""OK"",""NOT OK""))"
       ' Check Price (10 pbs max)
       Worksheets("VCON Import").Range("AB" & worksheet_row) = "=IF(A" & worksheet_row & "="""","""",IF((G" & worksheet_row & "=""""),"""",IF((G" & worksheet_row & "<(V" & worksheet_row & "*(1-$AB$3))),""IN FAVOUR"",IF(G" & worksheet_row & ">(W" & worksheet_row & "*(1+$AB$3)),""NOT OK"",""OK""))))"
       ' Check Yield (10 bps max)
       Worksheets("VCON Import").Range("AC" & worksheet_row) = "=IF(A" & worksheet_row & "="""","""",IF(OR((LEFT(E" & worksheet_row & ",6)=""1350Z7""),(LEFT(E" & worksheet_row & ",6)=""0985ZU""),(LEFT(E" & worksheet_row & ",6)=""6832Z5"")),IF((H" & worksheet_row & "<(X" & worksheet_row & "*(1-$AC$3))),""IN FAVOUR"",IF(H" & worksheet_row & ">(Y" & worksheet_row & "*(1+$AC$3)),""NOT OK"",""OK"")),""NOT T-BILL""))"
            
       ' Validation Check
       Worksheets("VCON Import").Range("U" & worksheet_row) = "=IF($E" & worksheet_row & "="""","""",IF(OR(AND(AA" & worksheet_row & "=""OK"",AB" & worksheet_row & "=""OK"",AC" & worksheet_row & "=""NOT T-BILL""),AND(AA" & worksheet_row & "=""OK"",AB" & worksheet_row & "=""OK"",AC" & worksheet_row & "=""OK"")),""OK"",""NOT OK""))"
       
       ' Load Time
       Worksheets("VCON Import").Range("AD" & worksheet_row) = Now
       
       
       worksheet_row = worksheet_row + 1
    End If
    
get_next_record:
Loop
Close 2
Exit Sub
Errorhandler:
    MsgBox "An error has occurred in the Read_REPO385_File subroutine: " & Err.Description
    Close 2
End Sub

snb · Mar 23, 2018

The .csv file was rounding decimals to the first decimal place and the .xlsx is fixing that issue.

A csv-file doesn't do anything: it contains data: nothing more, nothing less.

Excel adapts data without the user's consent.

Changing Data Source - .CSV to .xlsx

sholland

New member

p45cal

Super Moderator

sholland

New member

snb

New member