MS Excel VBA - Import Text File in Template Workbook - Invalid Procedure call or argument 1

[BxWill]

Timely insight needed!

Attempting to setup a MS Excel workbook template that contains several Excel tables.
The file is to be used weekly to review and analyze data for 8 departments. In essence, the various departments place text files on the network drive and I retrieve them and analyze using the template and then save the MS Excel file (without the complex formulae) for the departments' use.

The plan is to import the text file for a particular department into worksheet 1 and convert the active range to a table. Then, the tables on the other worksheets, Sheets2 through 10, that contain formulae involving the sumproduct function are dynamically populated. The analysis is performed and then, I import the text file for the other department and perform the analysis. And so on...until all of the departments are analyzed.

It appears that it is necessary to import the text file into a worksheet so that I can use the sumproduct function within the formulae.
Is this correct? Are there any viable alternatives? In other words, I don't believe that the sumproduct can be used within an Excel table to extract data from MS Access or a data model within PowerPivot. Example of sumproduct in use include "=SUMPRODUCT((tMSTR[ExpenseType]=$B8)*(tMSTR[Month]=C$7)*(tMSTR[Year]=$B$1)*(tMSTR[PaidAmt]))."

Currently, I have an error --> "Invalid Procedure call or argument."

How should this error, within the following code, be resolved? Also, any additional insight regarding the feasibility of the planned setup is appreciated. Would you perform this differently?

Sub mcrManuallyImportTextFile()
    Dim txtFileNameAndPath As String
    Dim ImportingFileName As String
    Dim fd As Office.FileDialog
    Dim SheetName As Worksheet
 
    Set fd = Application.FileDialog(msoFileDialogFilePicker)
 
    With fd
        
        .AllowMultiSelect = False
 
        
        .Title = "Please select the file."
 
        
        .Filters.Clear
        .Filters.Add "txt", "*.txt"
        .Filters.Add "All Files", "*.*"
 
        If .Show = True Then
          txtFileNameAndPath = .SelectedItems(1)
        Else
            MsgBox "Please start over.  You must select a file to import"
            
            Exit Sub
        End If
    End With
 
   With ActiveWorkbook.Connections("SampleTextFile4a")
        .Name = "SampleTextFile4a"
        .Description = ""
    End With
    Workbooks("Book1").Connections.AddFromFile _
        "C:\Users\Bill\Documents\SampleTextFile4a.txt", True, False


'*********************************************************************
'' 4/3/2016 at 4:40 pm Error at the line below  ----> Invalid procedure call or argument
'********************************************************************* 

    With ActiveSheet.ListObjects.Add(SourceType:=4, Source:=ActiveWorkbook. _
        Connections("SampleTextFile4a"), Destination:=Range("$A$1")).TableObject
        .RowNumbers = False
        .PreserveFormatting = True
        .RefreshStyle = 1
        .AdjustColumnWidth = True
        .ListObject.DisplayName = "Table_SampleTextFile4a"
        .Refresh
    End With
    ActiveWorkbook.Names.Add Name:="tMSTR", RefersToR1C1:= _
        "=Table_SampleTextFile4a[#All]"
End Sub

 

[SkipVought]

Hi,

Why would you be ADDing a table to your sheet programatically? That should be a ONE time EVENT!

If this is just a question of academic interest, then turn on your macro and record adding a Structured Table.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[BxWill]

Yes, it is a one time event for any particular week - performed every week for each department.

So, after any given week, upon importing the text file into the first worksheet of the template, it appears that it is
best to just delete the table on the first worksheet and manually import the next week's data and name the table "tMSTR" again to maintain the integrity of the formulae that is within each table on the other worksheets throughout the workbook. Note, for any given week, although the column headers may be fairly consistent, the data itself is not a continuation of the prior week's data. Hence, a refresh will not work.

Basically, I would like to maintain the formulae that is within the various tables on the multiple worksheets within the workbook but continue to reuse the first worksheet to import various text files (as long as they do not exceed 1,000,000 records).

Currently, the way that I set this up is that the first column of each MS Excel table will be cleared out and I will re-populate. All of the other columns within the MS Excel table have embedded formulae.

For example, if I have a column in my text file titled "Vendor", then I have a table on a worksheet that have the distinct vendors in the first column and formulae in all of the other columns.

 

[SkipVought]

A refresh will work, but there's a parameter to assign differently that will cause a file select dialog to appear. I'm not at my laptop to find this and early tomorrow I'm having cataract surgery, so I'll be out of pocket for a while. You might do some research to find that. I have never deleted a QueryTable just to add a replacement for another file/database.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[BxWill]

Thanks for the insight.

Did some research and it appears that thread707-1481022 is applicable...

Therefore, I have the following code;

 Sub mcrImportTextFile()
'
' mcrImportTextFile Macro
'
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;C:\Users\Bill\Documents\SampleTextFile4a.txt", Destination:=Range _
        ("$A$1"))
        .CommandType = 0
        .Name = "SampleTextFile4a"
        .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, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
    Range("A1").Select
    Selection.CurrentRegion.Select
    ActiveWorkbook.Names.Add Name:="tMSTR", RefersToR1C1:= _
        "=Sheet1!R1C1:R1236C7"
    Range("A2").Select
End Sub

Sub ImportAutoRead()
    Dim fopen As Variant
    fopen = Application.GetOpenFilename("Auto Read Files (*.txt), *.txt")
    
    If fopen <> False Then
'make sure that Data Range Properties NAME is SampleTextFile4a
        With Sheets("Sheet1").QueryTables("SampleTextFile4a")
            .Connection = "TEXT;" & fopen
            .Refresh BackgroundQuery:=False
        End With
    End If
End Sub

However, I believe that the query table should be cleared and/or deleted and replaced for another file. Particularly if there are less records imported into the query table relative to the preceeding week. For example, in week 1, if I manually import 1000 records and then in week 2, I refresh the query table with just 500 records, I believe that I will have 500 records still remaining from week 1.

Will continue the research...maybe exploring the deletion of a query table and replacing for another file.

 

[BxWill]

Did preliminarily test the Sub ImportAutoRead () and it appears that the records will be the records from the refreshed file. Specifically, the number of records within SampleTextFile4a was 1200 and the number of records within SampleTextFile4b was 1150. Upon running the procedure, the number of records was 1150.

Therefore, exploring the deletion of a query table and replacing for another file will cease

Just need to fine tune the first procedure "Sub mcrImportTextFile()" so that the imported data on worksheet1 will be displayed as a table (with the alternating colors for the rows) named "tMSTR" so that the formulae within the various worksheets will work and the workbook is updated...