Application.GetOpenFilename()

[mbrown1]

I am a huge rookie when it comes to VB, I have written a small macro to open a report in excel, set the column widths, and extract data to a spread sheet. The main issue I have is that I have to manually open each file as the macro runs, which is tedious and time consuming. Is there a way to possible save the file name as a variable to re-open the exact file, and I would like to be able to open multiple files at the start of the macro... Code:

Dim COUNT As Integer
 'ASK THE USER TO INPUT THE NUMBER OF FILES TO ANALYZE
 Dim I As Integer
 I = InputBox("HOW MANY FILES DO YOU WANT?")

 'Using counter to create a for loop
 For COUNT = 1 To I
    'opens the text file from directory & format the columns
    Workbooks.OpenText Filename:=Application.GetOpenFilename(), Origin:=437, StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array( _
        Array(0, 1), Array(15, 1), Array(24, 1), Array(37, 1), Array(50, 1), Array(61, 1), Array(71 _
        , 1), Array(82, 1)), TrailingMinusNumbers:=True



    'copies the data from the recently opened window then closes that window
    ActiveSheet.Activate
    Range("A1:G30").Select
    Selection.Copy
    ActiveWindow.Close



    'paste the data onto the formula workbook sheet
    Windows("BR725 DATA ANALYSIS_ CAPABILITY BOOK_CMM1.xls").Activate
    ActiveSheet.Paste
    'move the sn number two columns over
    ActiveCell.Offset(13, 1).Range("a1").Select
    Selection.Cut Destination:=ActiveCell.Offset(0, 2).Range("A1")

    'move the cursor 2 COLUMNS over & 6 ROWs down from the original position(ROW,COLUMN)& copy the data values
    ActiveCell.Offset(0, 2).Range("A1,A11:A15").Select
        Selection.Copy


        'activate calculation sheet
        Sheets("TIP CUTOFF AF TWIST CMM1").Select
        'move the cursor 1 COLUMN over from the original position(ROW,COLUMN)
        ActiveCell.Offset(0, 1).Range("A1").Select
        'paste data onto calculation sheet
        Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        True, Transpose:=False



        'go back and activate data archive sheet
        Sheets("Sheet1").Select
        'move the cursor 2 COLUMN over from the original position(ROW,COLUMN)and up 13 rows
        ActiveCell.Offset(-13, 4).Range("A1").Select

        Next COUNT

        'after collecting data, format the colums for easy reading
        Sheets("TIP CUTOFF AF TWIST CMM1").Select
        ActiveCell.Cells.EntireColumn.AutoFit

End Sub

Private Sub CommandButton2_Click()
' Macro recorded 6/18/2012 by mike
'
'Dim COUNT As Integer
 'ASK THE USER TO INPUT THE NUMBER OF FILES TO ANALYZE
 Dim I As Integer
 I = InputBox("HOW MANY FILES DO YOU WANT?")

 For COUNT = 1 To I
    'opens the text file from directory & format the columns
    Workbooks.OpenText Filename:=Application.GetOpenFilename(), Origin:=437, StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array( _
        Array(0, 1), Array(14, 1), Array(38, 1), Array(49, 1), Array(60, 1), Array(71, 1)), _
        TrailingMinusNumbers:=True



    'copies the data from the recently opened window then closes that window
    ActiveSheet.Activate
    Range("A1:F30").Select
    Selection.Copy
    ActiveWindow.Close



    'paste the data onto the formula workbook sheet
    Windows("BR725 DATA ANALYSIS_ CAPABILITY BOOK_CMM1.XLS").Activate
    ActiveSheet.Paste

[AndreRet]

How would you know which files to open? In other words, are they saved in a specific folder,
When the user inputs say 3 files, how will you know which ones to open with what name?

[mbrown1]

I currently have it set up to ask the user to open a file for each iteration, I was thinking I could put the target files in a specified folder, which is what I have been doing and simply choosing each file as I go. I feel like there is a way to specify a group of files to open instead...Thanks for the help btw