Help with VBA preventing duplicate selections

[y_ahmadi]

Hi all,

Im newbie in VBA and i have an issue about duplicate selection.

im using this code monthlyfile = Application.GetOpenFilename("Text Files (*.txt), *.txt") in my macro to select a specific file and fetch the data from it. Example: im selecting Jan 2017 file.

I need a code to prevent me from selecting the same file which is Jan 2017 because its already selected before when Im supposed to select Feb 2017.

Much appreciated

ATTN: IM NEWBIE, please try to keep the discussion as simple as possible

 

[JackDanIce]

Welcome to the board.

Can you post your whole code please? That alone isn't enough to suggest how to test if selected text file has already been opened.

 

[y_ahmadi]

Here you go

Sub Data_Import()
'
' Data_Import Macro
'


'
ChDir "C:\Users\pcadmin\Documents\VBA DUBAI\VBA"

monthlyfile = Application.GetOpenFilename("Text Files (*.txt), *.txt")

Workbooks.OpenText Filename:= _
monthlyfile, Origin:=437, _
StartRow:=4, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1), Array(11 _
, 1), Array(23, 1), Array(28, 1), Array(43, 1), Array(53, 1)), TrailingMinusNumbers:= _
True
' Sheets("Nov2007").Select (Crap statement)
ActiveSheet.Move Before:=Workbooks("Monthly Update.xlsm").Sheets(1)
Rows("2:2").Select
Selection.Delete Shift:=xlUp
Range("A1").Select
End Sub

 

[JackDanIce]

Untested, try:

Sub Data_Import_v1()

    Dim strFilesSoFar   As String
    Dim strMonthly      As String
    
    ChDir "C:\Users\pcadmin\Documents\VBA DUBAI\VBA"
    
    Application.ScreenUpdating = False
    
    Do Until InStr(strFilesSoFar, strMonthly) = 0
        strMonthly = Application.GetOpenFilename("Text Files (*.txt), *.txt")
        strFilesSoFar = strFilesSoFar & strMonthly & "|"
        
        If InStr(strFilesSoFar, strMonthly) > 0 Then
            MsgBox "Please select a different text file", vbExclamation, "Text File Already Opened"
        Else
            Workbooks.OpenText FileName:=strMonthly, origin:=437, StartRow:=4, DataType:=xlFixedWidth, fieldinfo:=Array(Array(0, 1), _
                Array(11, 1), Array(23, 1), Array(28, 1), Array(43, 1), Array(53, 1)), TrailingMinusNumbers:=True
            
            With ActiveSheet
                .Move before:=ThisWorkbook.Sheets(1)
                .Cells(1, 2).EntireRow.Delete
                Application.Goto Cells(1, 1), True
            End With
        End If
        
        If MsgBox("Select another file?", vbYesNoCancel, "Import Another Data File") <> vbYes Then Exit Do
    Loop
    
    Application.ScreenUpdating = True
    
End Sub

 

[y_ahmadi]

Appreciate your response, i have a debug message poping up.

Sorry i forget to mention it earlier, is it possible to pop up a box when selecting the same file again? i mean a box with a message (This file has been already imported)

 

[JackDanIce]

On what line does that debug message occur on? Click debug when the box appears and it should highlight a line in the VBE editor window.

 

[JackDanIce]

Made a few changes, try:

Sub Data_Import_v1()


    Dim strFilesSoFar   As String
    Dim strMonthly      As String
    
    ChDir "C:\Users\pcadmin\Documents\VBA DUBAI\VBA"
        
    Application.ScreenUpdating = False
    
    Do
        strMonthly = Application.GetOpenFilename("Text Files (*.txt), *.txt")
        strFilesSoFar = strFilesSoFar & strMonthly & "|"
        
        If InStr(strFilesSoFar, strMonthly) > 0 Then
            MsgBox "Please select a different text file", vbExclamation, "Text File Already Opened"
        Else
            Workbooks.OpenText FileName:=strMonthly, origin:=437, StartRow:=4, DataType:=xlFixedWidth, fieldinfo:=Array(Array(0, 1), _
                Array(11, 1), Array(23, 1), Array(28, 1), Array(43, 1), Array(53, 1)), TrailingMinusNumbers:=True
            
            ActiveSheet.Move before:=ThisWorkbook.Sheets(1)
            Cells(1, 2).EntireRow.Delete
            If Intersect(ActiveCell, Cells(1, 1)) Is Nothing Then Application.Goto Cells(1, 1), True
        End If
        
    Loop Until MsgBox("Select another file?", vbYesNoCancel, "Import Another Data File") <> vbYes
    
    Application.ScreenUpdating = True
    
End Sub

 

[y_ahmadi]

On what line does that debug message occur on? Click debug when the box appears and it should highlight a line in the VBE editor window.

on this line

Else
Workbooks.OpenText FileName:=strMonthly, origin:=437, StartRow:=4, DataType:=xlFixedWidth, fieldinfo:=Array(Array(0, 1), _
Array(11, 1), Array(23, 1), Array(28, 1), Array(43, 1), Array(53, 1)), TrailingMinusNumbers:=True

 

[JackDanIce]

Using a ChDir path on my PC and a random text file, I do not get an error message on that line I'm afraid. What does the error message say?