Efficient File management – Help

jalea148

New member
Joined
Jul 16, 2012
Messages
23
Reaction score
0
Points
0
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
 

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
O365
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.
 

jalea148

New member
Joined
Jul 16, 2012
Messages
23
Reaction score
0
Points
0
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.
View attachment SAMPLE.xlsm
 
Top