vba to import data

[chouston]

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

 

[chouston]

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

 

[chouston]

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

It also does this when I change it to True

 

[chouston]

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