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
Import Data from closed Workbook to closed Workbook
-
- 4StarLounger
- Posts: 412
- Joined: 29 Mar 2010, 11:50
- Location: Vienna, Austria
Import Data from closed Workbook to closed Workbook
You do not have the required permissions to view the files attached to this post.
-
- 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
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
Hans
-
- 4StarLounger
- Posts: 412
- Joined: 29 Mar 2010, 11:50
- Location: Vienna, Austria
Re: Import Data from closed Workbook to closed Workbook
ups , yes unfortunately, the data should be cleared.
-
- 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
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
Hans
-
- 4StarLounger
- Posts: 412
- Joined: 29 Mar 2010, 11:50
- Location: Vienna, Austria
Re: Import Data from closed Workbook to closed Workbook
Sorry, not it is not, i followed how some colleagues ordered it in the workbook.
-
- 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
See if you can use the code in the attached version as starting point.
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- 4StarLounger
- Posts: 412
- Joined: 29 Mar 2010, 11:50
- Location: Vienna, Austria
Re: Import Data from closed Workbook to closed Workbook
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
Stef
-
- 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
And what is your question?
Best wishes,
Hans
Hans
-
- 4StarLounger
- Posts: 412
- Joined: 29 Mar 2010, 11:50
- Location: Vienna, Austria
Re: Import Data from closed Workbook to closed Workbook
that was no question, Hans. If someone here wants to use the code... ;)
best regards and many thanks to You in the Netherlands.
Stef
best regards and many thanks to You in the Netherlands.
Stef
-
- 4StarLounger
- Posts: 412
- Joined: 29 Mar 2010, 11:50
- Location: Vienna, Austria
Re: Import Data from closed Workbook to closed Workbook
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.
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.
-
- 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
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.
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
Hans
-
- 4StarLounger
- Posts: 412
- Joined: 29 Mar 2010, 11:50
- Location: Vienna, Austria
Re: Import Data from closed Workbook to closed Workbook
No there is only the area from columns B:L in epics, delimited with the row, where i have Summary and a Sum formula.
-
- 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
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
Hans