User picks a text file, macro defining the array

I have a macro that opens a text file and starts manipulating the data in it.
I recorded the macro by opening a specific file and setting each column.
I want the macro to ask me (via a dialog box) which text file I want to
open, once I choose the file, I want the macro to apply the correct array and
formatting.

The current language I'm using is:

ChDir "C:\Documents and Settings\RRadmacher\My Documents\Productivity"
Workbooks.OpenText Filename:= _
"C:\Documents and Settings\RRadmacher\My
Documents\Productivity\picks.txt", Origin:=xlWindows, StartRow _
:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1), Array(15,
1), Array _
(47, 1), Array(93, 1), Array(125, 1), Array(134, 1), Array(150, 1),
Array(172, 1)), _
TrailingMinusNumbers:=True

After this language, the macro begins manipulating the data on that file.

I want to tell the program which directory and file to grab (it will be a
different text file each day, instead of having one file defined in the macro?
Thanks

Dim fName as Variant
ChDir "C:\Documents and Settings\RRadmacher\My Documents\Productivity"
fname = Application.GetOpenFileName(FileFilter:= "Text Files (*.txt),*.txt")
if fName <> False then
Workbooks.OpenText Filename:=fName, _
Origin:=xlWindows, _
StartRow:=1, _
DataType:=xlFixedWidth, _
FieldInfo:=Array( _
Array(0, 1), Array(15,1), _
Array(47, 1), Array(93, 1), _
Array(125, 1), Array(134, 1), _
Array(150, 1), Array(172, 1)), _
TrailingMinusNumbers:=True
End if

--
Regards,
Tom Ogilvy


"melric" <melric@discussions.microsoft.com> wrote in message
news:E36B6817-4530-4B61-AF24-405891B8FB03@microsoft.com...
> I have a macro that opens a text file and starts manipulating the data in
it.
> I recorded the macro by opening a specific file and setting each column.
> I want the macro to ask me (via a dialog box) which text file I want to
> open, once I choose the file, I want the macro to apply the correct array
and
> formatting.
>
> The current language I'm using is:
>
> ChDir "C:\Documents and Settings\RRadmacher\My Documents\Productivity"
> Workbooks.OpenText Filename:= _
> "C:\Documents and Settings\RRadmacher\My
> Documents\Productivity\picks.txt", Origin:=xlWindows, StartRow _
> :=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1),
Array(15,
> 1), Array _
> (47, 1), Array(93, 1), Array(125, 1), Array(134, 1), Array(150,
1),
> Array(172, 1)), _
> TrailingMinusNumbers:=True
>
> After this language, the macro begins manipulating the data on that file.
>
> I want to tell the program which directory and file to grab (it will be a
> different text file each day, instead of having one file defined in the
macro?
> Thanks

I would use my cut down dialog box, which you can copy and paste to a new
Class module and call as you would a standard commondialog control:

' ###################################################################
' ©Copyright Haresoftware 2002
#
' Class Module written due to Excel not supporting
#
' the CommonDialog Control.
#
' Written by Richard J Hare, Haresoftware
#
' No copying or distribution of this code without explicit permission of
the Author #
' ###################################################################

Option Explicit
DefStr S
DefLng N
DefBool B
DefVar V

' OFN constants
Const OFN_ALLOWMULTISELECT As Long = &H200
Const OFN_CREATEPROMPT As Long = &H2000
Const OFN_EXPLORER As Long = &H80000
Const OFN_EXTENSIONDIFFERENT As Long = &H400
Const OFN_FILEMUSTEXIST As Long = &H1000
Const OFN_HIDEREADONLY As Long = &H4
Const OFN_LONGNAMES As Long = &H200000
Const OFN_NOCHANGEDIR As Long = &H8
Const OFN_NODEREFERENCELINKS As Long = &H100000
Const OFN_OVERWRITEPROMPT As Long = &H2
Const OFN_PATHMUSTEXIST As Long = &H800
Const OFN_READONLY As Long = &H1

' The maximum length of a single file path:
Const MAX_PATH As Long = 260
' This MAX_BUFFER value allows you to select approx.
' 500 files with an average length of 25 characters.
' Change this value as needed.
Const MAX_BUFFER As Long = 50 * MAX_PATH
' String constants:
Const sBackSlash As String = "\"
Const sPipe As String = "|"

' API functions to use the Windows common dialog boxes
Private Declare Function GetOpenFileName Lib "COMDLG32.DLL" Alias
"GetOpenFileNameA" (pOpenfilename As OPENFILENAME) As Long
Private Declare Function GetSaveFileName Lib "COMDLG32.DLL" Alias
"GetSaveFileNameA" (pOpenfilename As OPENFILENAME) As Long
Private Declare Function GetActiveWindow Lib "user32" () As Long

' Type declaration, used by GetOpenFileName and GetSaveFileName
Private Type OPENFILENAME
lStructSize As Long
hwndOwner As Long
hInstance As Long
lpstrFilter As String
lpstrCustomFilter As String
nMaxCustFilter As Long
nFilterIndex As Long
lpstrFile As String
nMaxFile As Long
lpstrFileTitle As String
nMaxFileTitle As Long
lpstrInitialDir As String
lpstrTitle As String
Flags As Long
nFileOffset As Integer
nFileExtension As Integer
lpstrDefExt As String
lCustData As Long
lpfnHook As Long
lpTemplateName As String ' Can also be a Long
End Type

' private variables:
Private OFN As OPENFILENAME
Private colFileTitles As New Collection
Private colFileNames As New Collection
Private sFullName
Private sFileTitle
Private sPath
Private sExtension

'public enumeration variable:
Public Enum XFlags
PathMustExist = OFN_PATHMUSTEXIST
FileMustExist = OFN_FILEMUSTEXIST
PromptToCreateFile = OFN_CREATEPROMPT
End Enum

Property Let AllowMultiSelect(bFlag)
SetFlag OFN_ALLOWMULTISELECT, bFlag
End Property

Property Let DialogTitle(sCaption)
OFN.lpstrTitle = sCaption
End Property

Property Let Filter(vFilter)
If IsArray(vFilter) Then vFilter = Join(vFilter, vbNullChar)
OFN.lpstrFilter = Replace(vFilter, sPipe, vbNullChar) & vbNullChar
End Property

Property Get Filter()
With OFN
If .nFilterIndex Then
Dim sTemp()
sTemp = Split(.lpstrFilter, vbNullChar)
Filter = sTemp(.nFilterIndex * 2 - 2) & sPipe & sTemp(.nFilterIndex * 2
- 1)
End If
End With
End Property

Property Let FilterIndex(nIndex)
OFN.nFilterIndex = nIndex
End Property

Property Get FilterIndex() As Long
FilterIndex = OFN.nFilterIndex
End Property

Property Let RestoreCurDir(bFlag)
SetFlag OFN_NOCHANGEDIR, bFlag
End Property

Property Let ExistFlags(nFlags As XFlags)
OFN.Flags = OFN.Flags Or nFlags
End Property

Property Let CheckBoxVisible(bFlag)
SetFlag OFN_HIDEREADONLY, Not bFlag
End Property

Property Let CheckBoxSelected(bFlag)
SetFlag OFN_READONLY, bFlag
End Property

Property Get CheckBoxSelected() As Boolean
CheckBoxSelected = OFN.Flags And OFN_READONLY
End Property

Property Let FileName(sFileName)
If Len(sFileName) <= MAX_PATH Then OFN.lpstrFile = sFileName
End Property

Property Get FileName() As String
FileName = sFullName
End Property

Property Get FileNames() As Collection
Set FileNames = colFileNames
End Property

Property Get FileTitle() As String
FileTitle = sFileTitle
End Property

Property Get FileTitles() As Collection
Set FileTitles = colFileTitles
End Property

Property Let Directory(sInitDir)
OFN.lpstrInitialDir = sInitDir
End Property

Property Get Directory() As String
Directory = sPath
End Property

Property Let Extension(sDefExt)
OFN.lpstrDefExt = LCase$(Left$(Replace(sDefExt, ".", vbNullString), 3))
End Property

Property Get Extension() As String
Extension = sExtension
End Property

Function ShowOpen() As Boolean
ShowOpen = Show(True)
End Function

Function ShowSave() As Boolean
' Set or clear the appropriate flags for the Save As dialog:
SetFlag OFN_ALLOWMULTISELECT, False
SetFlag OFN_PATHMUSTEXIST, True
SetFlag OFN_OVERWRITEPROMPT, True
ShowSave = Show(False)
End Function

Private Function Show(bOpen)
With OFN
.lStructSize = Len(OFN)
.hwndOwner = GetActiveWindow ' could be zero if no owner is required
' If the RO checkbox must be checked, we should also display it:
If .Flags And OFN_READONLY Then SetFlag OFN_HIDEREADONLY, False
' Create an extra large buffer if multiple file selection is allowed:
.nMaxFile = IIf(.Flags And OFN_ALLOWMULTISELECT, _
MAX_BUFFER + 1, MAX_PATH + 1)
.nMaxFileTitle = MAX_PATH + 1
' Initialize the buffers:
.lpstrFile = .lpstrFile & String$(.nMaxFile - 1 - Len(.lpstrFile), 0)
.lpstrFileTitle = String$(.nMaxFileTitle - 1, 0)
' Display the appropriate dialog:
If bOpen Then
Show = GetOpenFileName(OFN)
Else
Show = GetSaveFileName(OFN)
End If

If Show Then
' Remove trailing null characters:
Dim nDoubleNullPos
nDoubleNullPos = InStr(.lpstrFile & vbNullChar, String$(2, 0))
If nDoubleNullPos Then
' Get the file name including the path name:
sFullName = Left$(.lpstrFile, nDoubleNullPos - 1)
' Get the file name without the path name:
sFileTitle = Left$(.lpstrFileTitle, InStr(.lpstrFileTitle, vbNullChar)
- 1)
' Get the path name:
sPath = Left$(sFullName, .nFileOffset - 1)
' Get the extension:
If .nFileExtension Then
sExtension = Mid$(sFullName, .nFileExtension + 1)
End If
' if sFileTitle is a string, we have a single selection:
If Len(sFileTitle) Then
' add to the collections:
colFileTitles.Add Mid$(sFullName, .nFileOffset + 1)
colFileNames.Add sFullName
Else ' tear multiple selection apart
Dim sTemp(), nCount
sTemp = Split(sFullName, vbNullChar)
' if array contains no elements, UBound returns -1
If UBound(sTemp) > LBound(sTemp) Then
' we have more than one array element!
' remove backslash if sPath is the root folder:
If Len(sPath) = 3 Then sPath = Left$(sPath, 2)
' loop through the array, and create the collections;
' we skip the first element (which contains the path name),
' so we start the counter at 1, not at 0
For nCount = 1 To UBound(sTemp)
colFileTitles.Add sTemp(nCount)
' if the string already contains a backslash, the user
' must have selected a shortcut file, so we don' t add
' the path
colFileNames.Add IIf(InStr(sTemp(nCount), sBackSlash), _
sTemp(nCount), sPath & sBackSlash & sTemp(nCount))
Next
' clear this variable:
sFullName = vbNullString
End If
End If
' add backslash if sPath is the root folder:
If Len(sPath) = 2 Then sPath = sPath & sBackSlash
End If
End If
End With
End Function

Private Sub SetFlag(nValue, bTrue)
' wrapper routine to set or clear bit flags
With OFN
If bTrue Then
.Flags = .Flags Or nValue
Else
.Flags = .Flags And Not nValue
End If
End With
End Sub

Private Sub Class_Initialize()
' this routine runs when the object is created
OFN.Flags = OFN.Flags Or OFN_EXPLORER Or OFN_LONGNAMES Or OFN_HIDEREADONLY
End Sub








"melric" wrote:

> I have a macro that opens a text file and starts manipulating the data in it.
> I recorded the macro by opening a specific file and setting each column.
> I want the macro to ask me (via a dialog box) which text file I want to
> open, once I choose the file, I want the macro to apply the correct array and
> formatting.
>
> The current language I'm using is:
>
> ChDir "C:\Documents and Settings\RRadmacher\My Documents\Productivity"
> Workbooks.OpenText Filename:= _
> "C:\Documents and Settings\RRadmacher\My
> Documents\Productivity\picks.txt", Origin:=xlWindows, StartRow _
> :=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1), Array(15,
> 1), Array _
> (47, 1), Array(93, 1), Array(125, 1), Array(134, 1), Array(150, 1),
> Array(172, 1)), _
> TrailingMinusNumbers:=True
>
> After this language, the macro begins manipulating the data on that file.
>
> I want to tell the program which directory and file to grab (it will be a
> different text file each day, instead of having one file defined in the macro?
> Thanks

It worked!!!!!
Now, one more:
When my macro is done, I'd like another dialog window to pop up asking me
what I'd like to save the file as. (If it defaults to save as an excel
spreadsheet, all the better.) Thanks for the help.

