To prompt us to copy from

[Frank Situmorang]

Hello,

This is my VBA to copy it from desegnated folder to desegnated folder.
My question,

1. how can we make it to prompt us to copy from, then to desingnated folder.
2. From designated folder then to prompt us to copy to destination

Sorry, I do not know how to say it in English, but my intention is:
1 Prompt us from and destination is fixed
2. Prompt us for Destination and from/origin is Fixed.

This is my VBA for both origin and destination is fixed ( determined)
Private Sub SendtoOutbox_Click()

On Error GoTo Err_SendtoOutbox_Click

Call Shell("xcopy C:\Churchdata\BkEnd\Hahomion_be.mdb
C:\Churchdata\ChurchdataConso\BkEnd\Hahomion_be.mdb/y")

Exit_SendtoOutbox_Click:
Exit Sub

Err_SendtoOutbox_Click:
MsgBox Err.Description
Resume Exit_SendtoOutbox_Click
End Sub

Thanks very much

 

[dymondjack]

Try adding in a few msgboxes to make sure the user wants to copy.

Dim i as Integer 'will hold the msgbox return

i = MsgBox("Would you like to copy from C:\Church.... " & vbCrLf & _
"to C:\....", vbOkCancel, "Copy")
If i = 2 Then
Goto Exit_SendtoOutbox_Click
End If

Call Shell("xcopy C:\....")



See the help file on return values for MsgBox (they're all the way at the
bottom of that subject's page). I'm pretty sure 2 is Cancel, but check
before you try it.

As a side note, I believe RoboCopy is the preferred method, replaceing
xcopy, but to each his own. Another way would be to use the Dir() and
FileCopy vba functions rather than shelling to the cmd line.

hth


--
Jack Leach
www.tristatemachine.com

- "Success is the ability to go from one failure to another with no loss of
enthusiasm." - Sir Winston Churchill

 

[Frank Situmorang]

Thanks dymondjack, but what I meant is just like the Get file open.. could
you please help me?

Thanks in advance

Frank

 

[dymondjack]

http://www.mvps.org/access/api/index.html

Here you go... the first two APIs listed here should do what you are looking
for.
You probably want the second one (BrowseFolder) mostly.

1 Prompt us from and destination is fixed

Sub cmdClick()
Dim sSource As String
Dim sCmdLine As String

sSource = BrowseFolder()

sCmdLine = "xcopy " & sSource & " C:\DestFolder /switches"

Shell sCmdLine
End Sub


End Sub

2. Prompt us for Destination and from/origin is Fixed.

Sub cmdClick()
Dim sDest As String
Dim sCmdLine As String

sDest = BrowseFolder()

sCmdLine = "xcopy C:\Source " & sDest & " /switches"

Shell sCmdLine
End Sub


I think this is what you are alooking for... be careful of spaces in the
source and destination paths, you will need to enclose them in quotes for
cmd.exe to read them correctly. Also, if this code will ever go on more
than one computer I wouldadvise writing a vba function to loop the files in
the folder, as xcopy and robocopy will fail on different versions of xp, and
there is no error handling that can be evaluated through vba in cmdline
procedures (error handling is capable in the batch files, but AFAIK there is
no way to get this information into vba, except for logging to a text file
and then having vba read the file). Food for thought, anyway

hth


--
Jack Leach
www.tristatemachine.com

- "Success is the ability to go from one failure to another with no loss of
enthusiasm." - Sir Winston Churchill

 

[Frank Situmorang]

Thanks dymondjack, I will give it a try. I am not too good in access. All
just by the help of you good people.

Thanks,

Frank

 

[Frank Situmorang]

Hello dymondjack: I have tried it, but the message is BrowseFolder is not
defined, could you tell me why this happened, this is my VBA:
Private Sub cmdSendDatatoexternal_Click()
Dim sDest As String
Dim sCmdLine As String

sDest = BrowseFolder()

sCmdLine = "xcopy C:\Churchdata\ChurchdataConso\BkEnd\Hahomion_be.mdb " &
sDest & " /switches"

Shell sCmdLine
End Sub

Thanks in advance,

Frank

 

[dymondjack]

http://www.mvps.org/access/api/api0002.htm


Go to this link and copy/paste the code from the site into a new module.
Get to a new module by opening the vb editor and using Insert -> Module from
the menubar, or from the database window, click modules, and then new at the
top.

Make sure the first two lines are:

Option Compare Database
Option Explicit

Paste the code from the website directly below these two lines.

Go to your code and find the BrowseFolder() line.

Enter quotes in the (), so it looks like:

sDest = BrowseFolder("")

(Anything you type between these quotes will show up as the title of the
window, you can leave it blank or put something in if you want).

Compile All Modules

Save

That should take care of the message you are getting.



Next question, are you copying only one file? xcopy is generally used for
copying multiple files, and if you are only copying one file you can do it
directly from VBA without having to shell a cmd line.

FileCopy Source, Destination

The filecopy function will copy a single file for you. Source and
Destination need to be the full path of the existing and the name of your
copy (including filename and extension).

The BrowseFolder function will return a path and foldername, you will have
to add the filename (using & "filename.mdb")

So if you want to do it that way, you will have:



Private Sub cmdSendDatatoexternal_Click()
Dim sDest As String

'Get the folder
sDest = BrowseFolder("")
'Add the file
sDest = sDest & "yournewfilename.mdb"
Debug.Print sDest

'the debug statement prints the value of sDest to your immediate window
'press ctrl+G to see the immediate window
'Make sure the \ is between the folder and filename
'If not, make sDesc = sDesc & "\yournewfilename.mdb"

FileCopy "C:\Churchdata\ChurchdataConso\BkEnd\Hahomion_be.mdb", sDest

End Sub


Otherwise I think your cmdline might need some touching up as well (I put
/switches there for you to choose whatever switches you wanted to use for
xcopy)


hth, let me know if you need a hand with any more details

--
Jack Leach
www.tristatemachine.com

- "Success is the ability to go from one failure to another with no loss of
enthusiasm." - Sir Winston Churchill

 

[Frank Situmorang]

Thanks dymondjack for your quick response. I just need to copy one file. I
already have it to copy my backend data, now I want to user to chose to copy
it to any folde. Because what I have made is a church membership database,
and they just copy their data to a flashdisk or any folder then they can send
it to the Regional office of the churches in order to combine the data.

Yes..Sir.. I need the detail how can we copy it from
C:\Churchdata\ChurchdataConso\BkEnd\Hahomion_be.mdb and promt the user the
destination folder.

Thanks in advance.

Frank

 

[Frank Situmorang]

Dymondjack. I forgot to say that the file name in the destination isjust the
same

Also in the regional office I will create the cmd button to prompt us for
the source and and destination path is fixed.

Thanks for your help

Frank

 

[dymondjack]

Frank,

The information I have in my last post should be everything you need to do
this. Unfortunately I don't think I can offer any more help, aside from
getting a copy of your db and writing the code for you. There are many
consultants out there that would be willing to take on a small task like this
if you are having trouble with it.

Good luck

--
Jack Leach
www.tristatemachine.com

- "Success is the ability to go from one failure to another with no loss of
enthusiasm." - Sir Winston Churchill