#  Other Applications & Softwares  > Access Tables & Databases >  >  Copy File To (Use Dialog Box to Find Location)

## Learn2010

I have a folder with Access files, subfolders, and some .vbs files in it. It requires one person to navigate through a setup process. Once the setup is completed, they need to copy the folder to a location on a shared drive. I would like to do this with vba in a file located outside the folder. In a nutshell, here is how the setup takes place.

Files are downloaded to C:\Program Files.
A vbscript is run and extracts a folder directly to the C:\Drive and opens it.
The user manually moves the BE file to a shared drive.
The user opens the FE file and is walked through using the Linked Table Manager.
The user then follows a series of forms to input data. 
After this, a Shell command closes that DB and opens another.
The second DB walks them through the Linked Table Manager and closes.
At this point I would like to open the file outside the folder.

Here is what I need:
Upon  opening the third file, I would like to have it  copy the folder C:\Test and have a dialog box open telling them to paste it in a location where all users have access to, allowing them to navigate to that location and paste the file.

Can anyone do that?

Thank you.

----------


## Learn2010

I am adding on to the post.

Another option is to open a dialog box that would allow for selection of a folder and the option to Save As. This way the user can select the file and save it to another location.

----------


## rvasquez

Hello there try the below code:

Choose folder 




```
Please Login or Register  to view this content.
```


Select File 




```
Please Login or Register  to view this content.
```

----------


## Learn2010

I put it behind a button on a form like this:




```
Please Login or Register  to view this content.
```


It bombed out on the line:  




```
Please Login or Register  to view this content.
```


So, I know I did something wrong. Can you guide me through this?

----------


## rvasquez

What was the error code that was recieved?

Also can you please place code tags around the code when posting? It makes it a little easier to read.

Thanks!

----------


## Learn2010

Error Message.....

Run-Time error ‘2147467259 (800040005)’:

Method ‘FileDialog’ of object’_Application’ failed

End Of Error Message......

I have never used code tags. What is meant by that?

Thank you.

----------


## rvasquez

To add code tags highlight your code and then select the # button on the Quick Reply option menu.

Can you please delete the code and then try to type it manually? I think you may need to add a project reference library but I'm not sure which one it was. So when you type you should get a prompt to add it.

Thanks!

----------


## Learn2010

I typed the following:




```
Please Login or Register  to view this content.
```


and got this message

VBA Message.jpg

----------


## rvasquez

Great! That's the prompt I wanted you to get, select the yes option from this promt. Then you should be able to copy and paste everything below this line and it will no longer throw the error.

----------


## Learn2010

1) It opened the dialog box to select a location. 
2)  It opened another dialog box to select a folder.
3)  I had to select a file in the folder to get through the process.

I would select a file and click on OK. The file would not copy or save to that location. However, what I need is to copy the entire folder to the selected location.

----------


## rvasquez

Okay, I'm sorry can you explain exactly what you are trying to do? The codes I gave were meant to be independent from eachother. The first option is to select a file. The second option is to select a file. Then you have to tell the code what to do with it.

----------


## Learn2010

I have a setup process that uses a folder, C:\Folder, which has subfolders, other files and scripts in it. At each site that is going to use the program, one user has to go through a setup process, which includes moving the BE file to a shared location, relinking to it, entering data, etc. When this person has completed the setup, they have to move that entire folder to another location on the shared drive. Other users will copy that folder to their C:\ Drive so that they too have C:\Folder. All the links and scripts are dependent upon that piece.

I am trying to eliminate as many manual steps as possible. This would be a big one.

Thank you.

----------


## Learn2010

I have to leave for a while. If you have more, post it and I will pick it up later.

Thanks very much. I have learned a great deal from you already. :Smilie:

----------


## rvasquez

Alrighty let's try this, please read through the comments so you can make the neccessary changes




```
Please Login or Register  to view this content.
```

----------


## Learn2010

Here is what I did and nothing happened.

I put this behind a button named CopyFolder:



```
Please Login or Register  to view this content.
```


I created a module named modCopy and pasted this behind it:



```
Please Login or Register  to view this content.
```

----------


## rvasquez

Hello there,
This line of code that you altered is incorrect.




```
Please Login or Register  to view this content.
```



It should be the full filepath that you are trying to copy over. Please open the file that you are copying over and in a blank cell type =CELL("filename"). The outcome will be something like this:

