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.
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