Import Data from closed Workbook to closed Workbook

User avatar
Stefan_Sand
4StarLounger
Posts: 412
Joined: 29 Mar 2010, 11:50
Location: Vienna, Austria

Import Data from closed Workbook to closed Workbook

Post by Stefan_Sand »

Hello,
i have to export data from MS Azure DevOps and i want to make the way more practicable for my colleagues and me.

Basically i can export the data via queries into csv files and then they are put together in a base workbook (Project List.xlsx)and defined worksheets - (User Stories, Features, Epics).

Then, my colleagues and i copy the data manually into the Target Workbook (TEST ALL PROJ DRAFT.xlsx) - Worksheets(DOp Epics; DOp Features, DOp User Stories).

What i want to do is, copying the data from the Base workbook/worksheets into the Target workbook/worksheets.

My idea was, to have a import the data via a so called parameter import devops file, where the macro copies the defined data from the baseworkbook/worksheets into the target workbook, worksheets.

Both workbooks don´t lie in the same folder, so it would be nice to set their path via file picker dialogue in the parameter sheet, where both information provides the macro procedure with the needed information, as you can see, when you open the file.

From cells c3 - path of base workbook
c5 - c7 , base worksheets

to cells c9 - path target workbook
c11 - 13 target worksheets

would be my idea, has anyone a procedure to do this?

any ideas would be highly appreciated.
Stef
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Import Data from closed Workbook to closed Workbook

Post by HansV »

Should the data in TEST ALL PROJ DRAFT.xlsx be cleared before importing the data from Project List.xlsx, or should the data be imported below the existing data rows?
Best wishes,
Hans

User avatar
Stefan_Sand
4StarLounger
Posts: 412
Joined: 29 Mar 2010, 11:50
Location: Vienna, Austria

Re: Import Data from closed Workbook to closed Workbook

Post by Stefan_Sand »

ups , yes unfortunately, the data should be cleared.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Import Data from closed Workbook to closed Workbook

Post by HansV »

I assume that C5:C7 and C10:C12 on the Parameter sheet should be ordered the same way. You now have DOp Epics as first but Epics as last. Or is that intentional?
Best wishes,
Hans

User avatar
Stefan_Sand
4StarLounger
Posts: 412
Joined: 29 Mar 2010, 11:50
Location: Vienna, Austria

Re: Import Data from closed Workbook to closed Workbook

Post by Stefan_Sand »

Sorry, not it is not, i followed how some colleagues ordered it in the workbook.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Import Data from closed Workbook to closed Workbook

Post by HansV »

See if you can use the code in the attached version as starting point.

PARAMETER IMPORT DEVOPS.xlsm
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans

User avatar
Stefan_Sand
4StarLounger
Posts: 412
Joined: 29 Mar 2010, 11:50
Location: Vienna, Austria

Re: Import Data from closed Workbook to closed Workbook

Post by Stefan_Sand »

thank you very much. When i tested it i came to one error in my thinking. With the folder picking dialogue one has to be careful, it only sets the folder(s) of the files, while the files and the worksheets are hardcoded in the cells! So we / i have to change this by hand, not to see, nothing happens if we want to do this with the folderpicking sequence. Thanks,
Stef

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Import Data from closed Workbook to closed Workbook

Post by HansV »

And what is your question?
Best wishes,
Hans

User avatar
Stefan_Sand
4StarLounger
Posts: 412
Joined: 29 Mar 2010, 11:50
Location: Vienna, Austria

Re: Import Data from closed Workbook to closed Workbook

Post by Stefan_Sand »

that was no question, Hans. If someone here wants to use the code... ;)
best regards and many thanks to You in the Netherlands.
Stef

User avatar
Stefan_Sand
4StarLounger
Posts: 412
Joined: 29 Mar 2010, 11:50
Location: Vienna, Austria

Re: Import Data from closed Workbook to closed Workbook

Post by Stefan_Sand »

Hello Hans, after working with the code, one last question i have:
in the target worksheet Epics, there are some formulas in columns M:O,how do i have to adopt the lines in your code that in this worksheet, the code does not clear all the conent from columns M:O as well?
Sorry, got no clue.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Import Data from closed Workbook to closed Workbook

Post by HansV »

The code clears B3:Z1000 on the target sheet. It leaves the headers in M2:O2 alone.
I don't see how it could leave formulas, unless they are below row 1000. If that is the case, simply change Z1000 to Z1000000 or so.
Best wishes,
Hans

User avatar
Stefan_Sand
4StarLounger
Posts: 412
Joined: 29 Mar 2010, 11:50
Location: Vienna, Austria

Re: Import Data from closed Workbook to closed Workbook

Post by Stefan_Sand »

No there is only the area from columns B:L in epics, delimited with the row, where i have Summary and a Sum formula.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Import Data from closed Workbook to closed Workbook

Post by HansV »

Your sample workbook had headers in M2:O2. You can remove these manually if you wish, you'd have to do it only once.
Best wishes,
Hans