Results 1 to 3 of 3

Thread: Efficient File management Help

  1. #1

    Efficient File management Help



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

    My workbook has a Main spreadsheet and 20 Other sheets. All are ordered by date. Each row of Main refers to the corresponding row and the previous row of the Others.

    Each of the Other sheets is a duplicate of a CSV file that is automatically updated daily. These files have 5 columns and about 1500 rows

    What is the most efficient way to keep the Other sheets up to date?

    Would this strategy to import data be more effectively done with INTEROP c#? [I'm not a C programmer, but there are vba ==> C# converters]?

    The code below will load 1 of the Other sheets. At the moment, I need to go to each of the Other sheets and run a similar subroutine. The sub name, file location, Sheet Name and TextFileStartRow are different for each. Clearly, this is not a feasible daily strategy.

    Sub GetcSV()
    '
    ' GetcSV Macro
    '
    ' Keyboard Shortcut: Ctrl+r


    With ActiveSheet.QueryTables.Add(Connection:="TEXT;C: \XYZ.csv", _
    Destination:=Range("$A$1"))
    .Name = "XYZ"
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .TextFilePromptOnRefresh = False
    .TextFilePlatform = 437
    .TextFileStartRow = 3510
    .TextFileParseType = xlDelimited
    .TextFileTextQualifier = xlTextQualifierDoubleQuote
    .TextFileConsecutiveDelimiter = False
    .TextFileTabDelimiter = True
    .TextFileSemicolonDelimiter = False
    .TextFileCommaDelimiter = True
    .TextFileSpaceDelimiter = False
    .TextFileColumnDataTypes = Array(3, 1)
    .TextFileTrailingMinusNumbers = True
    .Refresh BackgroundQuery:=False
    End With
    End Sub

  2. #2
    I am not sure what your question is, but I am sure that INTEROP C# (whatever you mean by that) will provide nothing that you cannot over VBA.

    Is your problem adding a new days sheet and adding a line to Master, or is it that the 20 sheets get updated daily?

    Any chance of workbook examples.

  3. #3
    Quote Originally Posted by Bob Phillips View Post
    I am not sure what your question is, but I am sure that INTEROP C# (whatever you mean by that) will provide nothing that you cannot over VBA.

    Is your problem adding a new days sheet and adding a line to Master, or is it that the 20 sheets get updated daily?

    Any chance of workbook examples.
    Here is an example. Instead of 20 files to import, there are 2. Sheets ABC and DEF need their data automatically updated daily from text files ABC.CSV and DEF.CSV {which are updated daily}. After their update the corresponding row of Main needs to be updated.
    SAMPLE.xlsm

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
  •