VBA Excel: How to import CSV data into a range when the data array may differ

Question

My macro imports CSV data saved from a DAQ app into range A2:AC12. The data "typically" includes 16 sets seen in the figure below from D2:D17. The set goes from 0,1,2,etc, and to the max of 15. (FYI, I use A1 for fixed header info & E2:F17 is to translate the 0-15 set to 1-16 for graphing purposes).

However, the reason I say "typically" is because its possible that the DAQ app user can choose a different set of runs or skip some runs! Here is an example of when they performed the run and skipped sets 0-4, and choose 4-15 only. If I imported that data into my macro as currently is, this is what it would look like.

You can see here the problem where set 5 is at D2 (where set 0 should be). Additionally, a calculation array (N21:AC35) notices the empty spaces and results in DIV/0 error. Due to my limited knowledge of importing data, I use the most basic code for a simple import of the CSV into A2:

 'Imports CSV Data
With ActiveSheet.QueryTables.Add(Connection:= _
    "TEXT;" & myDirString, Destination:=Range("$A$2"))
    .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 = 1
    .TextFileParseType = xlDelimited
    .TextFileTextQualifier = xlTextQualifierDoubleQuote
    .TextFileConsecutiveDelimiter = False
    .TextFileTabDelimiter = False
    .TextFileSemicolonDelimiter = False
    .TextFileCommaDelimiter = True
    .TextFileSpaceDelimiter = False
    .TextFileColumnDataTypes = Array(1, 1, 1)
    .TextFileTrailingMinusNumbers = True
    .Refresh BackgroundQuery:=False
End With

So the main question is how can I import the data, (whether it is a 0-15 set, a 5-15 set, or a 2-4-6-8-etc set), and still have it organized appropriately within that 0-15 rows? Should I rely on a FOR loop or IF statements somewhere? I'm perplexed about how to handle wide ranges of data like this and only used to fixed data runs.

Ideally, I would expect it to look like this. I manually manipulated the data as an example and okay with just putting in "n/a" for the empty non-run spaces:

mmurrietta · Accepted Answer · 2017-07-09 00:30:20Z

1

reading your post it and looking at the screenshots it looks like you always expect something in the range A1:A17. if that's so then you can run this after importing the data in your import macro:

For i = 2 To 17
    If ActiveSheet.Range("d" & i) <> i - 2 Then
        ActiveSheet.Range(i & ":" & i).Insert shift:=xlShiftDown
        With ActiveSheet.Range("a" & i & ":ac" & i)
            .Formula = "=na()"
            .Value = .Value
        End With
        ActiveSheet.Range("d" & i).Value = i - 2
    End If
Next i
'assuming your button is named 'Button 1'
'if you knew it was always the first shape made on the sheet then
'you could also access the button by index: Shapes(1)
with ActiveSheet.Shapes("Button 1")
    .Left = ActiveSheet.Range("b18").Left
    .Top = ActiveSheet.Range("b18").Top
End With

Edit

Added a with statement after the for loop to place the top left of your button at cell b18

edited Jul 9, 2017 at 0:30

answered Jul 7, 2017 at 21:55

mmurrietta

1913 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

joshjayse Over a year ago

This works wonderfully! But, I do notice that due to the ShiftDown command, that RESET button you see in the picture will also move downwards. So as I load and reset for new data the button moves as well. Any suggestion to keep that button fixed (without having to add code to keep "moving" it, or will just be stay due to the ShiftDown?

joshjayse Over a year ago

Thank you also for the Button fix code, works great too!

Collectives™ on Stack Overflow

VBA Excel: How to import CSV data into a range when the data array may differ

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related