Read data from closed worksheets

Lynton

New Member
Joined
Mar 13, 2011
Messages
19
I use excel to produce work invoices. I have 1000ish invoices in a folder called "invoices" the individual invoices are named inv100001 to inv101000, each invoice has the customers name and address in cells A13, A14, A15, A16, A17, A18,.
I would like to recover and store these name and address in another spreadsheet (called Address) obviously I don't want to alter any details on the already saved invoices.
Ideally I would like to automatically open each invoice in turn .
copy A13 in the inv100001 to A1 in the new address spreadsheet
copy A14 in the inv100001 to B1
copy A15 in the inv100001 to C1
copy A16 in the inv100001 to D1
copy A17 in the inv100001 to E1
copy A18 in the inv100001 to F1
then close the invoice, open the next invoice
and copy those details to row B.
At the finish I would like all the customers details in rows in the address spreadsheet.

any help greatly appreciated
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
13,908
Office Version
  1. 2007
Platform
  1. Windows
That is different, it could be like this:

VBA Code:
Sub Read_data_from_closed_worksheets_2()
  Dim sPath As String, sFile As String, sh As Worksheet
  Dim lr As Long
  
  Application.ScreenUpdating = False
  Set sh = ThisWorkbook.Sheets("Address")
  sPath = "C:\invoices\"
  sPath = "C:\trabajo\books\"
  sFile = Dir(sPath & "*.xls*")
  lr = sh.Cells.Find("*", , xlValues, xlPart, xlByRows, xlPrevious).Row
  
  Do While sFile <> ""
    lr = lr + 1
    With GetObject(sPath & sFile)
      sh.Range("A" & lr).Value = .Sheets(1).[E6].Value
      sh.Range("B" & lr).Value = .Sheets(1).[E4].Value
      sh.Range("C" & lr).Value = .Sheets(1).[A13].Value
      '
      'and so on...
      '
      .Close 0
    End With
    sFile = Dir()
  Loop
  Application.ScreenUpdating = True
End Sub
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

Forum statistics

Threads
1,147,498
Messages
5,741,504
Members
423,663
Latest member
kaveh87rsh

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top