copy files from one dir to another

[rvik]

hai

a cell address contains a file name. i want the file name in the cel
address to be the input for the code below, so that the file is copie
to a new location....i.e. instead of the "test.doc", i should have th
file name mentioned in the cell address


Sub file_trf_copy()
oldname = "C:\test.doc"
newname = "\\....\test.doc"
FileCopy oldname, newname
End Sub
test.do

 

[Nikos Yannacopoulos]

oldname = Range("B3").value

where I have assumed the file name is in B3. Change accordingly.

HTH,
Nikos

 

[Jim Rech]

To assign the value of a cell to a variable use code like:

Dim MyStr as String
MyStr = Range("A1").Value

or

MyStr =Range("MyCell").Value

The latter uses a named cell and is better in general than a hardwired cell
address like "A1" because you do not have to change your code if you happen
to insert a row above the cell in question.

 

[Don Guillett]

NewName = ActiveCell.Value

 

[Tushar Mehta]

Suppose the cell is C1. Then use

oldname = "C:\" & range("C1").value
newname = "\\....\" & range("C1").value

To be more specific about which workbook and which worksheet, use
workbooks("wbk name here").Worksheets("wks name here").range
("C1").value
--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[rvik]

Mehta,

no, it didnt work

I get the error file not found, thought the file test.doc is availabl
in c:\

the code is:

Sub file_trf_copy()
oldname = "c:\" & Range("b4").Value
newname = "\\...\..\test.doc"
FileCopy oldname, newname
End Sub


where b4 contains the value "test"..

what am i doing wrong

 

[rvik]

mehta,

yeah.. i got it right. I left out the extension .doc in the cel
address. Once i gave the extension, code worked..

Now a rejoinder to the query, if you all don't mind...

Supposing the file already exits in the destination, then i would lik
to have a pop displaying something like " file already exists, Do yo
want to overwrite? YES / NO"


Thank

 

[Tushar Mehta]

Use something like:

Option Explicit

Sub testIt()
Dim SrcFilename As String, DestFilename As String
SrcFilename = "C:\" & Range("C1").Value
DestFilename = "\\....\" & Range("C1").Value
If Dir(DestFilename) <> "" Then
If MsgBox("File " & DestFilename & _
" exists; OK to overwrite", _
vbOKCancel) = vbCancel Then
Exit Sub '<<<<<
End If
End If
FileCopy SrcFilename, DestFilename
End Sub

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions