Excel 365 VBA code to open Excel file from folder and copy data

[DThib]

Hi all,

I need help building a code. The open to the correct folder from input boxes works fine. I end up in the folder holding the data.
But I also need it to take the correct file selected and copy that data to the next empty row in a worksheet
Any ideas. I want to make it simpler than a solution I built for a more complex system.

Thanks in advance,

DThib

 

[DThib]

Thanks,

It runs now but stops 1 folder depth less.
The last folder it needs to open to get to the file has a name that I can code part (Study-run number) but the name is free form after. I cannot get the wildcard to work.

DThib

 

[mumps]

Why don't you just select the file from the file dialogue window that opens up?

 

[DThib]

The analyst using this may mistake one for another.

The folder structure is really convoluted. Company issue.

If there is no good way to do this, let me know please.

 

[mumps]

The only other way would be to hard code the path and file name into the macro. This would mean that every time the folder path and/or the file name changes, you would have to modify the macro to suit the changes. That is why it is easiest to allow the user to select the folder and the file.

 

[DThib]

Hi Mumps,

Thanks for the help to this point. I can get the data depositing into the tab as written in the code. The data is suppose to trigger the following in another tab.


{=IFERROR(IF('Run Data'!$B$2="","",IF(INDEX('Run Data'!$B$2:$B$4957,MATCH(0, FREQUENCY(IF(EXACT('Run Data'!$B$2:$B$4957,TRANSPOSE($A$2:A2)), MATCH(ROW('Run Data'!$B$2:$B$4957),ROW('Run Data'!$B$2:$B$4957)), ""), MATCH(ROW('Run Data'!$B$2:$B$4957),ROW('Run Data'!$B$2:$B$4957))), 0))="Sample","",INDEX('Run Data'!$B$2:$B$4957,MATCH(0, FREQUENCY(IF(EXACT('Run Data'!$B$2:$B$4957,TRANSPOSE($A$2:A2)), MATCH(ROW('Run Data'!$B$2:$B$4957),ROW('Run Data'!$B$2:$B$4957)), ""), MATCH(ROW('Run Data'!$B$2:$B$4957),ROW('Run Data'!$B$2:$B$4957))), 0)))),"Error")}

This is set to automatically calculate. I have even manually calculated. Nothing appears.
HELP!

 

[mumps]

If the macro is working properly in copying the data, then I suspect that the problem is with the formula. Unfortunately, formulas are not my strength. It may be possible to have the macro perform the same action as the formula so that the formula is not necessary. I could give that a try but you would have to explain in detail, step by step, what the formula is supposed to do using a few examples from your data and referring to specific cells, rows, columns and worksheets. I think that it would be easier to help and test possible solutions if I could work with your actual file which includes any macros you are currently using. Perhaps you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.

 

[DThib]

It's here:

https://www.dropbox.com/s/py3oajhri3162n2/Sample Analysis Template(05Nov2018)_DB.xlsm?dl=0

Thanks in advance,

DThib

 

[mumps]

I noticed that the formula in cell A3 of the Analysis Table is not the same as the formulas in the rest of column A. I'm not sure if that is what you want. The formula in A3 starts with a reference to cell B2 of the Run Data sheet: IF('Run Data'!$B$2="","" This will always result in a blank cell A3 because cell B2 of Run Data is always blank. I believe that is one problem. In order for me to find a VBA solution to the formula problem, you would have to explain in words what you want the other formulas to do. This may not be practical. You may want to start a new thread and explain the formula problem. Attach the link to your file. I'm sure that there are other Forum members who have expertise in the use of formulas who will be able to help.

 

[DThib]

Rock on!

Just FYI, The data gets dumped in to the second row if it is the first data appended to "Run Data". It was pointing to the first row before but nothing was there.
I can add data if you like.

The whole workbook is coded for different pieces but starts with the formulas I am asking about.

DThib