Importing user entered file name 2

[jammic25]

I am trying to input a file name ie. using "inputbox method"
and then import it into an Excel spreadsheet. The file is a text file. The only way I can figure out how to accomplish this is to hard code the name of the file into my code. But, the file names are going to change so I need a method that will allow me to have a user input the name and then retrieve the file. Any help would be appreciated. Thanks, Jim

 

[rmikesmith]

Jim,

To let the user select a path/filename then pass it to your code, you can use something like the following:

Sub ImportFile()
Dim FName as String

  FName = Application.GetOpenFilename
  If FName = "False" Then Exit Sub

' Else use FName as the file to open/import
' ...
' Other processing here, as needed

End Sub

HTH

Mike

 

[jammic25]

Mike,
Thanks for the reply. Getting the file that way is a lot easier. But, I still don't understand how to get it to open in excel. The only method I know is the querytables.add etc. and that won't work with a variable for the file name. I'm sure there is an easy way I just don't know it. If you can provide some more help that would be appreciated.

Thanks

Jim

 

[rmikesmith]

Jim,

If these are text files, you can try the following:

1. Record a new macro
2. File|Open and select one of the text files. After you click OK the Text Import Wizard should start. Step through this and change the options, as appropriate.
3. Stop Recording
4. Use the generated macro code as a starting point. You will need to clean this up and incorporate it into your existing code.

Post back with how it works out or if you have other questions.


Regards,
Mike

 

[Brian934]

Jim,

I used this in an application I am doing right now for the same thing.

Sub GetImportFileName()
Dim Filt As String
Dim FilterIndex As Integer
Dim FileName As Variant
Dim Title As String

Range("A1&quot.Select

' Set up list of file filters
Filt = "Text Files (*.txt),*.txt," & _
"Lotus Files (*.prn),*.prn," & _
"Comma Separated Files (*.csv),*.csv," & _
"ASCII Files (*.asc),*.asc," & _
"All Files (*.*),*.*"

' Display *.* by default
FilterIndex = 5

' Set the dialog box caption
Title = "Select a File to Import"

' Get the file name
FileName = Application.GetOpenFilename _
(FileFilter:=Filt, _
FilterIndex:=FilterIndex, _
Title:=Title)

' Exit if dialog box canceled
If FileName = False Then
MsgBox "No file was selected."
Exit Sub
End If

' Import the file
Set Wksheetname = Sheets.Add

Wksheetname.Name = "InsertYourWorksheetName"
Set imprng = ActiveCell
Open FileName For Input As #1
R = 0
Do Until EOF(1)
Line Input #1, data
ActiveCell.Offset(R, 0) = data
R = R + 1
Loop
Close #1
end Sub

This imports everything into one column, then I use a text to columns feature like this:

Sub TextStretch()
'text to columns all info imported to one row

Columns("A:A&quot.Select
Selection.TextToColumns Destination:=Range("A1&quot, DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 2), Array(4, 2), Array(63, 2), Array(75, 2), Array(97, 2), _
Array(108, 2), Array(115, 2), Array(132, 1), Array(141, 1), Array(153, 1), Array(163, 1)) _

Cells.CurrentRegion.AutoFormat Format:=False
Columns("A:AA&quot.EntireColumn.AutoFit
End Sub

Your best bet to get this to space the columns correctly is to import the file into column A, then start your macro recorder and go to data/text to columns and follow the wizard. Then use those array numbers in your code.

 

[jammic25]

Thanks for all your help. Just as I got it to work, I was put in charge of another project. This one was put on hold. Your replies were greatly appreciated. Thanks