organizing scattered data in a spreadsheet with no column headings/ Need to organize/transfer it into one table?

[edmonton]

Hello everyone,

Here is the case:-

we use Ms Excel to send forms of "quotes" to our customers, each form has data spread throughout, it is not based on columns, instead the fields are side by side like in :

Name : John ==> "This would be in one cell"
Address : 234 Ozr RD ==> "This would be in one cell"



So, there are 1000+ xls files that contains data for previous Quotes, I would love to transfer everything into a database in Ms Access, "I'm an advanced user of Ms Excel + Access, knows some VBA".

Is this doable? given the fact that the forms are consistent. i. e. Cell locations, information fields..etc. in every xls file.

Please let me know if you have any questions...
Thank you

 

[Andrew Fergus]

Hi and welcome to MrExcel!

If, as you say, the cell locations and the purposes for which those cells are used are consistent then yes you can do this.

In such a scenario I recommend you firstly use VBA with FSO file handling to extract the data from all of the files (assuming they are in one location or even subdirectories will be ok) and copy that data to a new master sheet that is laid out in a 2D format (records by row, data organised into columns). Then you can use any number of methods to get the data from that format into Access.

Andrew

 

[edmonton]

Thank you very much for your reply.

I've already started what you are suggesting, the files are like more than a thousand, currently i'm opening each file, run the macro then close it, can I do this without opening the files (it is very time consuming)


Thank you,

 

[Andrew Fergus]

Hello

You can process all of the files using a loop in VBA. The FSO (file system object) method is a really easy technique to use. There are a number of tutorials online.

There is some sample code in post 5 in this thread:
http://www.mrexcel.com/forum/showthread.php?t=451389

Notice also post 8 tells you how to set a reference to Microsoft Scripting Runtime.

Per the code in post 5, this part of the code:

Debug.Print File.Path
'Open File.Path workbook here and call your code on it

Needs to be replaced with your own code. And the initial directory also needs to be set to what you want instead of using "C:\Orders"

If you don't have a lot of experience with VBA I recommend you take your time rather than just copying the code and hoping it does what you want! Be sure to backup your work before using other peoples code.

This code will start with a given folder, find all of the spreadsheets within that folder and all subfolders, and then do *something* - at the moment it shows the name in the debug window - but per my post above you should insert your code at that point.

Andrew

 

[Andrew Fergus]

Ok. That was a really half-hearted attempt on my part that would have left you floundering. Below is some sample code that I have cobbled together with comments in the parts you need to change, which should give you a better start.

Copy this macro into your master workbook (I'm assuming you are aggregating the data into a single spreadsheet before transferring to Access) and amend as required.

What this does:

~ It uses fso (file scripting object) to loop through all files (and all subfolders too if you want)
~ It opens each target spreadsheet (.xls documents only)
~ It copies data from cell A1 into column B, and the target path and name into column A
~ advances the output row counter
~ closes the target spreadsheet
~ repeats itself until there is nothing left to process

If you already have your own code then you will want to insert that below the part that says : "Set wb = Workbooks.Open(File.Path)"

Andrew

'Requires reference to Microsoft Scripting Runtime by doing this:
'1) Open the visual basic editor
'2) Select menu option Tools > References
'3) Find 'Microsoft Scripting Runtime' and tick it

Option Explicit

Public Sub Process_Files()

Application.ScreenUpdating = False

Dim Fso As Scripting.FileSystemObject

Set Fso = New Scripting.FileSystemObject
'Set the directory you wish to search below:
Process_XLS_Files Fso, "C:\Documents and Settings\Andrew\My Documents"

Set Fso = Nothing

Application.ScreenUpdating = True

MsgBox "Finished Processing Files", vbInformation, "Done!"

End Sub


Private Sub Process_XLS_Files(Fso As Scripting.FileSystemObject, folderPath As String)
   
Dim Folder As Scripting.Folder, Subfolder As Scripting.Folder, File As Scripting.File
Dim wb As Workbook, OutputRow As Long
       
Set Folder = Fso.GetFolder(folderPath)
'Set the output row to the first row that you want this macro to write the results to
OutputRow = 2

For Each File In Folder.Files
    If InStr(File.Name, ".xls") And ThisWorkbook.Name <> File.Name Then
        Set wb = Workbooks.Open(File.Path)
        'Record which file the data came from in column A:
        ThisWorkbook.Sheets("Sheet1").Range("A" & OutputRow).Value = File.Path
        'Example of recording the value from cell A1 into column B
        ThisWorkbook.Sheets("Sheet1").Range("B" & OutputRow).Value = _
            wb.Sheets(1).Range("A1").Value
        'NB : note it records the data onto 'Sheet1' of the workbook that contains the macro
        'NB : it also assumes it is only looking at the first sheet of target spreadsheet
        '
        'Add additional lines for other cells you want to copy....
        '
        '
        wb.Close False
        OutputRow = OutputRow + 1
    End If
Next File

'************************************************************************************
'Delete this section if you do not want to process the spreadsheets in any subfolders
For Each Subfolder In Folder.subfolders
    For Each File In Subfolder.Files
        If InStr(File.Name, ".xls") And ThisWorkbook.Name <> File.Name Then
            Set wb = Workbooks.Open(File.Path)
            '*****************************************************
            'Whatever you did to the code above, repeat it below:
            ThisWorkbook.Sheets("Sheet1").Range("A" & OutputRow).Value = File.Path
            ThisWorkbook.Sheets("Sheet1").Range("B" & OutputRow).Value = _
                wb.Sheets(1).Range("A1").Value
            '*****************************************************
            wb.Close False
            OutputRow = OutputRow + 1
        End If
        
    Next File
Next Subfolder
'************************************************************************************

End Sub