User prompt for data connection
 

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!

User prompt for data connection
 

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

"Robby" wrote:

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!

User prompt for data connection
 

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

"Avishai" wrote:

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

"Robby" wrote:

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!

User prompt for data connection
 

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

"Robby" wrote in message
...
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!

User prompt for data connection
 

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.

"NickHK" wrote:

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

"Robby" wrote in message
...
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!

User prompt for data connection
 

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"))

Robby wrote:

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.

"NickHK" wrote:

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

"Robby" wrote in message
...
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!




--

Dave Peterson

User prompt for data connection
 

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

"Dave Peterson" wrote:

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"))

Robby wrote:

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.

"NickHK" wrote:

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

"Robby" wrote in message
...
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!




--

Dave Peterson