I have finished writing my program but need help in getting the data
into sheet2. In sheet1 a command button is supposed to have the user
select a file, have the data streamed to sheet2 and delimit it using
the comma.

My first question is how can I show just *.asc with this part of the
code:
Connection:= _ "TEXT;C:\data\meth\*.ASC",
When the open file window pops up it shows txt, then I have to select
asc file type. I want to show asc files the first time without having
to select the file type. Is there anyway to remove TEXT part?

My Second question, is it possible to arrange files by date and have
the last one highlighted without actually opening it ( there are
hundreds of files)

My third question, why does the VB give a run time error 1004 when if
for example your loading a file and you press cancel. Also happens
with my inputbox(). How can I prevent this?

Any help is appreciated. Thanks
Susan Hayes

I have the following code:

With Worksheets("data1").Activate

With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\data\meth\*.ASC",
Destination:=Sheets("Data1").Range("A1") _
)
'.Name = "222003-M"
'.FieldNames = True
'.RowNumbers = False
'.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
'.SavePassword = False
'.SaveData = True
.AdjustColumnWidth = True
'.RefreshPeriod = 0
.TextFilePromptOnRefresh = True
'.TextFilePlatform = 1252
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
'.TextFileTextQualifier = xlTextQualifierDoubleQuote
'.TextFileConsecutiveDelimiter = False
'.TextFileTabDelimiter = False
'.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
'.TextFileSpaceDelimiter = False
'.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1,
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
'.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False


End With
End With

Hi Susan

Part answer:

Sub test()
Dim V As Variant, Connection As String
V = Application.GetOpenFilename( _
fileFilter:="Asc Files (*.asc), *.asc")
If V = False Then Exit Sub
Connection = CStr(V)
MsgBox Connection
End Sub

HTH. Best wishes Harald

<Susan Hayes skrev i melding
...
I have finished writing my program but need help in getting the data
into sheet2. In sheet1 a command button is supposed to have the user
select a file, have the data streamed to sheet2 and delimit it using
the comma.

My first question is how can I show just *.asc with this part of the
code:
Connection:= _ "TEXT;C:\data\meth\*.ASC",
When the open file window pops up it shows txt, then I have to select
asc file type. I want to show asc files the first time without having
to select the file type. Is there anyway to remove TEXT part?

My Second question, is it possible to arrange files by date and have
the last one highlighted without actually opening it ( there are
hundreds of files)

My third question, why does the VB give a run time error 1004 when if
for example your loading a file and you press cancel. Also happens
with my inputbox(). How can I prevent this?

Any help is appreciated. Thanks
Susan Hayes

I have the following code:

With Worksheets("data1").Activate

With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\data\meth\*.ASC",
Destination:=Sheets("Data1").Range("A1") _
)
'.Name = "222003-M"
'.FieldNames = True
'.RowNumbers = False
'.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
'.SavePassword = False
'.SaveData = True
.AdjustColumnWidth = True
'.RefreshPeriod = 0
.TextFilePromptOnRefresh = True
'.TextFilePlatform = 1252
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
'.TextFileTextQualifier = xlTextQualifierDoubleQuote
'.TextFileConsecutiveDelimiter = False
'.TextFileTabDelimiter = False
'.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
'.TextFileSpaceDelimiter = False
'.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1,
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
'.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False


End With
End With

On the second question, there seems to be no argument to open the box in a
particular sort order on Date or size. Even if the actual folder where the
files are stored is set for descending date in Windows Explorer. The only
solution I see is to sort by date once the box is open.

Third question: when you hit Cancel and get the runtime error, the code from
Harald should fix that. When you click Cancel the box returns "False"
instead of a file name, so the line 'If V = False Then Exit Sub' should
catch that. If you have already selected a file and the query is running
when you hit Cancel, what Cancel are you hitting? because the
GetOpenFilename box should be closed now.

Mike F

