Need to make things simpler

I have a workbook that I run the following code in:

Dim FName As Variant
Dim BankNum As Integer
Dim i As Long
Dim aryWBs
ChDrive ActiveWorkbook.Path
ChDir ActiveWorkbook.Path
FName = Application.GetOpenFilename("CSV Files,*.csv", MultiSelect:=True)
ReDim aryWBs(LBound(FName) To UBound(FName))
For i = LBound(FName) To UBound(FName)
Set aryWBs(i) = Workbooks.Open(FName(i))


Application.ScreenUpdating = False
Next


I then display an input box that asks the user for a 3 digit number that
corresponds to the files they just opened. I would like to eliminate the need
for the input box but pulling the first three characters from any one of the
files that were just opened. All of the file they user will open will be
preceeded by a 3 digit number and that 3 digit number is the number I have
been asking them to input. I tried to do something like:

Dim BankNum as Integer
BankNum = Left(FName, 3)

or

Dim BankNum as String
BankNum = Left(FName, 3)

BUT neiher seemed to work down the road. Can anyone help?

FName will contain the entire path, so the left 3 chars will be something
like C:\
Use FileName = Dir(Fname)
now Filename will be only the name, without the path.
Bob Umlas
Excel MVP

"hshayh0rn" <[email protected]> wrote in message
news:[email protected]...
> I have a workbook that I run the following code in:
>
> Dim FName As Variant
> Dim BankNum As Integer
> Dim i As Long
> Dim aryWBs
> ChDrive ActiveWorkbook.Path
> ChDir ActiveWorkbook.Path
> FName = Application.GetOpenFilename("CSV Files,*.csv",
MultiSelect:=True)
> ReDim aryWBs(LBound(FName) To UBound(FName))
> For i = LBound(FName) To UBound(FName)
> Set aryWBs(i) = Workbooks.Open(FName(i))
>
>
> Application.ScreenUpdating = False
> Next
>
>
> I then display an input box that asks the user for a 3 digit number that
> corresponds to the files they just opened. I would like to eliminate the
need
> for the input box but pulling the first three characters from any one of
the
> files that were just opened. All of the file they user will open will be
> preceeded by a 3 digit number and that 3 digit number is the number I have
> been asking them to input. I tried to do something like:
>
> Dim BankNum as Integer
> BankNum = Left(FName, 3)
>
> or
>
> Dim BankNum as String
> BankNum = Left(FName, 3)
>
> BUT neiher seemed to work down the road. Can anyone help?
>

Hi,
The returned value when allowing multi-select is an array of variant,
therefore try something like:

....
FName = Application.GetOpenFilename("CSV Files,*.csv", MultiSelect:=True)

If TypeName(FName) = "Boolean" Then
MsgBox "Cancelled by user."
Else
Dim v As Variant, s As String
For Each v In FName
'remove path
s = Right(v, Len(v) - InStrRev(v, Application.PathSeparator))
'get 3 first digit
s = Left(s, 3)
'Display
MsgBox s & " --> " & v
Next
End If
End Sub

--
Regards,
Sébastien
<http://www.ondemandanalysis.com>


"hshayh0rn" wrote:

> I have a workbook that I run the following code in:
>
> Dim FName As Variant
> Dim BankNum As Integer
> Dim i As Long
> Dim aryWBs
> ChDrive ActiveWorkbook.Path
> ChDir ActiveWorkbook.Path
> FName = Application.GetOpenFilename("CSV Files,*.csv", MultiSelect:=True)
> ReDim aryWBs(LBound(FName) To UBound(FName))
> For i = LBound(FName) To UBound(FName)
> Set aryWBs(i) = Workbooks.Open(FName(i))
>
>
> Application.ScreenUpdating = False
> Next
>
>
> I then display an input box that asks the user for a 3 digit number that
> corresponds to the files they just opened. I would like to eliminate the need
> for the input box but pulling the first three characters from any one of the
> files that were just opened. All of the file they user will open will be
> preceeded by a 3 digit number and that 3 digit number is the number I have
> been asking them to input. I tried to do something like:
>
> Dim BankNum as Integer
> BankNum = Left(FName, 3)
>
> or
>
> Dim BankNum as String
> BankNum = Left(FName, 3)
>
> BUT neiher seemed to work down the road. Can anyone help?
>

