M
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.
! 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.