<Susan Hayes wrote in message
...
I have finished writing my program but need help in getting the data
into sheet2. In sheet1 a command button is supposed to have the user
select a file, have the data streamed to sheet2 and delimit it using
the comma.

My first question is how can I show just *.asc with this part of the
code:
Connection:= _ "TEXT;C:\data\meth\*.ASC",
When the open file window pops up it shows txt, then I have to select
asc file type. I want to show asc files the first time without having
to select the file type. Is there anyway to remove TEXT part?

My Second question, is it possible to arrange files by date and have
the last one highlighted without actually opening it ( there are
hundreds of files)

My third question, why does the VB give a run time error 1004 when if
for example your loading a file and you press cancel. Also happens
with my inputbox(). How can I prevent this?

Any help is appreciated. Thanks
Susan Hayes

I have the following code:

With Worksheets("data1").Activate

With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\data\meth\*.ASC",
Destination:=Sheets("Data1").Range("A1") _
)
'.Name = "222003-M"
'.FieldNames = True
'.RowNumbers = False
'.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
'.SavePassword = False
'.SaveData = True
.AdjustColumnWidth = True
'.RefreshPeriod = 0
.TextFilePromptOnRefresh = True
'.TextFilePlatform = 1252
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
'.TextFileTextQualifier = xlTextQualifierDoubleQuote
'.TextFileConsecutiveDelimiter = False
'.TextFileTabDelimiter = False
'.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
'.TextFileSpaceDelimiter = False
'.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1,
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
'.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False


End With
End With

there seems to be no argument to open the box in a
particular sort order on Date or size.

But showing the get open filename does allow the user to click on a header
to see the files in that order (in the detail view).

--
Regards,
Tom Ogilvy

"Mike Fogleman" wrote in message
news:r1LTc.294775$JR4.60864@attbi_s54...
On the second question, there seems to be no argument to open the box in a
particular sort order on Date or size. Even if the actual folder where the
files are stored is set for descending date in Windows Explorer. The only
solution I see is to sort by date once the box is open.

Third question: when you hit Cancel and get the runtime error, the code
from
Harald should fix that. When you click Cancel the box returns "False"
instead of a file name, so the line 'If V = False Then Exit Sub' should
catch that. If you have already selected a file and the query is running
when you hit Cancel, what Cancel are you hitting? because the
GetOpenFilename box should be closed now.

Mike F

<Susan Hayes wrote in message
...
I have finished writing my program but need help in getting the data
into sheet2. In sheet1 a command button is supposed to have the user
select a file, have the data streamed to sheet2 and delimit it using
the comma.

My first question is how can I show just *.asc with this part of the
code:
Connection:= _ "TEXT;C:\data\meth\*.ASC",
When the open file window pops up it shows txt, then I have to select
asc file type. I want to show asc files the first time without having
to select the file type. Is there anyway to remove TEXT part?

My Second question, is it possible to arrange files by date and have
the last one highlighted without actually opening it ( there are
hundreds of files)

My third question, why does the VB give a run time error 1004 when if
for example your loading a file and you press cancel. Also happens
with my inputbox(). How can I prevent this?

Any help is appreciated. Thanks
Susan Hayes

I have the following code:

With Worksheets("data1").Activate

With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\data\meth\*.ASC",
Destination:=Sheets("Data1").Range("A1") _
)
'.Name = "222003-M"
'.FieldNames = True
'.RowNumbers = False
'.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
'.SavePassword = False
'.SaveData = True
.AdjustColumnWidth = True
'.RefreshPeriod = 0
.TextFilePromptOnRefresh = True
'.TextFilePlatform = 1252
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
'.TextFileTextQualifier = xlTextQualifierDoubleQuote
'.TextFileConsecutiveDelimiter = False
'.TextFileTabDelimiter = False
'.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
'.TextFileSpaceDelimiter = False
'.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1,
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
'.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False


End With
End With