locked
Microsoft Excel Help RRS feed

  • Question

  • I have written three seperate macros to perform a function on a certain type of file. I am trying to add a code at the beginning of the macro that would allow me to search for any file on the c:// drive on any computer that the excel spreadsheet is used on to run the macro on:

    Here are the macros VBA code that I have written

    Macro 1:

    Sub RunUpload()
    '
    ' RunUpload Macro
    '

    '
        With ActiveSheet.QueryTables.Add(Connection:= _
            "TEXT;\\HOUIC-NA-V504\josiah.morgan$\Cached\My Documents\Study RR47_2005\R1205001\R1205001rr.txt" _
            , Destination:=Range("$A$6"))
            .Name = "R1205001rr"
            .FieldNames = True
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .TextFilePromptOnRefresh = False
            .TextFilePlatform = 437
            .TextFileStartRow = 17
            .TextFileParseType = xlFixedWidth
            .TextFileTextQualifier = xlTextQualifierDoubleQuote
            .TextFileConsecutiveDelimiter = False
            .TextFileTabDelimiter = True
            .TextFileSemicolonDelimiter = False
            .TextFileCommaDelimiter = False
            .TextFileSpaceDelimiter = False
            .TextFileColumnDataTypes = Array(9, 1, 1)
            .TextFileFixedColumnWidths = Array(3, 27)
            .TextFileTrailingMinusNumbers = True
            .Refresh BackgroundQuery:=False
        End With
    End Sub

    Macro 2:

    Sub GasUpload()
    '
    ' GasUpload Macro
    '

    '
        With ActiveSheet.QueryTables.Add(Connection:= _
            "TEXT;\\HOUIC-NA-V504\josiah.morgan$\Cached\My Documents\Study RR47_2005\R1205001\R1205001.GAS" _
            , Destination:=Range("$C$6"))
            .Name = "R1205001"
            .FieldNames = True
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .TextFilePromptOnRefresh = False
            .TextFilePlatform = 437
            .TextFileStartRow = 1
            .TextFileParseType = xlFixedWidth
            .TextFileTextQualifier = xlTextQualifierDoubleQuote
            .TextFileConsecutiveDelimiter = False
            .TextFileTabDelimiter = True
            .TextFileSemicolonDelimiter = False
            .TextFileCommaDelimiter = False
            .TextFileSpaceDelimiter = False
            .TextFileColumnDataTypes = Array(9, 1, 1)
            .TextFileFixedColumnWidths = Array(9, 19)
            .TextFileTrailingMinusNumbers = True
            .Refresh BackgroundQuery:=False
        End With
    End Sub

    Macro 3:

    Sub LiqUpload()
    '
    ' LiqUpload Macro
    '

    '
        With ActiveSheet.QueryTables.Add(Connection:= _
            "TEXT;\\HOUIC-NA-V504\josiah.morgan$\Cached\My Documents\Study RR47_2005\R1205001\R1205001.LIQ" _
            , Destination:=Range("$E$6"))
            .Name = "R1205001_1"
            .FieldNames = True
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .TextFilePromptOnRefresh = False
            .TextFilePlatform = 437
            .TextFileStartRow = 1
            .TextFileParseType = xlFixedWidth
            .TextFileTextQualifier = xlTextQualifierDoubleQuote
            .TextFileConsecutiveDelimiter = False
            .TextFileTabDelimiter = True
            .TextFileSemicolonDelimiter = False
            .TextFileCommaDelimiter = False
            .TextFileSpaceDelimiter = False
            .TextFileColumnDataTypes = Array(9, 1, 1)
            .TextFileFixedColumnWidths = Array(9, 19)
            .TextFileTrailingMinusNumbers = True
            .Refresh BackgroundQuery:=False
        End With
    End Sub

    Monday, July 23, 2012 4:36 PM

