Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: vba to import data

  1. #1
    Board Regular
    Join Date
    Apr 2002
    Location
    Arizona
    Posts
    68
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi all, I'm trying to automate importing data so I used the macro recorder to code the initial part. Then I added GetOpenFile method so a different file can be selected each time. However, I'm having trouble incorporating this file into the query.

    Here's the code:


    sub getrentroll()
    rentroll = Application.GetOpenFilename(, , "Select a File to Import", , False)


    With ActiveSheet.QueryTables.Add(Connection:=rentroll.Text, _
    Destination:=Range("A1"))
    .Name = "Hacienda"
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .TextFilePromptOnRefresh = False
    .TextFilePlatform = 1252
    .TextFileStartRow = 10
    .TextFileParseType = xlFixedWidth
    .TextFileTextQualifier = xlTextQualifierDoubleQuote
    .TextFileConsecutiveDelimiter = False
    .TextFileTabDelimiter = True
    .TextFileSemicolonDelimiter = False
    .TextFileCommaDelimiter = False
    .TextFileSpaceDelimiter = False
    .TextFileColumnDataTypes = Array(9, 1, 1, 1, 9, 1, 9, 3, 9)
    .TextFileFixedColumnWidths = Array(11, 3, 34, 6, 6, 5, 24, 13)
    .TextFileTrailingMinusNumbers = True
    .Refresh BackgroundQuery:=False
    End With
    End Sub

  2. #2
    Board Regular
    Join Date
    Apr 2002
    Location
    Arizona
    Posts
    68
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    oops, I think I figured out the filename problem, but when I try to run, I get:

    runtime error '1004'

    excel cannot find text file to refresh this external data range

  3. #3
    Board Regular
    Join Date
    Apr 2002
    Location
    Arizona
    Posts
    68
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    p.s. when I choose debug, it highlights the last line: ".Refresh BackgroundQuery:=False"

    It also does this when I change it to True

  4. #4
    Board Regular
    Join Date
    Apr 2002
    Location
    Arizona
    Posts
    68
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi! I'm still stuck with this problem...

    I'm trying to automate the import external database, I'd like my coworkers to be able to run this macro and then just pick the file. However when I try to run this, the getopenfilename works, but it won't import, debugging gives me the message I typed in the previous reply. Here's what I have so far:

    Sub getrentroll()

    rentroll = Application.GetOpenFilename(, , "Select a File to Import", , False)

    With ActiveSheet.QueryTables.Add(Connection:="TEXT;rentroll", _
    Destination:=Range("A1"))
    .Name = "Hacienda"
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .TextFilePromptOnRefresh = False
    .TextFilePlatform = 1252
    .TextFileStartRow = 10
    .TextFileParseType = xlFixedWidth
    .TextFileTextQualifier = xlTextQualifierDoubleQuote
    .TextFileConsecutiveDelimiter = False
    .TextFileTabDelimiter = True
    .TextFileSemicolonDelimiter = False
    .TextFileCommaDelimiter = False
    .TextFileSpaceDelimiter = False
    .TextFileColumnDataTypes = Array(9, 1, 1, 1, 9, 1, 9, 3, 9)
    .TextFileFixedColumnWidths = Array(11, 3, 34, 6, 6, 5, 24, 13)
    .TextFileTrailingMinusNumbers = True
    .Refresh BackgroundQuery:=False
    End With
    End Sub

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •