Excel Macro referencing a variable file name to import

**jouell** · 10-30-2008, 08:53 PM

Hi All

I have this Macro for Excel which imports 1 file C:\datafiles\user1-data-1.txt into cell E52:

Sub Macro2()
'
' Macro2 Macro
' Macro recorded 10/29/2008 by jouell
'

'
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;C:\datafiles\user1-data-1.txt", Destination:=Range("E52"))
        .Name = "user1-data-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 = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
    Application.Goto Reference:="Macro2"
End Sub

I'd like to be able to set the file name to import based on the name of excel file that I'm importing data to.

I.E

This piece:

   With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;C:\datafiles\user1-data-1.txt", Destination:=Range("E52"))

Would become:

   With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;C:\datafiles\name_of_excel_file-data-1.txt", Destination:=Range("E52"))

I.E if the Main Excel file name is CoolDoc.xls, the macro imports CoolDoc-data1.txt.

Thanks!
-jouell

**Kenneth Hobson** · 10-30-2008, 09:29 PM

Sub Import()
  ImportFile "E52"
End Sub


Sub ImportFile(sRange As String)
  Dim sName As String, sFile As String
  sName = Left(ActiveWorkbook.Name, InStrRev(ActiveWorkbook.Name, ".") - 1) & _
    "-data-1"
  sFile = "c:\datafiles\" & sName & ".txt"
  If Dir(sFile) = "" Then Exit Sub
  With ActiveSheet.QueryTables.Add(Connection:= _
  "TEXT;" & sFile, Destination:=Range(sRange))
  .Name = "user1-data-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 = False
  .TextFileTabDelimiter = True
  .TextFileSemicolonDelimiter = False
  .TextFileCommaDelimiter = False
  .TextFileSpaceDelimiter = False
  .TextFileColumnDataTypes = Array(1)
  .TextFileTrailingMinusNumbers = True
  .Refresh BackgroundQuery:=False
  End With
End Sub

**jouell** · 10-30-2008, 11:11 PM

Kenneth

Thank you so much for the quick reply.

That is AWESOME and this is solved!

Best
-jouell

**johnruggieri** · 12-08-2008, 07:10 PM

I have a similar issue I have been trying to solve. We have a filemaker system that generates an Excel file that contains the job number, Company name and job description. I want to be able to take that file name and put it into cells A1, B1, B3.
Example Filename: "7658.CompanyX.NewJob.xls'

Cell A1 = "7658"
Cell B1 = "CompanyX"
Cell C1 = "NewJob"

Can anyone help with this? TIA!

**Kenneth Hobson** · 12-08-2008, 08:07 PM

Welcome to the forum!

It is best to ask new questions in a new thread. I don't see how your question relates to this thread. Split() is the answer to your question. Post a new thread if you need help with it.

**RAparente** · 12-09-2008, 06:14 AM

Originally Posted by Kenneth Hobson

Sub Import()
  ImportFile "E52"
End Sub


Sub ImportFile(sRange As String)
...
  .TextFileTabDelimiter = True
  .TextFileSemicolonDelimiter = False
  .TextFileCommaDelimiter = False
  .TextFileSpaceDelimiter = False
...
  End With
End Sub

What if my delimiter = '|"

**Kenneth Hobson** · 12-09-2008, 09:41 AM

.TextFileOtherDelimiter = "|"
.TextFileTabDelimiter = False

The best way to learn is to record a macro. This is what the OP did. Select the inserted txt file's data in column A, Data > Text to Column, and set Other to "|".