Gathering data from multiple worksheets for one new file

[DanielleB573]

Hello everyone!

I'm new to this forum and this is my first post so please be kind.

I am a travel agent and we do all our invoices in excel. We save each invoice as its own workbook. (I know that's probably not the most efficient way but I didn't make up the rules it was just like that when I got here) We use the same template for all our bookings, so all the info will appear in the same cells.

I'm trying to create a client database with name, email, phone number etc...

Is there a way to extract all that data using Access or Excel or what ever else might work? I really don't want to go through 500+ workbook / invoices to do this.

Just trying to make life a bit easier because my boss is a d!ck.

Thanks in advance

Danielle

 

[bsquad]

What are your ranges, and are there blank cells in between?

 

[DanielleB573]

Hi!

I think there would be blank cells in between. Basically, every invoice we do is saved as it's own work book. We just use a template that was created in excel. So the customers email is always in C7, the phone number is always in C5, the name always appears in B9 ect... if i knew how or could up load an image of the invoice it might help. There is nothing fancy about it. The most is does is calculate how much the customer paid and what they still owe. That data doesn't matter to me for this particular purpose.
I've been muddling around on the internet and i think this can be done with access but I don't know how to do the query or if I'm really even on the right path.
Ultimately i just want to create a new client database with name, phone, and email. I have all the data but its all in excel workbooks.
Is that clear as mud? I wish I knew more so i could explain myself better.

 

[bsquad]

if its just three things you need aggregated from your 500 workbooks -
With a new Excel Workbook, put in Headers in: >> in A1 - Name, B1 - Phone, C1 - Email (or whatever you like, there just needs to be something to occupy the first row.

Then you can use this code to aggregate. When you initiate the code, it will bring up a windows explorer directory, this is where you can multi select all your files, once you hit 'ok' it will start to aggregate into one sheet. You could eventually import this into Access if you want, but if its just demographic information you wanting to keep; I would keep it in Excel; Access is dumb.
Let me know if you need help running the code and implementing it>> I wasn't sure of your skill level

Sub Master_Workbooks()
    Dim Imports As Worksheet
    Dim MasterSheet As Worksheet
    Dim OpenImportWorkbook As Variant
    Dim ImportWorkbook As Workbook
    Dim MasterWorkbook As Workbook
        Set MasterWorkbook = ThisWorkbook
    Dim TRIndex As Long
        TRIndex = ThisWorkbook.Worksheets(1).Cells(Rows.Count, 1).End(xlUp).Row + 1


        Application.DisplayAlerts = False
        Application.AskToUpdateLinks = False
        Application.ScreenUpdating = False
        ChDir ActiveWorkbook.Path
        OpenImportWorkbook = Application.GetOpenFilename(filefilter:="Excel Workbooks(*.xlsx; *xlsm,*.xlsx;*xlsm", _
                            Title:="Import File Select", MultiSelect:=True)
                            On Error Resume Next
                            Set ImportWorkbook = Workbooks.Open(OpenImportWorkbook(i))
            For i = LBound(OpenImportWorkbook) To UBound(OpenImportWorkbook)
                Set ImportWorkbook = Workbooks.Open(Filename:=OpenImportWorkbook(i), ReadOnly:=True)
                DoEvents
                ThisWorkbook.Application.StatusBar = "Workbook: " & i & " of " & UBound(OpenImportWorkbook)
                    With MasterWorkbook.Worksheets(1)
                        .Cells(TRIndex, 1).Value = ImportWorkbook.Worksheets(1).Cells(7, 3).Value
                        .Cells(TRIndex, 2).Value = ImportWorkbook.Worksheets(1).Cells(5, 3).Value
                        .Cells(TRIndex, 3).Value = ImportWorkbook.Worksheets(1).Cells(9, 2).Value
                    End With
                TRIndex = TRIndex + 1
                ImportWorkbook.Close savechanges:=False
            Next i
            Application.DisplayAlerts = True
            Application.AskToUpdateLinks = True
            Application.ScreenUpdating = True


End Sub

 

[DanielleB573]

This is great! I'm going to give it a try.

Thanks so much!

 

[DanielleB573]

It worked! Thank you so much. You saved me so much time!!!!

 

[bsquad]

np, thank you for the feedback, glad to have helped!