C:\Documents and Settings\rvasquez\My Documents\Example Spreadsheets\[vangur1.xlsm]Sheet1

You'll only want this portion of it:

C:\Documents and Settings\rvasquez\My Documents\Example Spreadsheets\vangur1.xlsm

Using the example file path above your code would look like this:




```
Please Login or Register  to view this content.
```


For the second instance of the line of code you change:




```
Please Login or Register  to view this content.
```


This should be a path to a folder. So locate the folder you want to copy to the selected folder from the dialog on your computer. Right click on the folder on select the Properties option. Copy the Location and then add the folder name to the end. 

For example I have folder on my computer that's called Test. When I view the Properties it states the location is C:\Documents and Settings\rvasquez\Desktop. So my code would look like this




```
Please Login or Register  to view this content.
```



Finally please remove the two functions that you inserted into a Module and merely paste them below your Private Sub CopyFolder_Click() after the End Sub.

Let me know if this changes anything.

----------


## Learn2010

This




```
Please Login or Register  to view this content.
```


Should read:


```
Please Login or Register  to view this content.
```

----------


## Learn2010

x = CopyFile("C:\Test", h.SelectedItems(1) & "\Test")

----------


## rvasquez

The file that you are trying to copy what kind of file is it?

----------


## Learn2010

I am trying to copy a folder. The folder is C:\Test.

----------


## rvasquez

The folder is just located on your C:/ Drive? Not on your desktop or within a folder?

Also, where are you trying to copy it to?

The line of code that you are using to copy a folder is not meant for that




```
Please Login or Register  to view this content.
```


That line of code is meant to copy a specified *file* not a folder. Thus the CopyFile in front of the specified locations.

The below line of code is the one used to copy the folder:




```
Please Login or Register  to view this content.
```

----------


## Learn2010

I don't know. If I knew, I could do that. It is going to several different sites and each has their own network. I have no control over where they put it. It is up to them. If I knew how to force something I would, but like I said, each network is going to be different.

What I have done is created a script that when they run it, which will be the first thing they do, it will unzip a file and put the folder and its contents on the C:\ Drive. I called the folder "Test" just for the sake of this forum.

----------


## rvasquez

Hey there,

Okay you don't have to know where they are going to put it that's what this part of the code does




```
Please Login or Register  to view this content.
```


It states copy the folder that you specify in the code, in this case C:\Test will be copied to the selected folder that the user choses from the dialog box.

So to test the code out, create a folder in your C:\Drive and name it TEST. Manually copy and paste a test document into the folder. Now, update the line of code that calls the CopyFolder function to this:




```
Please Login or Register  to view this content.
```


The run the code. Navigate to the selected folder that you tried to copy the C:/TEST folder to and see if the contents are now there?

----------


## Learn2010

Here is what I have at this point. I have everything in a folder named Hold on the C:\ Drive. It is C:\Hold. I have a test database set up. In it I have a form. on the form  I have a button named CopyAFolder. Behind the button is this code:




```
Please Login or Register  to view this content.
```


Now, I need the button, when clicked, to copy the entire folder "C:\Hold" and the dialog box open and allow the user to paste it anywhaere on the shared drive that they want to.

----------


## rvasquez

Have you tested it? Is it working at all?

----------


## Learn2010

I have tested it. It doesn't work. I don't even get a dialog box.

----------


## rvasquez

What happens when ou test it? Do you get an error?

----------


## Learn2010

Nothing happens. I put a stop break on the first line. It goes line by line to the End Sub line and then it quits.

----------


## rvasquez

Update on click event of to this




```
Please Login or Register  to view this content.
```


this way you only get one dialog box pop up. From the dialog box select the folder you wish to copy the contents of C:\Hold to. Then select the button to continue with selected folder. You must double click and ope the folder. To select it.

----------


## Learn2010

Same thing, even the F8 again.

----------


## rvasquez

Why do you have a stop break on the first line? And I'm sorry what do you mean by even the F8 again? Do you even get the dialogbox?

----------


## Learn2010

The stop break was after I clicked the button and nothing happened, I would try and see if it bypassed something. It didn't. I have to leave now. I will check the post in the morning. Thanks for all your help.

----------


## rvasquez

Alright, when you return in the morning remove the line of code that states 


```
Please Login or Register  to view this content.
```


then see if it errors out.

