How to pull data from a closed excel workbook and log into active workbook

[sdhutty]

Hello,

Before reading this bear in mind I'm an vba rookie!

I currently have an excel workbook called 'MasterRegister', within this workbook is a sheet called 'Register'.

I have another excel workbook called 'RO Status Log - Practice Copy', within this workbook is a sheet called 'R&O Closed'. This sheet is regularly updated with information in the format below: (NOTE: When it updates it adds a row to the top not the bottom)

Column A Column B Column C Column D Column E

R&O Number	Document Number	Document Type	Unit Affected	Issue/Revision
15610	J466	REPORT	ENGINE	1
17483	JRRU	LETTER	F18	8
78172	HGU7	SERVICE	2891	10

<tbody>
</tbody>

As this is updated with new data, I want the excel workbook 'MasterRegister' to also record this same new data in its worksheet "Register" WITHOUT needing to open the 'RO Status Log' workbook.

I want it to record the information in the columns stated below:

R&O Number: Column A
Document Number: Column B
Document Type: Column B
Unit Affected: Column L
Issue/Revision: Column G

So in procedure: I will open the 'MasterRegister' workbook & from a command button on the 'Register' sheet - it will show a message box stating:

"5 new entries have been made in RO Status Log - Entries now recorded in the sheet". If there isn't any new entries it will say "No new entries".

I have attempted to do this - but by pressing the command button it opens the RO Status log workbook which I obviously do not want and attempts to copy the whole column which I do not want also.

Sub AutoCopyVersion()
Dim pasteTo As Range
Dim countRows, i As Long
countRows = Application.CountA(Range("A:A"))
Workbooks.Open Filename:="C:\Users\SAN1011\Documents\RO Status Log - Practice Copy.xlsm"
i = Application.CountA(ActiveWorkbook.Sheets("R&O Closed").Range("A:A"))
If i = countRows Then Exit Sub
ActiveWorkbook.Sheets("R&O Closed").Range("A" & countRows + 1 & ":C" & i).Select
Selection.Copy
Workbooks("RO Status Log - Practice Copy.xlsm").Close
Set pasteTo = Sheets("Register").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
ThisWorkbook.ActiveSheet.Paste Destination:=pasteTo
Application.CutCopyMode = False
End Sub

 

[sdhutty]

Thanks for getting back to me. I will try and explain it clearer this time.

What I'm trying to do:

The RO Status Log workbook which you've programmed to collect data from, is regularly updated with new rows every now and then. The current code which you have provided, you have stated: "I can't see why you're now counting 4 specific cells A4:D4, as this should always return the value 4". I have done this because when it was range "A:A" and when I ran the code - it would import the whole of column A from the workbook - which I obviously do not want.

I changed it to A4:D4 because this way it imports the row and not the column. HOWEVER, A4:D4 will always extract A4:D4 no matter how many times I run the code.

The main coding I want is for it to extract the latest row(s) of data that has been created in that worksheet from the last saved, NOT the entire column of data.

I've also run your new code and get 'run time error (13) - type mismatch'

 

[baitmaster]

Not sure why it's doing this. To break down what I'm trying to do

Step 1 - compare 2 worksheets to decide the difference in row numbers. This assumes both are structured consistently e.g. 1 header row, no information in column A other than that header row, and the subsequent data. So if Source contains 20 rows and This contains 18, it will identify iNewRecords = 2

Step 2 - identify the range in question. So in the example [strAddress = "A" & 2 & ":E" & iNewRecords + 1] will return "A2:E3". Question: what address is being returned by the "Do you wish to import?" pop-up message?

Step 3 - loop through each column of the range - in this case Range("A2:E3") - and copy each column to a new location

Without seeing your files, I can only guess what is going wrong. So you need to have a go at doing some debugging. This will also help to find where the type mismatch is happening and why. FYI type mismatch is where you're supposed to be using one type of data e.g. an integer but are instead trying to use something else e.g. a piece of text. It can also happen if a calculated value in a cell contains an error

Debugging
This is a key skill in code writing, you'll benefit from learning about this sooner rather than later. It's often overlooked but SO important.

It's really useful to have 2 screens set up so you can see Excel in one window and VBA in another. You can also view side by side on one screen but not as easily. You'll ideally need to see both for this exercise

