asked on

Macro To Open And Copy A Workbook Into Another Workbook

I need assistance in a VBA that will open the most recent file in folder (saved on a shared network path).

The main workbook that will have macro will have a dropdown with folder names. When I run the macro I want it to (1) look for the folder name then (2) open the most recent file (3) save/copy the "master" tab into the main workbook as a new tab. The new tab can be named "previous file".

Please open the attached file.
test.xlsx

ASKER CERTIFIED SOLUTION

Sam Jacobs

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Lennon Gary

ASKER

This works great!!! . I want to now change the network path to be on a SharePoint rather than a shared network path. When i attempted to change the network path it get me a error, "bad file name or number"... Can you assist on this ?

Sam Jacobs

Can you map the SharePoint folder to a drive letter?

Sam Jacobs

Give me just a min ...

Sam Jacobs

Ok, I don't have a SharePoint server to test it on, but try making the following changes:

Change:

Const NetworkPath = "C:\Data\Experts Exchange\Excel"

Open in new window

to (obviously, you'll need to adjust for your environment):

Const NetworkPath = "http://server//filepath"

Open in new window

and a few lines down change:

fullPath = NetworkPath & "\" & Cells(1, 1) & "\"

Open in new window

to:

fullPath = NetworkPath & "/" & Cells(1, 1) & "/"

Open in new window

Lennon Gary

ASKER

Thats what i had attempted to adjust but it gave me an error on the line below the "fullPath.."

Sam Jacobs

I guess the Dir function doesn't work for SharePoint ... give me few minutes to rewrite it using the FileSystemObject.

Sam Jacobs

Please see the modified file attached ... Note the 2 places you need to change for SharePoint.
Again, sorry that I can't test it ...
CopyTabv2.xlsm

Lennon Gary

ASKER

I didnt work. It gave me an error on "set objFolder..."
I will just continue to using the network path. Thank you so much sir.

Sam Jacobs

If you want to try something ... I don't have SharePoint to try it with, but I've seen the SharePoint path expressed in 3 different ways:

"http://server/filepath" (you've tried this)

"//server/filepath" (no http:) or

"//server//filepath" (double slash after the server)

Maybe one of the remaining 2 variations will work for you ... ??

Sam Jacobs

You can also insert the following line:

MsgBox ("Full path: " & fullPath)

Open in new window

...right before Set objFolder ...
to make sure that the format looks correct.