Call File Browser ?

[monkeybumhead]

Hi everyone, this is my first post and I have to say - what a great site ! I think I'll be on here regularly soon !

I have a new Excel project I'm working on and already I need some help

My aim is to write Macro's that will automatically import several text files before completing pivot tables on the contents of each one and then analysing the results in a final correlation analysis and Chart.

I can do all of this manually and I'm confident in my worksheet function abilities - writing macros however is a different matter !

The first problem I have is importing several text files in seperately as seperate sheets. I recorded a macro that will do this for the first one but I want the macro to stop at the point of selecting the new file so the user can manually choose the next file. I guess there is a File Browser function I can call ?

My current code for the first sheet reads :

Sub Load_in_Data()
'
' Load_in_Data Macro
' Macro recorded 03/06/2005 by Sweeney
'

'
ActiveWorkbook.Worksheets.Add
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;E:\Operation Freedom\early bird EMD 30.txt", Destination:=Range("A1"))
.Name = "early bird EMD 30"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = xlWindows
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = True
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = True
.TextFileColumnDataTypes = Array(1, 1, 1)
.Refresh BackgroundQuery:=False
End With
End Sub


Thats a long first post - sorry folks! Help would be dearly appreciated.

[Kaak]

Mybe this is of use to you

FileSearch Sample:

Function ImportFiles()

Dim i As Integer

SPath = SelectFolder("Select the folder with the files.")

With Application.FileSearch

.NewSearch
.LookIn = SPath
.FileName = "*.*"
.SearchSubFolders = False
.Execute

For i = 1 To .FoundFiles.Count

Call ProcessFile(.FoundFiles(i))

Next

End With

End Function

Function ProcessFile(FileName)

Sheets.Add After:=Sheets(Sheets.Count)

ActiveSheet.Name = FileName

Cells.NumberFormat = "@"
Cells.Font.Name = "Courier New"

Range("A1").Select

j = 0
r = 0

Open FileName For Input As #1

Do Until EOF(1)

Line Input #1, LineOfText

For i = 1 To Len(LineOfText)

TempString = TempString & Mid(LineOfText, i, 1)

If Mid(LineOfText, i, 1) = ";" Then

Range("A1").Offset(r, j).Value = TempString

TempString = ""

j = j + 1

End If

Next

Range("A1").Offset(r, j).Value = TempString

TempString = ""

j = 0

r = r + 1

Loop

Close #1

Cells.EntireColumn.AutoFit

End Function

[Jake Marx]

Hi,

Take a look at the GetOpenFilename method in the VB help files (F1 from
within a code pane the VBE). That will allow you to show your users the
standard file open dialog, and it will return a string containing the path
of the file selected (or False if nothing is selected). So declare the
return value's variable as Variant, and your code may look something like
this:

Sub demo()
Dim vFile As Variant

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

If vFile <> False Then
MsgBox "User selected '" & vFile & "'."
End If
End Sub

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]


monkeybumhead wrote:
> Hi everyone, this is my first post and I have to say - what a great
> site ! I think I'll be on here regularly soon !
>
> I have a new Excel project I'm working on and already I need some help
>> eek:
>
> My aim is to write Macro's that will automatically import several text
> files before completing pivot tables on the contents of each one and
> then analysing the results in a final correlation analysis and Chart.
>
> I can do all of this manually and I'm confident in my worksheet
> function abilities - writing macros however is a different matter !
>
> The first problem I have is importing several text files in seperately
> as seperate sheets. I recorded a macro that will do this for the first
> one but I want the macro to stop at the point of selecting the new
> file so the user can manually choose the next file. I guess there is
> a File Browser function I can call ?
>
> My current code for the first sheet reads :
>
> Sub Load_in_Data()
> '
> ' Load_in_Data Macro
> ' Macro recorded 03/06/2005 by Sweeney
> '
>
> '
> ActiveWorkbook.Worksheets.Add
> With ActiveSheet.QueryTables.Add(Connection:= _
> "TEXT;E:\Operation Freedom\early bird EMD 30.txt",
> Destination:=Range("A1"))
> Name = "early bird EMD 30"
> FieldNames = True
> RowNumbers = False
> FillAdjacentFormulas = False
> PreserveFormatting = True
> RefreshOnFileOpen = False
> RefreshStyle = xlInsertDeleteCells
> SavePassword = False
> SaveData = True
> AdjustColumnWidth = True
> RefreshPeriod = 0
> TextFilePromptOnRefresh = False
> TextFilePlatform = xlWindows
> TextFileStartRow = 1
> TextFileParseType = xlDelimited
> TextFileTextQualifier = xlTextQualifierDoubleQuote
> TextFileConsecutiveDelimiter = True
> TextFileTabDelimiter = True
> TextFileSemicolonDelimiter = False
> TextFileCommaDelimiter = True
> TextFileSpaceDelimiter = True
> TextFileColumnDataTypes = Array(1, 1, 1)
> Refresh BackgroundQuery:=False
> End With
> End Sub
>
>
> Thats a long first post - sorry folks! Help would be dearly
> appreciated.