Thanks Sebastienm it works great!! What would I change if I didn't want to
look through all of the files the users selects though? I only need the first
three digits of one of the files. The 3 digts will be the same for all of
them. I realize it doesn't take long to loop through all of the files but I
would like to have clean code.

"sebastienm" wrote:

> Hi,
> The returned value when allowing multi-select is an array of variant,
> therefore try something like:
>
> ...
> FName = Application.GetOpenFilename("CSV Files,*.csv", MultiSelect:=True)
>
> If TypeName(FName) = "Boolean" Then
> MsgBox "Cancelled by user."
> Else
> Dim v As Variant, s As String
> For Each v In FName
> 'remove path
> s = Right(v, Len(v) - InStrRev(v, Application.PathSeparator))
> 'get 3 first digit
> s = Left(s, 3)
> 'Display
> MsgBox s & " --> " & v
> Next
> End If
> End Sub
>
> --
> Regards,
> Sébastien
> <http://www.ondemandanalysis.com>
>
>
> "hshayh0rn" wrote:
>
> > I have a workbook that I run the following code in:
> >
> > Dim FName As Variant
> > Dim BankNum As Integer
> > Dim i As Long
> > Dim aryWBs
> > ChDrive ActiveWorkbook.Path
> > ChDir ActiveWorkbook.Path
> > FName = Application.GetOpenFilename("CSV Files,*.csv", MultiSelect:=True)
> > ReDim aryWBs(LBound(FName) To UBound(FName))
> > For i = LBound(FName) To UBound(FName)
> > Set aryWBs(i) = Workbooks.Open(FName(i))
> >
> >
> > Application.ScreenUpdating = False
> > Next
> >
> >
> > I then display an input box that asks the user for a 3 digit number that
> > corresponds to the files they just opened. I would like to eliminate the need
> > for the input box but pulling the first three characters from any one of the
> > files that were just opened. All of the file they user will open will be
> > preceeded by a 3 digit number and that 3 digit number is the number I have
> > been asking them to input. I tried to do something like:
> >
> > Dim BankNum as Integer
> > BankNum = Left(FName, 3)
> >
> > or
> >
> > Dim BankNum as String
> > BankNum = Left(FName, 3)
> >
> > BUT neiher seemed to work down the road. Can anyone help?
> >

- just replace the line
For Each v In FName
by
v = FName(1)
- and delte the line : Next (corresponding to the previous For loop)

Regards,
Sébastien
<http://www.ondemandanalysis.com>


"hshayh0rn" wrote:

> Thanks Sebastienm it works great!! What would I change if I didn't want to
> look through all of the files the users selects though? I only need the first
> three digits of one of the files. The 3 digts will be the same for all of
> them. I realize it doesn't take long to loop through all of the files but I
> would like to have clean code.
>
> "sebastienm" wrote:
>
> > Hi,
> > The returned value when allowing multi-select is an array of variant,
> > therefore try something like:
> >
> > ...
> > FName = Application.GetOpenFilename("CSV Files,*.csv", MultiSelect:=True)
> >
> > If TypeName(FName) = "Boolean" Then
> > MsgBox "Cancelled by user."
> > Else
> > Dim v As Variant, s As String
> > For Each v In FName
> > 'remove path
> > s = Right(v, Len(v) - InStrRev(v, Application.PathSeparator))
> > 'get 3 first digit
> > s = Left(s, 3)
> > 'Display
> > MsgBox s & " --> " & v
> > Next
> > End If
> > End Sub
> >
> > --
> > Regards,
> > Sébastien
> > <http://www.ondemandanalysis.com>
> >
> >
> > "hshayh0rn" wrote:
> >
> > > I have a workbook that I run the following code in:
> > >
> > > Dim FName As Variant
> > > Dim BankNum As Integer
> > > Dim i As Long
> > > Dim aryWBs
> > > ChDrive ActiveWorkbook.Path
> > > ChDir ActiveWorkbook.Path
> > > FName = Application.GetOpenFilename("CSV Files,*.csv", MultiSelect:=True)
> > > ReDim aryWBs(LBound(FName) To UBound(FName))
> > > For i = LBound(FName) To UBound(FName)
> > > Set aryWBs(i) = Workbooks.Open(FName(i))
> > >
> > >
> > > Application.ScreenUpdating = False
> > > Next
> > >
> > >
> > > I then display an input box that asks the user for a 3 digit number that
> > > corresponds to the files they just opened. I would like to eliminate the need
> > > for the input box but pulling the first three characters from any one of the
> > > files that were just opened. All of the file they user will open will be
> > > preceeded by a 3 digit number and that 3 digit number is the number I have
> > > been asking them to input. I tried to do something like:
> > >
> > > Dim BankNum as Integer
> > > BankNum = Left(FName, 3)
> > >
> > > or
> > >
> > > Dim BankNum as String
> > > BankNum = Left(FName, 3)
> > >
> > > BUT neiher seemed to work down the road. Can anyone help?
> > >

