Split results of query array, apply different formulas to each then send to Worksheet?

Event2020

Board Regular
Joined
Jan 6, 2011
Messages
84
I have a macro that uses a query to open a tab delimited text file and import some (but not all) of the columns into a specific worksheet in the same workbook from where the macro was called. This works perfectly except I need to run the data from each column through a formula that cleans up the data and formats it according to my needs and then writes that to the work sheet.

In the code below you can see within the array statement the columns I am importing from the text file so is there any way to split the array, send each column to its own unique formula and then write the result to the worksheet specified in my code?

I have searched online but I can not find any reference to doing this.

VBA Code:
Sub importText()
    Dim FName
    Dim rDest As Range
    Dim Answer As VbMsgBoxResult
    Dim fDialog As FileDialog, result As Integer
    Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
    
'Optional: FileDialog properties
    fDialog.AllowMultiSelect = False
    fDialog.Title = "Select a file"
    fDialog.InitialFileName = "F:\"
    
'Optional: Add filters
    fDialog.Filters.Clear
    fDialog.Filters.Add "Text/CSV files (*.txt; *.csv)", "*.txt; *.csv", 1
    
    Answer = MsgBox("Are You Sure You Want To Import A Text File?", vbYesNo + vbCritical, "Import A Text File")
    If Answer = vbYes Then
        Application.ScreenUpdating = False
    Else
        Exit Sub
    End If
    
    FName = Application.GetOpenFilename(filefilter:="Text/CSV files (*.txt; *.csv),*.txt;*.csv", MultiSelect:=False)
    If FName = False Then Exit Sub
    
    Set rDest = Worksheets("Tracks").Cells(1, 3)
  
    With rDest.Worksheet.QueryTables.Add(Connection:= _
        "TEXT;" & FName, Destination:=rDest)
        .Name = "new 1_1"
        .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 = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 9, 9, 9, 1, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 1, _
        1, 1, 1, 9)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
End Sub
 

Some videos you may like

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
6,146
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Using XL2BB, Upload some same data and then upload a mocked up version of your solution. The solution may be easier to solve than trying to re-engineer non working code.
 

Event2020

Board Regular
Joined
Jan 6, 2011
Messages
84
Using XL2BB, Upload some same data and then upload a mocked up version of your solution. The solution may be easier to solve than trying to re-engineer non working code.
Your comment is unhelpful and I do not understand what you mean by non working code, my code above works perfectly and I am asking if it is possible to add formulas to each column that the array represents before that column is then inserted in the destination worksheet.

If I had a solution to mock up then I would have no need to seek suggestions in this forum?
 

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
6,146
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
When I ask you to mock up a solution, I don't expect you to write code. I want to see visually what you have as a before and after scenario. of your workbook. I would prefer to write my own code/solution rather than try to edit and "fix" and understand someone elses. If you do not wish to share a workbook before and after scenario so that we can understand your needs and expectations, then ok. I will move on to helping someone else and leave this for another. Good Luck with your project.
 

Event2020

Board Regular
Joined
Jan 6, 2011
Messages
84
Your are continuing to be unhelpful and presumptuous. You presumed that I knew what XL2BB was and how to use it (I did not) and you presume that I do not wish to share a workbook which I have not said. I think it is best that you "move on to help someone else" as I do not care for your attitude or presumptions when it was you who did not fully explain yourself. Good day.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,136
Messages
5,622,936
Members
415,941
Latest member
georgiana686

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top