User prompt for data connection

[Guest]

I am testing a report that gathers data from two different files via data
connection. One is a text file and the other is a csv. Both populate data
in different worksheets. I have noticed that Excel (2007) does not have any
logic behind which one of these gets done first. This creates a huge problem
since the data is different depending on the file.

What I would like to do is to create a form (??) that would prompt the user
to select the "Data 1" file and then select the "Data 2" file. Once the
files are selected, then the data is refreshed correctly. Does anyone know
of a good way to accomplish this?

Thanks all!

 

[Guest]

Robby:
try using 'Application.GetOpenFilename' if I understood you right; it will
help you

 

[Guest]

How does the result from "GetOpenFilename" get passed back to my data
connections?

 

[NickHK]

Sounds like you need to change to synchronous update.
Assuming you are using a query table, set its .BackgroundRefresh property to
false.
Then you control which QT is updated and when.

NickHK

 

[Guest]

Here is what I have put together so far:

<code>
Private Sub CommandButton1_Click()
Dim fileToOpen As String
fileToOpen = Application _
.GetOpenFilename("Text Files (*.csv), *.csv")
MsgBox "Open " & fileToOpen
TextBox1.Text = fileToOpen

With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;fileToOpen" _
, Destination:=Range("$A$1"))
.Name = fileToOpen
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 1252
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(9, 9, 9, 1, 1, 1, 9, 9)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With

End Sub

</code>

The problem is that the "fileToOpen" value is not getting passed to the add
function of the query tables. I keep getting 1004 errors (Excel can't find
the file) and it creates a new connection named "fileToOpen".

Long story short... I can't figure out how to correctly pass the user input
(fileToOpen) to the function.

 

[Dave Peterson]

I don't do much with QueryTables, but this looks funny to me:

With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;fileToOpen" _
, Destination:=Range("$A$1"))

Maybe:

With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;" & fileToOpen _
, Destination:=Range("$A$1"))

And starting with that comma would drive me nuts <bg>:

With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;" & fileToOpen, _
Destination:=Range("$A$1"))

 

[Guest]

Dave,
Thanks much!! That worked like a champ. I don't know how I missed the
quotes!