Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Westi on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Need Macro Excel Help 1

Status
Not open for further replies.

jonsi

IS-IT--Management
Dec 20, 2001
1,989
DE
Originally posted on the MS Office forum

It is some while since I used a Macro in Excel but I have a lot of Data gathering from old .txt files that I need to do.

I have lots of data from a logger output into a .txt file. I need to fetch all of the data from columns A:I and dump it into another file which contains formulas.

the following Macro is recorded in the destination workbook
Sub fetch()
'
' fetch Macro
'
' Keyboard Shortcut: Ctrl+q
'
Workbooks.OpenText Filename:= _
"G:\folder\subfolder\txtfile.ext", Origin:=932, StartRow _
:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=True, _
Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1), Array( _
3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), Array(10 _
, 1), Array(11, 1)), TrailingMinusNumbers:=True
Columns("A:I").Select
Selection.Copy
Windows("destination.xlsm").Activate
Range("A1").Select
ActiveSheet.Paste
Selection.ColumnWidth = 8.29
Columns("A:A").EntireColumn.AutoFit
End Sub

I open the destination workbook first, then run the macro, but need it to 'fetch' data from a different txtfile everytime (the red bit in the macro). The destination workbook is then Save As NEWFILENAME.xlsx, leaving the original untouched.

How do I get the macro to wait for me to select the file? (the txtfile to be selected would always be in the same folder\subfolder)





hwyl
Jonsi B-)
"If an apple a day keeps the Doctor away ...why don't Daleks live in Orchards?"
 
** update **

Skip suggested that I check out GetOpenFileName().

that's fine advice I'm sure but for someone who's been away from this for a while I haven't a clue what to write into the above or where to put it.

Jonsi [shadeshappy]

hwyl
Jonsi B-)
"If an apple a day keeps the Doctor away ...why don't Daleks live in Orchards?"
 


Code:
'
    Dim sFName
    
    sFName = Application.GetOpenFilename("Text Files (*.txt), *.txt")
    If sFName <> False Then
        Workbooks.OpenText _
            Filename:=sFName, _
            Origin:=932, StartRow _
        :=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
        ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=True, _
        Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1), Array( _
        3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), Array(10 _
        , 1), Array(11, 1)), TrailingMinusNumbers:=True
        
        Columns("A:I").Select
        Selection.Copy
        Windows("destination.xlsm").Activate
        Range("A1").Select
        ActiveSheet.Paste
        Selection.ColumnWidth = 8.29
        Columns("A:A").EntireColumn.AutoFit
    End If
 
Thanks Skip ...works like a charm


hwyl
Jonsi B-)
"If an apple a day keeps the Doctor away ...why don't Daleks live in Orchards?"
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top