A little project I need some help with.

[durexlw]

There's only 1 Sheet per excel file that needs to be touched. So basically it's going to be a macro that loops through a directory of excel files one by one and plucks the same cells of information out of each of them, and puts them into a table. And yes I only need certain cells of information from each sheet, because there is a lot of information on each quote sheet and I want to be able to quickly locate the specific pieces that i'm looking for (which are the ones I will be importing).

Any ideas?

If for each XLS file, you need to extract the same data, then the answer to your question was pretty much already written in front of your nose.

In fact there are many ways... an easy one: build your database first, forget about visual studio, get yourself a beginners course on access (lynda.com will bring you a far way), learn about forms, reports and data normalization as a few suggested here before me.
Once you have your database, you can do several things:

Considering you VBA skills:
--> The hard way: the way you're trying now: use VBA to fill your database
--> The easy way: use a macro editor to get your data inside access (look for 'Macro Scheduler' or 'Automation Anywhere'), they often have user-friendly ways to manipulate data in excel and you can bring them to anywhere else if you like, including access.

Either way it boils down to the same thing:
For each XLS there has to be a logic in to what fields you want to extract and getting your data is useless until you have an actual database.

Using VBA, you'd build a list of fields you want to extract, open an excel object, select only those rows of your interest and insert them into your database... the same goes for the macro way, only the syntax will be a bit more easy... anyway, that's all pretty trivial... make your database first

 

[Rabbie]

Hi

The attached file is a piece of VBA that I use to select and reformat data in an Excell spread sheet. It may give you some ideas on how to do this.

 

[Patty28715]

I found the script where it reads one cell in every excel sheet under in a folder and returns the file name if that cell say "test". This might help with what you need. If you can change this script to read all the files and certain cells then store into anthor excel sheet with the file name. You can then create an access table to link the original file with the info and then append you tables with the data in the excel files.
http://www.microsoft.com/technet/scriptcenter/resources/qanda/feb08/hey0226.mspx

 

[Patty28715]

Viewing the scripts from the website I posted earlier there a many options to read a cell in excel and copy it to another excel sheet.

Excel will hold over 65000 rows, so if you can write a script to read all the excel files and copy the information into one excel file along with the file name it came from then you can import this data into access and create your form to view it with.

I would create a hyperlink field for the location of the file names so if you need to see the original file then you can click on it and it would open up the excel sheet for viewing/printing if needed.

 

[NigelShaw]

Excel Code

Hi,

you could try this. it might need to be manipulated slightly and you will need to save the code in a workbook that resides in the same folder as ALL of the other workbooks in question-

Sub ListFileNames()
Dim fsoObj As New FileSystemObject
Dim fsoFolder As Folder
Dim fsoFile As File
Dim LngRow As Long
Dim StrPath As String

StrPath = ThisWorkbook.path ' set current path of workbook
Set fsoFolder = fsoObj.GetFolder(StrPath)

lngRow = 1

for Each fsoFile In fsoFolder.Files
ActiveSheet.Cells(lngRow, 1) = fsoFile.Name
lngRow = lngRow + 1
Next fsoFile

Set fsoFile = Nothing
Set fsoObj = Nothing

End Sub

then, once your active open sheet has a list of files, add a formula to the cells next to each folder path to retrieve your data-
=[C/Data/XlsFolder/WorkbookName]![SheetName]![Cell]

or add into the above code to expand and retrieve the data while the routine is running.

(ps. this is not my code, just something ive had for a while )


once you have your data, you can then get Access to import as a table.


NS