Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need to make things simpler
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" wrote in message ... 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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need to make things simpler
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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need to make things simpler
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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need to make things simpler
- 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? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need to make things simpler
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help with a procedure to make things easier | Excel Discussion (Misc queries) | |||
need a template for quilters/crafts for things to make | Excel Worksheet Functions | |||
How can I make this simpler? | Excel Programming | |||
Make it more simple or intuitive to do simple things | Charts and Charting in Excel | |||
Need to make this simpler. | Excel Programming |