All replies

  • Add this line at the top of each routine:

    Workbooks.Open Application.GetOpenFilename(, , "Pick your file...")

    You may also need to add this as the second line:

    Worksheets("Sheet name of the sheet with the Query").Activate


    HTH, Bernie

    Monday, July 23, 2012 5:01 PM
  • Thanks so much for your help, but heres the problem that I'm having when I add

    (Workbooks.Open Application.GetOpenFilename(, , "Pick your file...")...It basically imports the file I select and then runs the macro on the test file I used..How do I get it to where it allows me to select the file I want and perform the same operations as what I performed on the test file?

    For example, what I did was I recorded a macro where Imported a text file into excel and selected which rows and column I wanted to insert and where I wanted it to import into the worksheet..So what I am trying to do now is make it to where you can select the file then the same operations are performed on the file I selected as the original test file but without the test file loading as well

    Monday, July 23, 2012 5:36 PM
  • Try changing, for example, this:

    With ActiveSheet.QueryTables.Add(Connection:= _
            "TEXT;\\HOUIC-NA-V504\josiah.morgan$\Cached\My Documents\Study RR47_2005\R1205001\R1205001rr.txt" _
            , Destination:=Range("$A$6"))
            .Name = "R1205001rr"

    to this:

    myFN = Application.GetOpenFilename(, , "Pick the Text (*.txt) File")

    With ActiveSheet.QueryTables.Add(Connection:= _
            "TEXT;" & myFN _
            , Destination:=Range("$A$6"))
            .Name = Replace(Split(myFN, "\")(UBound(Split(myFN, "\"))), ".txt", "")


    HTH, Bernie

    Monday, July 23, 2012 6:00 PM
  • It works now thanks so much for all your help, however, I have one more question, I created a button and assigned each macro to a button is there a way I can make the buttons appears automatically on subsequent pages in the workbook?

    Monday, July 23, 2012 6:32 PM
  • Hi,

    Just select the buttons and paste them into the other sheets, the buttons will still work with the macro. But the buttons will do the exactly same thing as the first one.


    Jaynet Zhang

    TechNet Community Support

    Tuesday, July 24, 2012 7:58 AM
  • Hey I have one more question for instance for the three different macros is there a way that I can have the macros open up multiple files at once like a dialog box asking me which files to open?

    Wednesday, July 25, 2012 3:52 PM
  • Sure - but you need to somehow have select which sheet to process - this just adds one more sheet for each of the files ... the AddQT macro is not complete - but it should give you some ideas

    Sub ProcessLotsOfFiles()
        Dim VFile As Variant
        Dim i As Integer
        VFile = Application.GetOpenFilename(, , "Pick the Text (*.txt) File(s)", , True)
        If IsArray(VFile) Then
            For i = LBound(VFile) To UBound(VFile)
                Worksheets.Add
                AddQT VFile(i)
            Next i
        Else
            AddQT VFile
        End If
    End Sub

    Sub AddQT(myFN As Variant)

    With ActiveSheet.QueryTables.Add(Connection:= _
            "TEXT;" & myFN _
            , Destination:=Range("$A$6"))
            .Name = Replace(Split(myFN, "\")(UBound(Split(myFN, "\"))), ".txt", "")

    'Rest of your QueryTable code here 

    End Sub


    HTH, Bernie

    Wednesday, July 25, 2012 4:43 PM
  • This is my VBA code it is telling me that something is wrong is wrong at the line that I have BOLD and Italicizied it is saying "Method Range of Object Global Fail" can you tell me how to fix this

    Sub RunUpload()
    '
    ' RunUpload Macro
    '
    Dim colStart As String
    Dim letters(1 To 26) As String
    letters(1) = "A"
    letters(3) = "C"
    letters(5) = "E"
    letters(7) = "G"

    For i = 1 To 26 Step 2
        If IsEmpty(Cells(i, 6).Value) Then
            colStart = letters(i)
        End If
    Next i


    myFN = Application.GetOpenFilename(, , "Pick the Text (*.txt) File")

    With ActiveSheet.QueryTables.Add(Connection:= _
            "TEXT;" & myFN _
            , Destination:=Range("$" & colStart & "$6"))
            .Name = Replace(Split(myFN, "\")(UBound(Split(myFN, "\"))), ".txt", "")
            .FieldNames = True
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .TextFilePromptOnRefresh = False
            .TextFilePlatform = 437
            .TextFileStartRow = 17
            .TextFileParseType = xlFixedWidth
            .TextFileTextQualifier = xlTextQualifierDoubleQuote
            .TextFileConsecutiveDelimiter = False
            .TextFileTabDelimiter = True
            .TextFileSemicolonDelimiter = False
            .TextFileCommaDelimiter = False
            .TextFileSpaceDelimiter = False
            .TextFileColumnDataTypes = Array(9, 1, 1)
            .TextFileFixedColumnWidths = Array(3, 27)
            .TextFileTrailingMinusNumbers = True
            .Refresh BackgroundQuery:=False
        End With
    End Sub

    Friday, July 27, 2012 3:30 PM
  • basically what I'm trying to do is when the macro loads I want it to upload to the first available column on the sheet
    Friday, July 27, 2012 4:06 PM
  • this is what I need the file to do when I run the macro the first time i want the data to start at B6 the next time I run the macro I want the data to start a C6, then the next time run the macro I want the data to start at D6 and so on and so on.....

    How can I get my current code to do this because as of now everytime I run it the new data uploads to A6 and pushes the old data to the right here is the current code

    Sub RunUpload()
    '
    ' RunUpload Macro
    '
    Dim colStart As String
    Dim letters(1 To 26) As String
    letters(1) = "A"
    letters(3) = "C"
    letters(5) = "E"
    letters(7) = "G"

    For i = 1 To 26 Step 1
        If IsEmpty(Cells(i, 6)) Then
            colStart = letters(i)
             Exit For
             End If
    Next i


    myFN = Application.GetOpenFilename(, , "Pick the Text (*.txt) File")

    With ActiveSheet.QueryTables.Add(Connection:= _
            "TEXT;" & myFN _
            , Destination:=Range("$" & colStart & "$6"))
                    .FieldNames = True
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .TextFilePromptOnRefresh = False
            .TextFilePlatform = 437
            .TextFileStartRow = 17
            .TextFileParseType = xlFixedWidth
            .TextFileTextQualifier = xlTextQualifierDoubleQuote
            .TextFileConsecutiveDelimiter = False
            .TextFileTabDelimiter = True
            .TextFileSemicolonDelimiter = False
            .TextFileCommaDelimiter = False
            .TextFileSpaceDelimiter = False
            .TextFileColumnDataTypes = Array(9, 1, 1)
            .TextFileFixedColumnWidths = Array(3, 27)
            .TextFileTrailingMinusNumbers = True
            .Refresh BackgroundQuery:=False
        End With
    End Sub

    Friday, July 27, 2012 5:35 PM