none
[Help]Updation RRS feed

  • Question

  • I have an excel that contains the current pricing of components for products across out branches. Our central system lets me generate the average selling price of components that I then use against the budgets. Now, the system generates a .CSV file for this purpose and I'd like my excel to run the run (ActiveX command button or anything) and pick the prices from the CSV and update against the SKU's in my excel (in contains limited items compared to the excel).

    How do I go about this ?

    Sunday, January 3, 2016 10:24 AM

Answers

  • >>>Now, the system generates a .CSV file for this purpose and I'd like my excel to run the run (ActiveX command button or anything) and pick the prices from the CSV and update against the SKU's in my excel (in contains limited items compared to the excel).

    How do I go about this ?<<<

    According to your description, I suggest that you could import csv files using VBA macros, then you could update Excel special Range, you could refer to below code:

    Function doFileQuery(filename As String, outSheet As String) As Boolean
        Dim rootDir As String
        rootDir = "D:\"
        Dim connectionName As String
        connectionName = "TEXT;" + rootDir + "\" + filename
        With Worksheets(outSheet).QueryTables.Add(Connection:=connectionName, Destination:=Worksheets(outSheet).Range("A1"))
            .Name = filename
            .FieldNames = True
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .RefreshStyle = xlOverwriteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .TextFilePromptOnRefresh = False
            .TextFilePlatform = 437
            .TextFileStartRow = 1
            .TextFileParseType = xlDelimited
            .TextFileTextQualifier = xlTextQualifierDoubleQuote
            .TextFileConsecutiveDelimiter = False
            .TextFileTabDelimiter = False
            .TextFileSemicolonDelimiter = False
            .TextFileCommaDelimiter = True
            .TextFileSpaceDelimiter = False
            .Refresh BackgroundQuery:=False
        End With
    End Function

    Otherwise you could refer to below code to get value from .csv file:

    Dim fso As Variant
    Dim objStream As Variant
    Dim objFile As Variant
    Dim dataItem() As String
    i = 0
    
    Set fso = CreateObject("Scripting.FileSystemObject")
    If fso.FileExists("D:\April.csv") Then
       Set objStream = fso.OpenTextFile("D:\April.csv", 1, False, 0)
    End If
    Do While Not objStream.AtEndOfStream
       strLine = objStream.ReadLine
       Debug.Print strLine
    
       dataItem = Split(strLine, ",")
    
       For Each v In dataItem
           Debug.Print v
       Next v
    
       i = i + 1
    Loop
    

    For more information, click here to refer about Range Object (Excel)

    • Proposed as answer by Ciprian Lupu Tuesday, January 5, 2016 9:19 AM
    • Marked as answer by David_JunFeng Sunday, January 17, 2016 2:51 PM
    Monday, January 4, 2016 2:37 AM

All replies

  • >>>Now, the system generates a .CSV file for this purpose and I'd like my excel to run the run (ActiveX command button or anything) and pick the prices from the CSV and update against the SKU's in my excel (in contains limited items compared to the excel).

    How do I go about this ?<<<

    According to your description, I suggest that you could import csv files using VBA macros, then you could update Excel special Range, you could refer to below code:

    Function doFileQuery(filename As String, outSheet As String) As Boolean
        Dim rootDir As String
        rootDir = "D:\"
        Dim connectionName As String
        connectionName = "TEXT;" + rootDir + "\" + filename
        With Worksheets(outSheet).QueryTables.Add(Connection:=connectionName, Destination:=Worksheets(outSheet).Range("A1"))
            .Name = filename
            .FieldNames = True
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .RefreshStyle = xlOverwriteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .TextFilePromptOnRefresh = False
            .TextFilePlatform = 437
            .TextFileStartRow = 1
            .TextFileParseType = xlDelimited
            .TextFileTextQualifier = xlTextQualifierDoubleQuote
            .TextFileConsecutiveDelimiter = False
            .TextFileTabDelimiter = False
            .TextFileSemicolonDelimiter = False
            .TextFileCommaDelimiter = True
            .TextFileSpaceDelimiter = False
            .Refresh BackgroundQuery:=False
        End With
    End Function

    Otherwise you could refer to below code to get value from .csv file:

    Dim fso As Variant
    Dim objStream As Variant
    Dim objFile As Variant
    Dim dataItem() As String
    i = 0
    
    Set fso = CreateObject("Scripting.FileSystemObject")
    If fso.FileExists("D:\April.csv") Then
       Set objStream = fso.OpenTextFile("D:\April.csv", 1, False, 0)
    End If
    Do While Not objStream.AtEndOfStream
       strLine = objStream.ReadLine
       Debug.Print strLine
    
       dataItem = Split(strLine, ",")
    
       For Each v In dataItem
           Debug.Print v
       Next v
    
       i = i + 1
    Loop
    

    For more information, click here to refer about Range Object (Excel)

    • Proposed as answer by Ciprian Lupu Tuesday, January 5, 2016 9:19 AM
    • Marked as answer by David_JunFeng Sunday, January 17, 2016 2:51 PM
    Monday, January 4, 2016 2:37 AM
  • Ummm, but I guess this asks me to open the CSV to read the data. What I'd like is to run a command where the excel automatically searches the csv file for appropriate SKU's with the excel list and updates the new price from the CSV to the excel in the appropriate column against the SKU. Rest of the data the excel can ignore.

    Can something like this be done ?

    Monday, January 4, 2016 6:24 AM
  • >>>What I'd like is to run a command where the excel automatically searches the csv file for appropriate SKU's with the excel list and updates the new price from the CSV to the excel in the appropriate column against the SKU. Rest of the data the excel can ignore.

    According to your description, I have made a sample to let you refer to it, then you could modify them based on your requirement:

    Sub ImportCSV()
    
        Dim ws As Worksheet, destRng As Range, fileName As String
        Set ws = Worksheets("Sheet2")
        Set destRng = ws.Range("A1")
        fileName = "D:\T.csv"
        With ws.QueryTables.Add(Connection:="TEXT;" & fileName & "", Destination:=destRng)
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 852
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        'Select your delimiter - selected below for Comma
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .TextFileTrailingMinusNumbers = True
        'This will refresh the query
        .Refresh BackgroundQuery = False
        End With
        
        findString = "00-12-1234"
        Dim priceValue As String
        With ws.Range("A:A")
                Set Rng = .Find(What:=findString, _
                                After:=.Cells(.Cells.Count), _
                                LookIn:=xlValues, _
                                LookAt:=xlWhole, _
                                SearchOrder:=xlByRows, _
                                SearchDirection:=xlNext, _
                                MatchCase:=False)
                If Not Rng Is Nothing Then
                     priceValue = Rng.Offset(, 1)
                     Debug.Print Rng.Count
                     Debug.Print Rng.Offset(, 1)
                Else
                    MsgBox "Nothing found"
                End If
        End With
    
        Set targetWs = Worksheets("Sheet1")
        With targetWs.Range("A:A")
                Set targetRng = .Find(What:=findString, _
                                After:=.Cells(.Cells.Count), _
                                LookIn:=xlValues, _
                                LookAt:=xlWhole, _
                                SearchOrder:=xlByRows, _
                                SearchDirection:=xlNext, _
                                MatchCase:=False)
                If Not targetRng Is Nothing Then
                     Debug.Print targetRng.Offset(, 1)
                     targetRng.Offset(, 1) = priceValue
                Else
                    MsgBox "Nothing found"
                End If
        End With
    End Sub
    

    then you could get this result:

    For more information, click here to refer about Range.Find Method (Excel)

    Tuesday, January 5, 2016 8:10 AM