Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cannot obtain Filename
Hi,
I have the following code to open in turn all files in a directory and run a similar routine on them. The programming will recognise the file path, but my "Fname" is blank and not picking up any of the files in the directory Help gratefully recieved... Private Sub ImportNew_Click() Dim Fileloc As String Dim Fname As String Dim wkbk As Workbook Fileloc = "C:\Documents and Settings\FINPORT03\My Documents\Share\NR6 Draft.rpt" Fname = Dir(Fileloc & "*.rpt") While Fname < "" Workbooks.OpenText FileDir & Fname ConvertCostCenterMacro Thanks Graham |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cannot obtain Filename
Your Fileloc string does not look OK. You should have the folder path here
including a \ (backslash) at the end. Together with "*.rpt" the Dir command will look for all files ending of that type. "GrahamL" wrote: Hi, I have the following code to open in turn all files in a directory and run a similar routine on them. The programming will recognise the file path, but my "Fname" is blank and not picking up any of the files in the directory Help gratefully recieved... Private Sub ImportNew_Click() Dim Fileloc As String Dim Fname As String Dim wkbk As Workbook Fileloc = "C:\Documents and Settings\FINPORT03\My Documents\Share\NR6 Draft.rpt" Fname = Dir(Fileloc & "*.rpt") While Fname < "" Workbooks.OpenText FileDir & Fname ConvertCostCenterMacro Thanks Graham |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cannot obtain Filename
Thanks..I have amended to
Fileloc = "C:\Documents and Settings\FINPORT03\My Documents\Share\NR6 Draft.rpt\ & *.rpt" Fname = Dir(Fileloc) ....but still no joy ...Fname = "" Graham "Alok" wrote: Your Fileloc string does not look OK. You should have the folder path here including a \ (backslash) at the end. Together with "*.rpt" the Dir command will look for all files ending of that type. "GrahamL" wrote: Hi, I have the following code to open in turn all files in a directory and run a similar routine on them. The programming will recognise the file path, but my "Fname" is blank and not picking up any of the files in the directory Help gratefully recieved... Private Sub ImportNew_Click() Dim Fileloc As String Dim Fname As String Dim wkbk As Workbook Fileloc = "C:\Documents and Settings\FINPORT03\My Documents\Share\NR6 Draft.rpt" Fname = Dir(Fileloc & "*.rpt") While Fname < "" Workbooks.OpenText FileDir & Fname ConvertCostCenterMacro Thanks Graham |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cannot obtain Filename
Graham,
The problem is that : Fileloc = "C:\Documents and Settings\FINPORT03\My Documents\Share\NR6 Draft.rpt already points to a single file ("NR6 Draft.rpt"), not a folder. So you cannot add a file name to the end of that. The containing folder is : "C:\Documents and Settings\FINPORT03\My Documents\Share\" Is that the folder want to search with Dir() ? NickHK "GrahamL" ... Thanks..I have amended to Fileloc = "C:\Documents and Settings\FINPORT03\My Documents\Share\NR6 Draft.rpt\ & *.rpt" Fname = Dir(Fileloc) ...but still no joy ...Fname = "" Graham "Alok" wrote: Your Fileloc string does not look OK. You should have the folder path here including a \ (backslash) at the end. Together with "*.rpt" the Dir command will look for all files ending of that type. "GrahamL" wrote: Hi, I have the following code to open in turn all files in a directory and run a similar routine on them. The programming will recognise the file path, but my "Fname" is blank and not picking up any of the files in the directory Help gratefully recieved... Private Sub ImportNew_Click() Dim Fileloc As String Dim Fname As String Dim wkbk As Workbook Fileloc = "C:\Documents and Settings\FINPORT03\My Documents\Share\NR6 Draft.rpt" Fname = Dir(Fileloc & "*.rpt") While Fname < "" Workbooks.OpenText FileDir & Fname ConvertCostCenterMacro Thanks Graham |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cannot obtain Filename
See NikHK's response. On the other hand if your folder is named
"C:\Documents and Settings\FINPORT03\My Documents\Share\NR6 Draft.rpt\" then you do not need & in the Fileloc string. It should be Fileloc = "C:\Documents and Settings\FINPORT03\My Documents\Share\NR6 Draft.rpt\*.rpt" "GrahamL" wrote: Thanks..I have amended to Fileloc = "C:\Documents and Settings\FINPORT03\My Documents\Share\NR6 Draft.rpt\ & *.rpt" Fname = Dir(Fileloc) ...but still no joy ...Fname = "" Graham "Alok" wrote: Your Fileloc string does not look OK. You should have the folder path here including a \ (backslash) at the end. Together with "*.rpt" the Dir command will look for all files ending of that type. "GrahamL" wrote: Hi, I have the following code to open in turn all files in a directory and run a similar routine on them. The programming will recognise the file path, but my "Fname" is blank and not picking up any of the files in the directory Help gratefully recieved... Private Sub ImportNew_Click() Dim Fileloc As String Dim Fname As String Dim wkbk As Workbook Fileloc = "C:\Documents and Settings\FINPORT03\My Documents\Share\NR6 Draft.rpt" Fname = Dir(Fileloc & "*.rpt") While Fname < "" Workbooks.OpenText FileDir & Fname ConvertCostCenterMacro Thanks Graham |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cannot obtain Filename
Hi,
Thanks and sorry to add to the confusion...my folder is actually called NR6 Draft . rpt and contains a multitude of other files with .rpt file type. This routine ran perfectly well for years, but I have recently transfered to XL 2000 from 97 version which must have something to do with the problem. Graham "NickHK" wrote: Graham, The problem is that : Fileloc = "C:\Documents and Settings\FINPORT03\My Documents\Share\NR6 Draft.rpt already points to a single file ("NR6 Draft.rpt"), not a folder. So you cannot add a file name to the end of that. The containing folder is : "C:\Documents and Settings\FINPORT03\My Documents\Share\" Is that the folder want to search with Dir() ? NickHK "GrahamL" ... Thanks..I have amended to Fileloc = "C:\Documents and Settings\FINPORT03\My Documents\Share\NR6 Draft.rpt\ & *.rpt" Fname = Dir(Fileloc) ...but still no joy ...Fname = "" Graham "Alok" wrote: Your Fileloc string does not look OK. You should have the folder path here including a \ (backslash) at the end. Together with "*.rpt" the Dir command will look for all files ending of that type. "GrahamL" wrote: Hi, I have the following code to open in turn all files in a directory and run a similar routine on them. The programming will recognise the file path, but my "Fname" is blank and not picking up any of the files in the directory Help gratefully recieved... Private Sub ImportNew_Click() Dim Fileloc As String Dim Fname As String Dim wkbk As Workbook Fileloc = "C:\Documents and Settings\FINPORT03\My Documents\Share\NR6 Draft.rpt" Fname = Dir(Fileloc & "*.rpt") While Fname < "" Workbooks.OpenText FileDir & Fname ConvertCostCenterMacro Thanks Graham |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cannot obtain Filename
Alok,
I did assume that Fileloc = "C:\Documents and Settings\FINPORT03\My Documents\Share\NR6 Draft.rpt" actually referred to file, rather than a folder with a "." in the name, which would be valid. But you response covers that. NickHK "Alok" ... See NikHK's response. On the other hand if your folder is named "C:\Documents and Settings\FINPORT03\My Documents\Share\NR6 Draft.rpt\" then you do not need & in the Fileloc string. It should be Fileloc = "C:\Documents and Settings\FINPORT03\My Documents\Share\NR6 Draft.rpt\*.rpt" "GrahamL" wrote: Thanks..I have amended to Fileloc = "C:\Documents and Settings\FINPORT03\My Documents\Share\NR6 Draft.rpt\ & *.rpt" Fname = Dir(Fileloc) ...but still no joy ...Fname = "" Graham "Alok" wrote: Your Fileloc string does not look OK. You should have the folder path here including a \ (backslash) at the end. Together with "*.rpt" the Dir command will look for all files ending of that type. "GrahamL" wrote: Hi, I have the following code to open in turn all files in a directory and run a similar routine on them. The programming will recognise the file path, but my "Fname" is blank and not picking up any of the files in the directory Help gratefully recieved... Private Sub ImportNew_Click() Dim Fileloc As String Dim Fname As String Dim wkbk As Workbook Fileloc = "C:\Documents and Settings\FINPORT03\My Documents\Share\NR6 Draft.rpt" Fname = Dir(Fileloc & "*.rpt") While Fname < "" Workbooks.OpenText FileDir & Fname ConvertCostCenterMacro Thanks Graham |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cannot obtain Filename
Fileloc = "C:\Documents and Settings\FINPORT03\" & _ "My Documents\Share\NR6 Draft.rpt\" Fname = Dir(Fileloc & "*.rpt") -- Regards, Tom Ogilvy "GrahamL" wrote: Hi, Thanks and sorry to add to the confusion...my folder is actually called NR6 Draft . rpt and contains a multitude of other files with .rpt file type. This routine ran perfectly well for years, but I have recently transfered to XL 2000 from 97 version which must have something to do with the problem. Graham "NickHK" wrote: Graham, The problem is that : Fileloc = "C:\Documents and Settings\FINPORT03\My Documents\Share\NR6 Draft.rpt already points to a single file ("NR6 Draft.rpt"), not a folder. So you cannot add a file name to the end of that. The containing folder is : "C:\Documents and Settings\FINPORT03\My Documents\Share\" Is that the folder want to search with Dir() ? NickHK "GrahamL" ... Thanks..I have amended to Fileloc = "C:\Documents and Settings\FINPORT03\My Documents\Share\NR6 Draft.rpt\ & *.rpt" Fname = Dir(Fileloc) ...but still no joy ...Fname = "" Graham "Alok" wrote: Your Fileloc string does not look OK. You should have the folder path here including a \ (backslash) at the end. Together with "*.rpt" the Dir command will look for all files ending of that type. "GrahamL" wrote: Hi, I have the following code to open in turn all files in a directory and run a similar routine on them. The programming will recognise the file path, but my "Fname" is blank and not picking up any of the files in the directory Help gratefully recieved... Private Sub ImportNew_Click() Dim Fileloc As String Dim Fname As String Dim wkbk As Workbook Fileloc = "C:\Documents and Settings\FINPORT03\My Documents\Share\NR6 Draft.rpt" Fname = Dir(Fileloc & "*.rpt") While Fname < "" Workbooks.OpenText FileDir & Fname ConvertCostCenterMacro Thanks Graham |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to: obtain the filename that a module exists in, via VBA? | Excel Programming | |||
Converting a Variable Filename to a Constant Filename | Excel Programming | |||
set filename to <filename-date on open | Excel Worksheet Functions | |||
set excel <filename to <filename-date | Excel Programming | |||
Saving filename same as import filename | Excel Programming |