Thanks again for the help Sebastienm! Would you mind looking at another post
I have out there. The post is titled: 'Move from a function statement to VBA
Code'

Thanks again!



"sebastienm" wrote:

> - just replace the line
> For Each v In FName
> by
> v = FName(1)
> - and delte the line : Next (corresponding to the previous For loop)
>
> Regards,
> Sébastien
> <http://www.ondemandanalysis.com>
>
>
> "hshayh0rn" wrote:
>
> > Thanks Sebastienm it works great!! What would I change if I didn't want to
> > look through all of the files the users selects though? I only need the first
> > three digits of one of the files. The 3 digts will be the same for all of
> > them. I realize it doesn't take long to loop through all of the files but I
> > would like to have clean code.
> >
> > "sebastienm" wrote:
> >
> > > Hi,
> > > The returned value when allowing multi-select is an array of variant,
> > > therefore try something like:
> > >
> > > ...
> > > FName = Application.GetOpenFilename("CSV Files,*.csv", MultiSelect:=True)
> > >
> > > If TypeName(FName) = "Boolean" Then
> > > MsgBox "Cancelled by user."
> > > Else
> > > Dim v As Variant, s As String
> > > For Each v In FName
> > > 'remove path
> > > s = Right(v, Len(v) - InStrRev(v, Application.PathSeparator))
> > > 'get 3 first digit
> > > s = Left(s, 3)
> > > 'Display
> > > MsgBox s & " --> " & v
> > > Next
> > > End If
> > > End Sub
> > >
> > > --
> > > Regards,
> > > Sébastien
> > > <http://www.ondemandanalysis.com>
> > >
> > >
> > > "hshayh0rn" wrote:
> > >
> > > > I have a workbook that I run the following code in:
> > > >
> > > > Dim FName As Variant
> > > > Dim BankNum As Integer
> > > > Dim i As Long
> > > > Dim aryWBs
> > > > ChDrive ActiveWorkbook.Path
> > > > ChDir ActiveWorkbook.Path
> > > > FName = Application.GetOpenFilename("CSV Files,*.csv", MultiSelect:=True)
> > > > ReDim aryWBs(LBound(FName) To UBound(FName))
> > > > For i = LBound(FName) To UBound(FName)
> > > > Set aryWBs(i) = Workbooks.Open(FName(i))
> > > >
> > > >
> > > > Application.ScreenUpdating = False
> > > > Next
> > > >
> > > >
> > > > I then display an input box that asks the user for a 3 digit number that
> > > > corresponds to the files they just opened. I would like to eliminate the need
> > > > for the input box but pulling the first three characters from any one of the
> > > > files that were just opened. All of the file they user will open will be
> > > > preceeded by a 3 digit number and that 3 digit number is the number I have
> > > > been asking them to input. I tried to do something like:
> > > >
> > > > Dim BankNum as Integer
> > > > BankNum = Left(FName, 3)
> > > >
> > > > or
> > > >
> > > > Dim BankNum as String
> > > > BankNum = Left(FName, 3)
> > > >
> > > > BUT neiher seemed to work down the road. Can anyone help?
> > > >