"Tom Ogilvy" wrote:

> Dim fName as Variant
> ChDir "C:\Documents and Settings\RRadmacher\My Documents\Productivity"
> fname = Application.GetOpenFileName(FileFilter:= "Text Files (*.txt),*.txt")
> if fName <> False then
> Workbooks.OpenText Filename:=fName, _
> Origin:=xlWindows, _
> StartRow:=1, _
> DataType:=xlFixedWidth, _
> FieldInfo:=Array( _
> Array(0, 1), Array(15,1), _
> Array(47, 1), Array(93, 1), _
> Array(125, 1), Array(134, 1), _
> Array(150, 1), Array(172, 1)), _
> TrailingMinusNumbers:=True
> End if
>
> --
> Regards,
> Tom Ogilvy
>
>
> "melric" <melric@discussions.microsoft.com> wrote in message
> news:E36B6817-4530-4B61-AF24-405891B8FB03@microsoft.com...
> > I have a macro that opens a text file and starts manipulating the data in
> it.
> > I recorded the macro by opening a specific file and setting each column.
> > I want the macro to ask me (via a dialog box) which text file I want to
> > open, once I choose the file, I want the macro to apply the correct array
> and
> > formatting.
> >
> > The current language I'm using is:
> >
> > ChDir "C:\Documents and Settings\RRadmacher\My Documents\Productivity"
> > Workbooks.OpenText Filename:= _
> > "C:\Documents and Settings\RRadmacher\My
> > Documents\Productivity\picks.txt", Origin:=xlWindows, StartRow _
> > :=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1),
> Array(15,
> > 1), Array _
> > (47, 1), Array(93, 1), Array(125, 1), Array(134, 1), Array(150,
> 1),
> > Array(172, 1)), _
> > TrailingMinusNumbers:=True
> >
> > After this language, the macro begins manipulating the data on that file.
> >
> > I want to tell the program which directory and file to grab (it will be a
> > different text file each day, instead of having one file defined in the
> macro?
> > Thanks
>
>
>

Look in help at Application.GetSaveAsFileName. This returns a string with
the fully qualified path for the specified file name. You then use this
with

fName = Application.GetSaveAsFileName( see help for args)
if fName <> False then
activeworkbook.SaveAs fName
End If

--
Regards,
Tom Ogilvy


"melric" <melric@discussions.microsoft.com> wrote in message
news:148BEF4D-4B4E-42AB-AD77-EDFD586E7CB3@microsoft.com...
> It worked!!!!!
> Now, one more:
> When my macro is done, I'd like another dialog window to pop up asking me
> what I'd like to save the file as. (If it defaults to save as an excel
> spreadsheet, all the better.) Thanks for the help.
>
> "Tom Ogilvy" wrote:
>
> > Dim fName as Variant
> > ChDir "C:\Documents and Settings\RRadmacher\My Documents\Productivity"
> > fname = Application.GetOpenFileName(FileFilter:= "Text Files
(*.txt),*.txt")
> > if fName <> False then
> > Workbooks.OpenText Filename:=fName, _
> > Origin:=xlWindows, _
> > StartRow:=1, _
> > DataType:=xlFixedWidth, _
> > FieldInfo:=Array( _
> > Array(0, 1), Array(15,1), _
> > Array(47, 1), Array(93, 1), _
> > Array(125, 1), Array(134, 1), _
> > Array(150, 1), Array(172, 1)), _
> > TrailingMinusNumbers:=True
> > End if
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> > "melric" <melric@discussions.microsoft.com> wrote in message
> > news:E36B6817-4530-4B61-AF24-405891B8FB03@microsoft.com...
> > > I have a macro that opens a text file and starts manipulating the data
in
> > it.
> > > I recorded the macro by opening a specific file and setting each
column.
> > > I want the macro to ask me (via a dialog box) which text file I want
to
> > > open, once I choose the file, I want the macro to apply the correct
array
> > and
> > > formatting.
> > >
> > > The current language I'm using is:
> > >
> > > ChDir "C:\Documents and Settings\RRadmacher\My Documents\Productivity"
> > > Workbooks.OpenText Filename:= _
> > > "C:\Documents and Settings\RRadmacher\My
> > > Documents\Productivity\picks.txt", Origin:=xlWindows, StartRow _
> > > :=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1),
> > Array(15,
> > > 1), Array _
> > > (47, 1), Array(93, 1), Array(125, 1), Array(134, 1),
Array(150,
> > 1),
> > > Array(172, 1)), _
> > > TrailingMinusNumbers:=True
> > >
> > > After this language, the macro begins manipulating the data on that
file.
> > >
> > > I want to tell the program which directory and file to grab (it will
be a
> > > different text file each day, instead of having one file defined in
the
> > macro?
> > > Thanks
> >
> >
> >