MACRO for importing TEXT file from location

[Jaydeep_sanghavi]

i want a macro which will import the TEXT file from specific(fixed) location irrespective of file name means whatever would be the file name it should import the file from that location


Thanks in Advance

 

[Comfy]

Will you be importing more than one file?

Have you tried just using Excel to browse to the file and open it? This will start the import wizard.

 

[Jaydeep_sanghavi]

Will you be importing more than one file?

Have you tried just using Excel to browse to the file and open it? This will start the import wizard.

no i will import only one file at a time.

to answer your second question yes i did so many times but want to make it automated using macro

 

[Comfy]

Try this:

Sub FileImport()
    Dim Fldr As String, FldrArr() As Variant
    Fldr = "Your File Location here"
    FldrArr() = FileCount(Fldr)
    If FldrArr(0) = 1 Then
        With ActiveSheet.QueryTables.Add(Connection:= _
            "TEXT;" & Fldr & FldrArr(1) & "", Destination:=Range("$A$5"))
            .Name = FldrArr(1)
            .Refresh BackgroundQuery:=False
        End With
    Else
        MsgBox FldrArr(1)
    End If
End Sub
Private Function FileCount(Folder As String) As Variant
    Dim objFSO As Object
    Dim objFile As Object
    Dim objFolder As Object
    Dim Name As String
    
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    
    'Get the folder object associated with the directory
    Set objFolder = objFSO.GetFolder(Folder)
    Select Case objFolder.Files.Count
        Case 0
            FileCount = (Array(objFolder.Files.Count, "No Files were Found in " & Folder))
        Case 1
            For Each objFile In objFolder.Files
            Name = objFile.Name
            Next
            FileCount = (Array(objFolder.Files.Count, Name))
        Case Else
            FileCount = (Array(objFolder.Files.Count, "There is more than one file in " & Folder))
    End Select
    'Clean up!
    Set objFolder = Nothing
    Set objFile = Nothing
    Set objFSO = Nothing
End Function

 

[Jaydeep_sanghavi]

it gives an error path not found

 

[Comfy]

Have you changed:

Fldr = "Your File Location here"

To the folder Directory you want to pull the file from?

EG.

Fldr = "C:/Import Folder/"

 

[Jaydeep_sanghavi]

can't execute code in break up mode

 

[Comfy]

Do you have the VBA explorer window open and is it still in debug mode?

 

[Jaydeep_sanghavi]

It worked thanks a lot thank you very much

just one more thing if i have more than one file in folder then is there any way by which it will ask me which file to import???

Thanx in advance

 

[Jaydeep_sanghavi]

i have chinese character file so i want text file to be imported in UTF -8 Character set