----------


## Learn2010

I removed the On Error line and got the following. (See attached)

Debug Message.jpg
Debug Line.jpg

Thanks.

----------


## rvasquez

Okay again remove the code and being to manually type it instead. When you get the prompt to add the library select yes. This is neccessary for the code to run properly

----------


## Learn2010

If this is something the users at other locations will have to do, I can't do it. If it becomes part of the program, and they don't have to do this, I can. I plan to make it a runtime version before sending it out. What do you think?

----------


## rvasquez

It should become part of the program. Right now you are writing the code, I believe it's the same. Users will not have to rewrite the code for the workbook to work and will also not have reselect which libraries the code uses. That's what you are doing is selecting a library that the code needs to use.

----------


## Learn2010

That worked great! There is still one problem. I created a dummy folder to copy it to. I clicked the button to run the code, the dialog box opened, the button said "Choose Selected Folder," I selected the dummy folder, clicked that button, and the contents of the folder copied into the dummy folder. The problem is I need to copy the folder as well. When the user sets this up at the other sites, they need to copy the entire folder to the shared drive. The other users will copy the same folder directly to their C:\ Drive. I am trying to eliminate them creating a folder to copy this to. I will be dealing with some of the people that will not read the instructions very well.

----------


## Learn2010

I figured out a solution. I can do this so that it is transparent to the user. I will bury the folder inside another folder, Folder A, rename everything to account for that. When I run the code you gave me, it will copy everything inside Folder A, which includes the folder I need with its contents, and when they choose the location to paste it, it will paste the folder and its contents, exactly what I need.

Thanks so much for that. You have been a great help. Now, since that works, I can do the same thing with copying a file. Can you tell me how to do that again?

----------


## rvasquez

Hello there, 

Glad you got it to work for you! Great idea on the workaround. The following is the file code




```
Please Login or Register  to view this content.
```


Please take the time to read through the comments, they will direct you on how the code works and what elements need to be altered to fit your needs.

----------


## Learn2010

Here is my actual code. I ran it and got the following message. (See attached)

Copy File Error Message.jpg

Thanks again.

----------


## rvasquez

Sorry, I forgot the function here you go




```
Please Login or Register  to view this content.
```

----------


## Learn2010

I forgot to note that this line is where it bombs out.




```
Please Login or Register  to view this content.
```


I get the attached message.

Copy File Error Message First Message.jpg

----------


## rvasquez

Try typing the line that it errros at manually again and if it asks you to add a library select yes.

----------


## rvasquez

Try typing the line that it errros at manually again and if it asks you to add a library select yes. You shouldn't of had to do this since you added it but let's see.

----------


## Learn2010

No. It just does the intellisense.

----------


## Learn2010

No. I just get the intellisense prompt to fill in the words.

----------


## Learn2010

Good morning rvasquez. Anything new?

----------


## rvasquez

When you get the inteliprompt after typing the Application.Filedialog( what are your options? Also please try assigning a different variable and declaring it

Such as 




```
Please Login or Register  to view this content.
```

----------


## Learn2010

I will be occupied for a couple of hours. Sorry, I will get back with you. Thanks again.

----------


## Learn2010

rvasquez, I have another question. I made a change in my plans for the setup process. I don't have another computer available to try this on at this time, so I will ask for your expertise. I took the code you gave me yesterday, put the folder I was to copy inside another folder, and it worked. It pasted everything inside the added folder to the new location. That was exactly what I needed. Here is the final code with the real names. PRAPSetup is the folder I put my folder in. PRAP is the folder inside PRAPSetup and is what I needed to paste and that is what pasted.




```
Please Login or Register  to view this content.
```


With the change in plans, I am now trying to add another line of code that will automatically paste the PRAP folder directly to the C:\ Drive as well, i.e., create C:\PRAP.




```
Please Login or Register  to view this content.
```


or




```
Please Login or Register  to view this content.
```


Will one of these work? I don't want to wipe out my C:\ Drive.

Thank you.

----------


## Learn2010

rvasquez,

I want to thank you a great deal. Your assistance helped me through this ordeal. With your help I was able to solve all the problems I encountered in the process. I look forward to maybe picking your brain again someday. Again, thank you very much.

----------


## rvasquez

No problem, please mark this thread solved and maybe give a little star tap to add to my reputation points.

Thanks!

----------

