Results 1 to 4 of 4

Thread: Changing Data Source - .CSV to .xlsx

  1. #1

    Changing Data Source - .CSV to .xlsx



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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("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()
    Thank you, I really appreciate the help.
    Last edited by p45cal; 2018-03-22 at 09:35 PM. Reason: code tags

  2. #2
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,974
    Articles
    0
    Excel Version
    365
    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.

  3. #3
    Quote Originally Posted by p45cal View Post
    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: www.TheSpreadsheetGuru.com
    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
    Last edited by Pecoflyer; 2018-03-23 at 08:51 AM. Reason: Added tags

  4. #4
    Conjurer snb's Avatar
    Join Date
    May 2013
    Posts
    376
    Articles
    0
    Excel Version
    2020
    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.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •