Dear Excel specialist,
A have question, but first allow me to provide some context.
I created VBA code that opens sequentially all workbooks in a source directory (all files contain the same format/structure, but the contents vary).
At high level:
- Data is sequentially copied and paste from the (source) workbook files, range (R7:S500) to 1 destination file
- File is closed, next file opened
- All data (again) will be past in the destination file, but one column to the right -> (next column i=i+2 counter), otherwise the original data will be overwritten.
- Redo from start until no files are found
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Currently, I have 2 challenges.
1. I don't want to open the (source) workbooks (visually) while gathering the data.
I would like to have a kind of background process. For example: (https://www.exceldemy.com/excel-vba-...thout-opening/)
2. Each file in de source directory contains this cell information (content may vary)
a. Refresh_Date = Cell B1
b. Department_Name = Cell B2
c. Domain_Owner_Name = Cell C1
I would like to add these (a, b, c) at the top of each (to be added) column that is filled with the data of the source files.
I am trying to achieve this, but unfortunately, it does not work.
-----------------------------------------------------------------------------------------------
For example (please see attached example)
Blank row
02-02-2023 (This is the date field from cell B1)
Finance (This is the department name from Cell B2)
Piet Compete (This is the owner name from Cell C1)
Blank row
(R7:S500) data from the range.
-----------------------------------------------------------------------------------------------
Is it possible to help me out with this question (btw, I am not a programmer)?
Normally I add the original Excel files to provide an example, but this is not allowed, because it contains a lot of company information (i.e. confidential).
Thank you in advance for any support.
Bookmarks