Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
retrieve filenames from given directory into excel
Hello,
I'm trying to retrive files from a given directory into excel. I already have this file from another posted message: Sub GetFileNames() Dim F As Long Dim FileName As String Dim TheNames As Variant ReDim TheNames(1 To 1) FileName = Dir$("*.*") Do While Len(FileName) F = F + 1 ReDim Preserve TheNames(1 To F) TheNames(F) = FileName FileName = Dir$() Loop Cells(1, 1).Resize(F, 1).Value = Application.Transpose(TheNames) End Sub But I want to be able to set the path myself thanks |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
retrieve filenames from given directory into excel
I use Jim Cone's fine Add-in called "ListFiles" for this sort of thing.
It's available for free at......... http://www.realezsites.com/bus/primi...e/products.php Vaya con Dios, Chuck, CABGx3 wrote in message oups.com... Hello, I'm trying to retrive files from a given directory into excel. I already have this file from another posted message: Sub GetFileNames() Dim F As Long Dim FileName As String Dim TheNames As Variant ReDim TheNames(1 To 1) FileName = Dir$("*.*") Do While Len(FileName) F = F + 1 ReDim Preserve TheNames(1 To F) TheNames(F) = FileName FileName = Dir$() Loop Cells(1, 1).Resize(F, 1).Value = Application.Transpose(TheNames) End Sub But I want to be able to set the path myself thanks |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
retrieve filenames from given directory into excel
CLR wrote: I use Jim Cone's fine Add-in called "ListFiles" for this sort of thing. It's available for free at......... http://www.realezsites.com/bus/primi...e/products.php Vaya con Dios, Chuck, CABGx3 wrote in message oups.com... Hello, I'm trying to retrive files from a given directory into excel. I already have this file from another posted message: Sub GetFileNames() Dim F As Long Dim FileName As String Dim TheNames As Variant ReDim TheNames(1 To 1) FileName = Dir$("*.*") Do While Len(FileName) F = F + 1 ReDim Preserve TheNames(1 To F) TheNames(F) = FileName FileName = Dir$() Loop Cells(1, 1).Resize(F, 1).Value = Application.Transpose(TheNames) End Sub But I want to be able to set the path myself thanks Many thanks chuck But I need this as a part of another "sub" so I'm looking for code Regards, Giel |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
retrieve filenames from given directory into excel
Hi
Maybe you can adjust this function: ---------- Public Function GetMyFile(MyFolder As String, FileNum As Integer, MyExtension As String) Dim fs, f, f1, fc, s Set fs = CreateObject("Scripting.FileSystemObject") Set f = fs.GetFolder(MyFolder) Set fc = f.Files i = 0 For Each f1 In fc If Right(f1.Name, 3) = MyExtension Then i = i + 1 If i = FileNum Then GetMyFile = f1.Name End If Next End Function ----------- The function returns the name of n-th file with estimated extension from estimated folder. Arvi Laanemets wrote in message oups.com... Hello, I'm trying to retrive files from a given directory into excel. I already have this file from another posted message: Sub GetFileNames() Dim F As Long Dim FileName As String Dim TheNames As Variant ReDim TheNames(1 To 1) FileName = Dir$("*.*") Do While Len(FileName) F = F + 1 ReDim Preserve TheNames(1 To F) TheNames(F) = FileName FileName = Dir$() Loop Cells(1, 1).Resize(F, 1).Value = Application.Transpose(TheNames) End Sub But I want to be able to set the path myself thanks |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
retrieve filenames from given directory into excel
erweurw
Arvi Laanemets wrote: Hi Maybe you can adjust this function: ---------- Public Function GetMyFile(MyFolder As String, FileNum As Integer, MyExtension As String) Dim fs, f, f1, fc, s Set fs = CreateObject("Scripting.FileSystemObject") Set f = fs.GetFolder(MyFolder) Set fc = f.Files i = 0 For Each f1 In fc If Right(f1.Name, 3) = MyExtension Then i = i + 1 If i = FileNum Then GetMyFile = f1.Name End If Next End Function ----------- The function returns the name of n-th file with estimated extension from estimated folder. Arvi Laanemets wrote in message oups.com... Hello, I'm trying to retrive files from a given directory into excel. I already have this file from another posted message: Sub GetFileNames() Dim F As Long Dim FileName As String Dim TheNames As Variant ReDim TheNames(1 To 1) FileName = Dir$("*.*") Do While Len(FileName) F = F + 1 ReDim Preserve TheNames(1 To F) TheNames(F) = FileName FileName = Dir$() Loop Cells(1, 1).Resize(F, 1).Value = Application.Transpose(TheNames) End Sub But I want to be able to set the path myself thanks |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
retrieve filenames from given directory into excel
Arvi,
Did you check this code? When I'm calling this function I get no results. Regards, Giel Arvi Laanemets wrote: Hi Maybe you can adjust this function: ---------- Public Function GetMyFile(MyFolder As String, FileNum As Integer, MyExtension As String) Dim fs, f, f1, fc, s Set fs = CreateObject("Scripting.FileSystemObject") Set f = fs.GetFolder(MyFolder) Set fc = f.Files i = 0 For Each f1 In fc If Right(f1.Name, 3) = MyExtension Then i = i + 1 If i = FileNum Then GetMyFile = f1.Name End If Next End Function ----------- The function returns the name of n-th file with estimated extension from estimated folder. Arvi Laanemets wrote in message oups.com... Hello, I'm trying to retrive files from a given directory into excel. I already have this file from another posted message: Sub GetFileNames() Dim F As Long Dim FileName As String Dim TheNames As Variant ReDim TheNames(1 To 1) FileName = Dir$("*.*") Do While Len(FileName) F = F + 1 ReDim Preserve TheNames(1 To F) TheNames(F) = FileName FileName = Dir$() Loop Cells(1, 1).Resize(F, 1).Value = Application.Transpose(TheNames) End Sub But I want to be able to set the path myself thanks |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
retrieve filenames from given directory into excel
Hi
1. Created a new excel file; 2. Actrivated VBA editor; 3. Added a module; 4. Copied the code from my posting into module; 5. Closed the VBA editor; 6. Into any cell, entered the formula =GetMyFile("C:\Documents and Settings\Arvi\My Documents",1,"doc") A word document's name from My Documents folder is returned. =GetMyFile("C:\Documents and Settings\Arvi\My Documents",2,"doc") A second word document's name from My Documents folder is returned, etc. Arvi Laanemets wrote in message oups.com... Arvi, Did you check this code? When I'm calling this function I get no results. Regards, Giel Arvi Laanemets wrote: Hi Maybe you can adjust this function: ---------- Public Function GetMyFile(MyFolder As String, FileNum As Integer, MyExtension As String) Dim fs, f, f1, fc, s Set fs = CreateObject("Scripting.FileSystemObject") Set f = fs.GetFolder(MyFolder) Set fc = f.Files i = 0 For Each f1 In fc If Right(f1.Name, 3) = MyExtension Then i = i + 1 If i = FileNum Then GetMyFile = f1.Name End If Next End Function ----------- The function returns the name of n-th file with estimated extension from estimated folder. Arvi Laanemets wrote in message oups.com... Hello, I'm trying to retrive files from a given directory into excel. I already have this file from another posted message: Sub GetFileNames() Dim F As Long Dim FileName As String Dim TheNames As Variant ReDim TheNames(1 To 1) FileName = Dir$("*.*") Do While Len(FileName) F = F + 1 ReDim Preserve TheNames(1 To F) TheNames(F) = FileName FileName = Dir$() Loop Cells(1, 1).Resize(F, 1).Value = Application.Transpose(TheNames) End Sub But I want to be able to set the path myself thanks |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
retrieve filenames from given directory into excel
Arvi,
This is great... I was planning to make this work with an "open" dialog. Then the user could choose the directory. But this works even better. Thanks for the help! Regards, Giel Arvi Laanemets wrote: Hi 1. Created a new excel file; 2. Actrivated VBA editor; 3. Added a module; 4. Copied the code from my posting into module; 5. Closed the VBA editor; 6. Into any cell, entered the formula =GetMyFile("C:\Documents and Settings\Arvi\My Documents",1,"doc") A word document's name from My Documents folder is returned. =GetMyFile("C:\Documents and Settings\Arvi\My Documents",2,"doc") A second word document's name from My Documents folder is returned, etc. Arvi Laanemets wrote in message oups.com... Arvi, Did you check this code? When I'm calling this function I get no results. Regards, Giel Arvi Laanemets wrote: Hi Maybe you can adjust this function: ---------- Public Function GetMyFile(MyFolder As String, FileNum As Integer, MyExtension As String) Dim fs, f, f1, fc, s Set fs = CreateObject("Scripting.FileSystemObject") Set f = fs.GetFolder(MyFolder) Set fc = f.Files i = 0 For Each f1 In fc If Right(f1.Name, 3) = MyExtension Then i = i + 1 If i = FileNum Then GetMyFile = f1.Name End If Next End Function ----------- The function returns the name of n-th file with estimated extension from estimated folder. Arvi Laanemets wrote in message oups.com... Hello, I'm trying to retrive files from a given directory into excel. I already have this file from another posted message: Sub GetFileNames() Dim F As Long Dim FileName As String Dim TheNames As Variant ReDim TheNames(1 To 1) FileName = Dir$("*.*") Do While Len(FileName) F = F + 1 ReDim Preserve TheNames(1 To F) TheNames(F) = FileName FileName = Dir$() Loop Cells(1, 1).Resize(F, 1).Value = Application.Transpose(TheNames) End Sub But I want to be able to set the path myself thanks |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
retrieve filenames from given directory into excel
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
retrieve filenames from given directory into excel
Hi
wrote in message oups.com... I know now what the problem was in the first time. It seems like it does not work in office 2000 ??? I have Office2000 ! Btw., I often use this UDF combined with function ROW(), and another 2 UDF's, which you find below. -- Arvi Laanemets ( My real mail address: arvi.laanemets<attarkon.ee ) -------------- Public Function GetThisFolder(Optional MyTime As Date) GetThisFolder = ThisWorkbook.Path End Function Public Function GetSubfolder(MyFolder As String, FolderNum As Integer, Optional MyTime As Date) Dim fs, f, f1, s, sf Set fs = CreateObject("Scripting.FileSystemObject") Set f = fs.GetFolder(MyFolder) Set sf = f.SubFolders i = 0 For Each f1 In sf i = i + 1 If i = FolderNum Then GetSubfolder = f1.Name Next End Function |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
retrieve filenames from given directory into excel
Extention ee
what is that Estonia? Regards, Giel PS. If I can do something for you? I'm in the printing business. If you have PDF material to print, I can do that for you and send it anywhere you like. thats for free of course... Arvi Laanemets wrote: Hi wrote in message oups.com... I know now what the problem was in the first time. It seems like it does not work in office 2000 ??? I have Office2000 ! Btw., I often use this UDF combined with function ROW(), and another 2 UDF's, which you find below. -- Arvi Laanemets ( My real mail address: arvi.laanemets<attarkon.ee ) -------------- Public Function GetThisFolder(Optional MyTime As Date) GetThisFolder = ThisWorkbook.Path End Function Public Function GetSubfolder(MyFolder As String, FolderNum As Integer, Optional MyTime As Date) Dim fs, f, f1, s, sf Set fs = CreateObject("Scripting.FileSystemObject") Set f = fs.GetFolder(MyFolder) Set sf = f.SubFolders i = 0 For Each f1 In sf i = i + 1 If i = FolderNum Then GetSubfolder = f1.Name Next End Function |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
retrieve filenames from given directory into excel
Hi
wrote in message oups.com... Extention ee what is that Estonia? Yes Regards, Giel PS. If I can do something for you? I'm in the printing business. If you have PDF material to print, I can do that for you and send it anywhere you like. thats for free of course... Thanks for offer, but hardly I'll need this :-)) Arvi Laanemets |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2003 FAILS, but Excel 2000 SUCCEEDS ??? | Excel Discussion (Misc queries) | |||
How do I retrieve changes to an unsaved excel document | Excel Discussion (Misc queries) | |||
Retrieve data from separate Excel session | Charts and Charting in Excel | |||
How do I retrieve changes to an unsaved excel document | New Users to Excel | |||
Default Save To directory doesn't work Excel 2003 | Excel Discussion (Misc queries) |