Macro to update table from another workbook

[Gregg Wolin]

I'd like to automate the transfer of data from one workbook (from a third-party) into a separate file I will use for analytics and report generation.
The file we receive each month is attached (note the name of the file - each month the last part of the name is adjusted with the new date) along with my destination file. I envision a macro that would ask the user to (i) browse for the source file then, (ii) find whatever data has been updated then, (iii) copy and paste the data to the proper in the destination file.

Thanks in advance!

 

[vletm]

Gregg Wolin
Some questions:
1) Your both files were .xlsx - is there third file, which You would do Your 'move' or how?
2) There has use some 'ActiveX'-components -- are those needed? (... I cannot use those.)
3) (i) Would user take care that he opens 'valid' source-file?
4) (ii) Are ALL data has updated or only some parts? If not ALL, then how to know which has been updated?
5) (iii) Need to know source and target. Would You mark eg with BLUE fonts from 'source' and 'target' those part which should NOW move?

 

[Gregg Wolin]

Gregg Wolin
Some questions:
1) Your both files were .xlsx - is there third file, which You would do Your 'move' or how?
2) There has use some 'ActiveX'-components -- are those needed? (... I cannot use those.)
3) (i) Would user take care that he opens 'valid' source-file?
4) (ii) Are ALL data has updated or only some parts? If not ALL, then how to know which has been updated?
5) (iii) Need to know source and target. Would You mark eg with BLUE fonts from 'source' and 'target' those part which should NOW move?

1. No. I renamed (and annotated each file for clarity).

2. What ActiveX controls?

3 - 5 . If I had my wish, the macro would (i) prompt the user to browse for the source file (ii) identify the source column from which to update [ie. the month that follows the last month where data exists in the existing Destination file] and (iii) copy, transpose and paste the source data to the row following the last populated row of the Destination file.

Other. As the Source file has trailing 12-mo data, it would be great if the macro could identify if there were any changes to the historic data and give the user the choice of updating the current month OR the entire 13-month array.

 

[vletm]

Gregg Wolin
Okay ...
1) Still both .xlsx ... naming okay ... still this would use with the third eg .xlsb-file.
2) Sorry ... not ActiveX ... I rechecked ...

3) (i) Do user take care that user open a valid file? ( =not any file! )
5) What kind of layout of rows 1 & 2 would be 2019 FEB? now [B1:M1] presents year 2018! ... is B2 eg Feb and so on?
= it would be more clear/useful if row 2 cells would be dates (not only 'months'!)
6) As well as 'Destination'-files A-column should be clear dates (= not formulas) .. eg if missing month ... hmm?
I'll try to do something ... later

 

[Gregg Wolin]

Gregg Wolin
Okay ...
1) Still both .xlsx ... naming okay ... still this would use with the third eg .xlsb-file.
2) Sorry ... not ActiveX ... I rechecked ...
View attachment 59616
3) (i) Do user take care that user open a valid file? ( =not any file! )
5) What kind of layout of rows 1 & 2 would be 2019 FEB? now [B1:M1] presents year 2018! ... is B2 eg Feb and so on?
= it would be more clear/useful if row 2 cells would be dates (not only 'months'!)
6) As well as 'Destination'-files A-column should be clear dates (= not formulas) .. eg if missing month ... hmm?
I'll try to do something ... later

1. I'm not following the need for a 3rd file. My intent is that the "Permits-Destination" file include the macro that would update its content with the new source data as it comes in (once per month). In other words, the Destination file is my database that requires monthly updating.

3. Ideally, the macro would validate that the selected source file is properly formatted. If its not, the macro can terminate and inform the user that the source isn't properly formatted.

5. I have nothing to do with the creation of the Source file (hence my desire for a macro to automate the updating of my Destination file which i currently copy, transpose and paste into manually).

6. This is one of the challenges. The Destination file is "clean" where the source has month names in one row and a single number in a merged cell for the year.

* I also just realized that that the source file has hidden columns (O:Z) that will are filled in each month of the current year. In December of year, there will be a total of 24-months of data. In January, it drops to 13, then builds from there.

 

[vletm]

Seems that Your 'this month' column won't be N-column = Jan-2019 - neither 13th month ... what would case later - eg Jan-2020?



... and You noticed something same too
3) No matter of format ... don't You need that DATA?
6) Not any challenge - 'source has dates which to move' - if no month in destination then it should do!

>> You just realized ... that You should know about SOURCE-file ... how do it will be month-by-month?

 

[Gregg Wolin]

Seems that Your 'this month' column won't be N-column = Jan-2019 - neither 13th month ... what would case later - eg Jan-2020?
View attachment 59618
View attachment 59617

Note hidden columns.

 

[vletm]

Gregg Wolin
Sample ...
Read careful ...
1) Because Your sent files could open for me - I did 'my version's of those' -- okay?
2) You should able to use Your files! (mine as for reference)
3) You have to change 'destination'-files A-column 'Month'-values as mine!
You can find needed eg formula for needed values from mine files X-column = always 1st of month!
4) USAGE ...
5) I did Do_Move-file for use ( ... because... )
6) Open it!
7) select 'gray' cell right side of SOURCE and select Your wanted 'SOURCE-file'
8) do same with DESTINATION
9) Select [ MOVE ]-cells (it should be GREEN after both files are selected
10) ... wait short time --- if value has changed in DESTINATION file, You'll see comment in that sheet
! NOT FULLY TESTED !

 

[Gregg Wolin]

Gregg Wolin
Sample ...
Read careful ...
1) Because Your sent files could open for me - I did 'my version's of those' -- okay?
2) You should able to use Your files! (mine as for reference)
3) You have to change 'destination'-files A-column 'Month'-values as mine!
You can find needed eg formula for needed values from mine files X-column = always 1st of month!
4) USAGE ...
5) I did Do_Move-file for use ( ... because... )
6) Open it!
7) select 'gray' cell right side of SOURCE and select Your wanted 'SOURCE-file'
8) do same with DESTINATION
9) Select [ MOVE ]-cells (it should be GREEN after both files are selected
10) ... wait short time --- if value has changed in DESTINATION file, You'll see comment in that sheet
! NOT FULLY TESTED !

Thanks! This is very interesting (and substantially over my head).
I really need the routine to be incorporated INSIDE the Destination book (see attached).

The workflow I'd like when I receive a new Source file is (i) open the Destination wb, (ii) click the Source cell, (iii) navigate to the the Source, (iv) click Update. I tested a few scenarios and it updates multiple months at a time (nice!), however it doesn't update column X. What's the purpose of that column?

 

[vletm]

Gregg Wolin
Why REALLY need INSIDE that file?
ESPECIALLY, because it is NOT POSSIBLE!
It's is .xlsx -file = NO MACROS!
As well as Scottsdale is not in Alaska!

Now, with that Do_Move.xlsb can do it!

Case: X-column
Did You read:
3) You have to change 'destination'-files A-column 'Month'-values as mine!
You can find needed eg formula for needed values from mine files X-column = always 1st of month!
>> Did You do that with YOUR file? ... no!
As I tried to write ... Your files A-column dates were 'mess' = no logic!
My sample solution NEEDS that those are always 1st of month!
Later, NO MATTER, my code will take care about NEW MONTHS.
... and who knows, how that 'source'-file change few month --- later!
There are RULES, which should match ... that that code could work!
That's why I started with phrase 'Read careful...'
as well as
2) You should able to use Your files! (mine as for reference)