Importing .rpt Files in VBA based on a condition

[muirfield59]

Hi all,

I am importing .rpt files into excel using a vba code that I've attached at the bottom. Currently, I select a folder to import, and it imports all the files within that folder. I need to be able to check if I have imported a file before, and not import it again. The file names are a bunch of dates (04032017, 04042017, etc.). Any suggestions?

Dim xStrPath As String
    Dim xFileDialog As FileDialog
    Dim xFile As String
    Dim xCount As Long
    
    Set xFileDialog = Application.FileDialog(msoFileDialogFolderPicker)
    xFileDialog.AllowMultiSelect = False
    xFileDialog.Title = "Select a folder"
    If xFileDialog.Show = -1 Then
        xStrPath = xFileDialog.SelectedItems(1)
    End If
    If xStrPath = "" Then Exit Sub
    Application.ScreenUpdating = False
    xFile = Dir(xStrPath & "\*.rpt")
    Do While xFile <> ""
        xCount = xCount + 1
        Sheets(xCount).Select
        With ActiveSheet.QueryTables.Add(Connection:="TEXT;" _
          & xStrPath & "" & xFile, Destination:=Range("A1"))
            .Name = "a" & xCount
            .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 = xlFixedWidth
            .TextFileTextQualifier = xlTextQualifierDoubleQuote
            .TextFileConsecutiveDelimiter = False
            .TextFileTabDelimiter = True
            .TextFileSemicolonDelimiter = False
            .TextFileCommaDelimiter = False
            .TextFileSpaceDelimiter = False
            .TextFileColumnDataTypes = Array(1, 2, 2, 2, 2, 2, 2)
            .TextFileFixedColumnWidths = Array(3, 12, 4, 14, 12, 81)
            .TextFileTrailingMinusNumbers = False
            .Refresh BackgroundQuery:=False
            xFile = Dir
        End With
    Loop

 

[Joe4]

Welcome to the Board!

I need to be able to check if I have imported a file before, and not import it again.

And how exactly can you determine if you have imported it before?
What is the logic for making that determination?
Do you keep a log of the files you have imported somewhere?

 

[muirfield59]

Welcome to the Board!


And how exactly can you determine if you have imported it before?
What is the logic for making that determination?
Do you keep a log of the files you have imported somewhere?

Hi Joe,

Currently the files that are being imported are reports from specific dates. Right now, on my home tab in Excel I have a range with all the file dates (04032017, 04102017,04172017 etc.). My question is the following - Is there a way to build an if statement within the file import section in my macro? For example, if the file name is equal to any of the dates in the range on the home tab, then go to the next file, if not, import the new file. Thanks!

 

[Joe4]

Yes. You can use native Excel functions like "CountIf" in VBA by prefacing it with the phrase Application.WorksheetFunction, i.e. "Application.WorksheetFunction.CountIf(...)"
So, you can use that to count how many times a particular value show up in a range. If it is greater than 0, than it exists, otherwise it does not.