Hi All!
I have an excel spreadsheet with kind of folder tree structure in it and what I need to do is to have it populated with names of the files from corresponding folders on a regular basis.
I have lots of different directories and it's quite time consuming so I was wondering if it's possible to create a macro that will extract all the filenames from certain folders after mapping folder names from the spreadsheet with actual folders and sub-folders in my directory. Ideally I will have the macro being able to only append the existing list of files in the spreadsheet and do that in different color (i.e red)
Is this possible and can anybody help me create the core of this macro?
Thanks for your good will!
Paul
9 6957
Anything is possible Paul, maybe difficult but possible... Do stay tuned! I will send over to Access for a closer look:-)
Hope you get this one pinned down. I would post a bit of anything if you have it just to give folks out there some idea what you're up against.
Good luck!
@Dököll
Just check in, will return later.
I've found the Dir Function useful. If you have questions about that, I will certainly do my best to help you learn.
@mdensana
Hello mdensana, I'm a little confused as to what exactly you are requesting, and as to whether you wish to implement the functionality from within Access or Excel. In any event, I see 2 generic approaches to your problem. - Link the Excel Spreadsheet to the Access Database, if you are going that route. I'm assuming that the Spreadsheet has a Column Header named Folder or something similar. Loop through every value (Folder Name) in that Column and pass it to a Function that will evaluate the Folder and process File Names contained within it if appropriate.
- The 2nd approach would be to parallel the functionality listed above, but use Automation Code to Open the Excel Spreadsheet then process the values in the Column.
- I'm still confused as to what you wish to do with the File Names for each Folder.
- I've written a Generic Function that should be a good starting point for you. Simply pass to this Function the Name of a Folder, and it will then return (Debug.Print) all Files contained within the Folder, or another appropriate Value.
- Let us know how this works out.
- Public Function fListFiles(strFolder As String) As String
-
On Error GoTo Err_fListFiles
-
Dim MyName As Variant
-
-
strFolder = Trim(strFolder)
-
-
'Was a Folder passed to the Function?
-
If strFolder = "" Then
-
fListFiles = "No Folder specified"
-
Exit Function
-
End If
-
-
'We need a Trailing Backslash, if not present
-
If Right(strFolder, 1) <> "\" Then
-
strFolder = strFolder & "\"
-
End If
-
-
'Does the Folder even exist?
-
If Dir(strFolder, vbDirectory) = "" Then
-
fListFiles = "INVALID Folder"
-
Exit Function
-
End If
-
-
'*********************************************************************
-
'If we get here, we can retrieve all Files within the specified Folder
-
MyName = Dir(strFolder) ' Retrieve the first entry.
-
-
Do While MyName <> "" 'Start the loop.
-
'Not really sure what you want to do here!
-
Debug.Print MyName
-
MyName = Dir 'Get next entry.
-
Loop
-
'*********************************************************************
-
-
Exit_Err_fListFiles:
-
Exit Function
-
-
Err_fListFiles:
-
fListFiles = "ERROR in processing " & strFolder & "!"
-
Resume Exit_Err_fListFiles
-
End Function
NeoPa 32,556
Expert Mod 16PB
Unless I'm misreading the history here, the only reason this Excel question is in this forum is that we are the closest match to the question.
This is an Excel question unrelated to Access, but we are the best people to help.
Right. That sorted, I think Chip has the right idea here (and probably ADezii too as far as the specifics of the code goes). However, a clearer representation of exactly what is required would be beneficial to all. Dir() can certainly help with this. Exactly what you require is essential before we can guide you further with any assurance.
@NeoPa
Did I miss another boat?
NeoPa 32,556
Expert Mod 16PB
No worries. Your code is still the best suggestion so far ;)
I think we need a better explanation of the question before going any further though, as there is always the danger of wasting time going off in the wrong direction, which just confuses everything for later on.
dear,
This macro will add the files to the sheet.
In VBA you have to add the Reference = "Microsoft Scripting Runtime" !!
Select the cell with the path and run the macro.=> - Sub Macro_GetFiles()
-
'
-
' Macro recorded 21/11/2009 by Guido Geurs
-
'
-
'
-
Dim FOLDERNAME As String
-
Dim FSO As New Scripting.filesystemobject
-
Dim START_FOLDER As Scripting.Folder
-
Dim PATH_FILE As Scripting.File
-
FOLDERNAME = ActiveCell.Value
-
ActiveCell.Offset(1, 1).Activate
-
On Error GoTo Error_FileName
-
Set START_FOLDER = FSO.GetFolder(FOLDERNAME)
-
For Each PATH_FILE In START_FOLDER.Files
-
Selection.EntireRow.Insert
-
ActiveCell.Value = Mid(PATH_FILE, InStrRev(PATH_FILE, "\") + 1)
-
ActiveCell.Offset(1, 0).Activate
-
Next
-
Exit Sub
-
Error_FileName:
-
MsgBox ("Select a Drive or Folder")
-
End Sub
br,
Sign in to post your reply or Sign up for a free account.
Similar topics
by: jimserac |
last post by:
I had previously posted this in an Access forum
with negative results so will try here.
Although this question specifies an Access database,
I also wish to accomplish this with a large MS SQL...
|
by: jimserac |
last post by:
The following SQL statement, used in VBScript,
will COPY a table from Excel to an Access mdb.
SQL = "SELECT * INTO C1R0" & _
" FROM IN ''" & _
" 'Excel...
|
by: wildbill |
last post by:
I have an Excel spreadsheet with 1000+ rows that I need to import into
an Access 2002 db once a month or so. I then need to use that
information to update any existing records(multiple fields may...
|
by: Chris Fink |
last post by:
Any idea why the database table is not being updated with the contents of the excel file loaded into the dataset? Any help is appreciated!
// load database table, shell only
string cnString =...
|
by: berndh |
last post by:
Hi,
I have a need to update all price fields in an SQL database. The new prices are in an excel spreadsheet (c:\db\update.xls).
Unfortunately the structure of the Excel file is not the same as...
|
by: roger.rigsby |
last post by:
Ok I know how to get the data I want from mysql but I need to actually
do something with this data and then send it back to mySQL.
Company heads love the spreadsheet view so this is serious...
|
by: semijoyful |
last post by:
OS: Win XP SP2
Access version: 2003
Excel version: 2003
I am new at this, as I am sure you have gathered from this post title:)
I am working on a form where users can input data in Access and...
|
by: Master Ken |
last post by:
Hi All,
I'm very new to C# and ASP and I am asking for some help as I don't really know where to start on this project.
Some background info first
I run a report each week which outputs data...
|
by: lavsaxena |
last post by:
Can we edit a worksheet of excel through spreadsheet::writeExcel.
Because when I am using
my $workbook = Spreadsheet::WriteExcel->new('D:\test_result.xls');
this statement is creating the...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
| |