Error attempting to import data

[Jeff Johnson]

Hi,

I am trying to automatically import all text files (csv type) from a given folder. My code to pick the folder and loop through the files is working but the code (which was recorded during Get External Data) that is supposed to drop the data in my workbook is throwing an error:

Run-time error 1004
Excel cannot find the text file to refresh this external data range
Check to make sure the text file has not been removed or renamed, then try the refresh again.

Here's a subset of my code:
'Name new sheet
Sheets(ActiveSheet.Name).Name = dest_sheet_name
' Open .s2p files in selected folder and import them all
With Sheets(dest_sheet_name).QueryTables.Add(Connection:= _
"TEXT;" + file_name, Destination:=Sheets(dest_sheet_name).Range("$a$2"))
.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 = True
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = True
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With

The .Refresh BackgroundQuery isn't working.

Thanks for any help!

 

[JLGWhiz]

Where is the part of the code that shows what 'file_Name' is?

 

[Jeff Johnson]

JLGWhiz,

After playing with it, it works if I hard code the filename. Here's my working code:

Sub import1File()
'
' import1File Macro
'
Dim source_folder As String
Dim file_name As String
Dim workbk As Workbook
Dim dest_sheet_name As String
Dim cntr As Integer
Application.ScreenUpdating = False
With Application.FileDialog(msoFileDialogFolderPicker)
.Title = "Select Source Folder"
.Show
.AllowMultiSelect = False
If .SelectedItems.Count = 0 Then
MsgBox "Oops, you need to select a folder!"
Exit Sub
End If
source_folder = .SelectedItems(1) & "\"
End With
cntr = 0
'file_name = Dir(source_folder) 'Gets first file in folder
file_name = "C:\test data\0956_sample_5 150519.s2p"
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;" + file_name, Destination:=Range("$A$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 = True
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = True
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End Sub

Commenting out the hard coded file_name and uncommenting the other causes the error.
The folder picker seems to work fine. If I comment out the .Refresh line at the end, it will add new sheets with the filename (minus extension) but won't import the data.

Thanks a ton for looking at it....

 

[JLGWhiz]

I don't dink around with these SQL procedures, mostly because I have no real need to, but the error message indicated that there was a problem with the source file name. You could probably do a little research and figure out why the Dir function was not returning your file name.
Regards, JLG

 

[John_w]

Dir returns the matching file name without the folder path, therefore change the Connection argument to:

Connection:="TEXT;" & source_folder & file_name

IIRC in your previous thread you said you wanted each file to be imported into a new sheet, therefore insert the following line before the With ActiveSheet line:

Worksheets.Add

PS - please use CODE tags to preserve indentation and make the code easier to read: select the code then click the # symbol.

 

[JLGWhiz]

Dir returns the matching file name without the folder path, therefore change the Connection argument to:

Connection:="TEXT;" & source_folder & file_name

IIRC in your previous thread you said you wanted each file to be imported into a new sheet, therefore insert the following line before the With ActiveSheet line:

Worksheets.Add

PS - please use CODE tags to preserve indentation and make the code easier to read: select the code then click the # symbol.

@John_w, I learned something from this. Glad you jumped in here.
Regards, JLG

 

[Jeff Johnson]

That did the trick! Thank you so much. Hope you're having a nice Memorial weekend.

 

[Jeff Johnson]

Dir returns the matching file name without the folder path, therefore change the Connection argument to:

Connection:="TEXT;" & source_folder & file_name

IIRC in your previous thread you said you wanted each file to be imported into a new sheet, therefore insert the following line before the With ActiveSheet line:

Worksheets.Add

PS - please use CODE tags to preserve indentation and make the code easier to read: select the code then click the # symbol.

Thanks again for your help. Here's the resulting working code:

Sub Import_s2P()

Dim source_folder As String
Dim file_name As String
Dim workbk As Workbook
Dim dest_sheet_name As String
Dim cntr As Integer

Application.ScreenUpdating = False

With Application.FileDialog(msoFileDialogFolderPicker)
.Title = "Select Source Folder"
.Show
.AllowMultiSelect = False
If .SelectedItems.Count = 0 Then
    MsgBox "You must select a folder!"
    Exit Sub
End If
source_folder = .SelectedItems(1) & "\"
End With
cntr = 0
file_name = Dir(source_folder) 'Gets first file in folder
Do While file_name <> ""
' Create destination sheet name from file name by removing extension

If LCase(Right(file_name, 4)) = ".s2p" Then
' Import only for .s2p files
  cntr = cntr + 1
    dest_sheet_name = Left(file_name, InStrRev(file_name, ".") - 1)
    Sheets.Add after:=Sheets(Sheets.Count)
    'Check for existing sheets
    For i = 1 To Worksheets.Count
        If LCase(Sheets(i).Name) = LCase(dest_sheet_name) Then
         MsgBox "Sheet Name Already Exists!"
         Exit Sub
        End If
    Next i
    'Name new sheet
    Sheets(ActiveSheet.Name).Name = dest_sheet_name

    ' Open .s2p files in selected folder and import them all
    'Worksheets.Add
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;" & source_folder & file_name, Destination:=Range("$A$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 = True
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = True
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
        
    Dim wb_connection As WorkbookConnection
    For Each wb_connection In ActiveWorkbook.Connections
      If InStr(file_name, wb_connection.Name) > 0 Then
      wb_connection.Delete
      End If
    Next wb_connection
 End If
    file_name = Dir
Loop

MsgBox "Imported " & CStr(cntr) & " .s2p Files"
End Sub

Sub DeleteWS()
For Each ws In Sheets
Application.DisplayAlerts = False
If LCase(ws.Name) <> LCase("Master") Then ws.Delete
Next
Application.DisplayAlerts = True
End Sub

The other sub deletes all but the master sheet so you can load fresh data. I put a couple buttons on the master sheet. Click, click, done for as many files as you want. I used to open every one manually, cut, paste, ughh.

You guys are very cool for helping :^)