Rather than run your code (F5), I want you to step through it (F8). I also want you to have the immediate window and the locals window visible - both can be displayed from the View menu of the VB Editor. This allows you to see what each line of code is doing and to spot errors

You can add break points by clicking in the grey border to the left of your code. A break point means your code will pause at that line when running, allowing you to run good code and then step through an area of known problems

As you step through, you'll see all your variables listed in the locals window, and their current value. As you progress, do they contain what you expect them to contain? If something doesn't change as expected, which line of code is not performing?

You can change any variable at any time by writing single lines of code in the immediate window. So if strAddress was created incorrectly, you can manually change it by typing straddress = "A2:E3" and hitting enter, and seeing if the code then works properly

You can use debug.print [some text] to pass anything you want to the immediate window when code is running

On what line does your type mismatch occur? What variable is affected, and what value are you trying to pass into it?

 

[sdhutty]

This is the line which it occurs:

proceed = MsgBox(iNewRecords & " new records found at range " & strAddress & ". Do you wish to import?" & vbCr & Join(arrResults, "; "), vbQuestion + vbYesNo)

 

[baitmaster]

well the only thing that changed in that row is that I added an array of results. [Join] should concatenate the array into a single text string using ";" as a delimiter. Following the advice above, what does the Locals window tell you about the contents of arrResults at the time the code breaks?

It may be worth adding the following line above it, to try and pass the results directly to the Immediate window:
debug.print Join(arrResults, "; ")

If that doesn't work we'll set up a loop to pass the array values one at a time and check them individually

 

[sdhutty]

It states that the value is Empty

& No that doesn't work unfortunately.

 

[baitmaster]

try:

        For Each cl In Range(strAddress).Columns(1)[COLOR=#FF0000].cells[/COLOR]

 

[sdhutty]

Hi,

Yes that imports the data. Although I do not see a difference in the method

Few things which it doesn't still do:

1) Doesn't import values only.
2) It still imports the whole columns of data - how does it just import the latest row of data reading from row 4.
3) The immediate window states "R&O Folder Link" with several semi colons - how does it show the R&O number from column A instead

 

[baitmaster]

1) I used a piece of code from earlier in the thread but forgot I'd said to amend it to xlPasteValues instead of xlPasteAll (see post 8)
2) I thought you wanted to import the latest data up to 4 records. So in my assumption it would be rows 2 to (2,3,4 or 5). Do you only want 1 row at a time? Why have you said row 4 - which assumption have I got wrong?
3) the immediate window is showing the contents of the array, which I've set to display multiple references from column A of rows 2 to (2-5), with a semi-colon between each item. If there are a few semi-colons with nothing between then there are blanks making their way into the array - which suggests some form of misalignment, or row of info after your data, something like that

 

[sdhutty]

No not upto 4 records.

The data that we are importing from the columns is correct. However, the latest data isn't imported up to 4 records - the amount of records can vary.

I have said row 4 because in the RO Status Log where we are receiving the data - that is where the data updates & begins - the new data of rows are entered from row 4.

So are Monday for example there could be 2 rows of new data from row 4 (so it would be row 4 and row 5 which is new)

Tuesday there could be 5 new rows of data from row 4 (so it would be row 4, 5, 6, 7, 8 which is new) and so forth.

Thus when this code runs it will search for the newest rows according to last saved on that spreadsheet and import those new rows - so it will not be just one row at a time - it can be two rows at once, it can be five rows at once etc.

 

[baitmaster]

OK I think I understand

I've already mentioned that I'm not going to start getting into timestamps etc., that's for another thread. I don't think we need them yet though, this code probably works but just isn't configured correctly. See post 12 for what I've been assuming in terms of alignment

So in step 1 we need to take account of more row headers in the source file, as we are comparing the total rows used, and in the destination I'm still assuming 1 header row. So if the difference in header rows is x, where x = header rows in source - header rows in destination, then

' calculate new entries
iNewRecords = countRowsSource - countRowsThis - x

Then in step 2 [strAddress = "A" & 2 & ":E" & iNewRecords + 1] needs to be amended as we don't want to start at row 2, we'll need to change this to [strAddress = "A" & x+1 & ":E" & iNewRecords + x]
Note, I'm not actually using x as a code item yet, you'll need to calculate it. This is just explaining what I'm doing