refresh backgroundquery

[chouston]

Hi! I did several searches, but couldn't find and posts that could help me. Perhaps someone can figure this out. I'm trying to automate importing external data sources, and with the following code, I get the error: Run-time error '1004' excel cannot find the text file to refresh this external data range.

When I hit debug, it highlights the last line ".refresh background query = false

Sub getrentroll()
'
' getrentroll Macro
' Macro recorded 4/30/2002 by Comptrol Technologies
'

'
Dim rentroll
rentroll = CVar(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

Many thanks in advance for any help!

[Jack in the UK]

some olsd code i wrote some time back make what you can from it and yes they still run this today...



With ActiveSheet.QueryTables.Add(Connection:= _
"ODBC;DSN=Kopen3 Live;UID=emc;;SERVER=ufsvr20;DBNAME=KO3LIVE;LUID=emc;", _
Destination:=Range("A1"))
.Sql = Array( _
"SELECT ""Sales Ledger Customer File Co 01"".""Address 01"", ""Sales Ledger Customer File Co 01"".""Address 02"", ""Sales Ledger Customer File Co 01"".""Address 03"", ""Sales Ledger Customer File Co 01"".""Address 04" _
, _
"""" & Chr(13) & "" & Chr(10) & "FROM ""Sales Ledger Customer File Co 01"" ""Sales Ledger Customer File Co 01""" _
)
.FieldNames = True
.RefreshStyle = xlInsertDeleteCells
.RowNumbers = False
.FillAdjacentFormulas = False
.RefreshOnFileOpen = False
.HasAutoFormat = True
.BackgroundQuery = True
.TablesOnlyFromHTML = True
.Refresh BackgroundQuery:=False
.SavePassword = False
.SaveData = True
End With
End Sub

_________________
If you can help a guy in trouble -
If you can sort that nagging problem -
Pease try, at home, at work or on a message board.

Others help you!
So PLEASE help if you can - If only the once.

Thank you -

Rdgs
======

[ This Message was edited by: Jack in the UK on 2002-05-06 14:58 ]