Re: xlDialogOpen

["Walter Lake" <wlake@xxxxxxxxx>]

Yes you can use the "xlDialogOpen" method instead of the "GetOpenFilename"
method to open ".txt" files.

Excel has 100+ "application.Dialog" that you can use ... see the help files.
For example:

xlDialogActivate
xlDialogActiveCellFont
xlDialogAddChartAutoformat ... etc.

The longer "GetOpenFilename" code below could be rewritten as follows:

Sub ImportTextFile()
      ChDrive "D"
      ChDir "D:\TurtleData\Soybeans"
      Application.Dialogs(xlDialogOpen).show
      for each wkbk in applications.workbook
       if Ucase(wkbk.name) <> Ucase("SoyBeans_30YrData.xls") then
           wkbk.worksheets(1).copy _
           Before:=Workbooks("SoyBeans_30YrData.xls").Sheets(2)
       end if
End sub

This is a step backward in functionality.

It looks simpler, but you have lost all control.  If you are opening a text
file you will be taken through the text wizard with each file you open.  It
would be pretty much the same as doing it through the menu File=>Open.

Thanks to Tom O. for help in writing the code.

Best regards

Walter


----- Original Message -----
From: Walter Lake <wlake@xxxxxxxxx>
To: <metastock@xxxxxxxxxxxxx>
Sent: Monday, October 04, 1999 10:32 PM
Subject: Re: Excel 2000 VBa book--some details reqd.


| Thanks Ton for the info on importing data from sheets into Metastock.
|
| Here's some VBA for opening multiple text files into Excel using
Shift+click
| or Ctrl+click.
| Thanks to Tom for the assistance in coding "loops"
| ChDir = Change Directory   --> enter address of your target folder with
the
| text files to download
| Enter destination workbook and sheet position in 6th last line
| I've never tried it to download directly off the internet.
|
| Best regards
|
| Walter
| ======================
|
| Sub ImportTextFile()
|      Dim myFile as Variant
|      ChDir "D:\TurtleData\Soybeans"
|      myFile = Application.GetOpenFilename("Text
| Files,*.txt",MultiSelect:=True)
|      if isarray(myFile) then
|      for i = lbound(myfile) to ubound(myfile)
|       Workbooks.OpenText _
|          Filename:=myFile(i), _
|          Origin:=xlWindows, _
|          StartRow:=1, _
|          DataType:=xlDelimited, _
|          TextQualifier:=xlDoubleQuote, _
|          ConsecutiveDelimiter:=False, _
|          Tab:=True, _
|          Semicolon:=False, _
|          Comma:=True, _
|          Space:=False, _
|          Other:=False, _
|          FieldInfo:=Array(Array(1, 1), _
|          Array(2, 1), Array(3, 1), _
|          Array(4, 1), Array(5, 1), _
|          Array(6, 1), Array(7, 1))
|        ActiveSheet.Move _
|          Before:=Workbooks("SoyBeans_30YrData.xls").Sheets(2)
|      Next i
|      else
|        msgbox "You hit cancel"
|      End if
|  End Sub
|
|