FSO question - copy files

ktab

Well-known Member
Joined
Apr 21, 2005
Messages
1,297
Hello,

I have the following problem with the code:

Dim fsoObj As Scripting.FileSystemObject
Const stPathFiles As String = ???????
Const stTarget As String = ????????
Set fsoObj = New Scripting.FileSystemObject
With fsoObj
If Not .FolderExists(stTarget) Then .CreateFolder (stTarget)
End With
fsoObj.CopyFile Source:=stPathFiles, Destination:=stTarget, OverWriteFiles:=True
Set fsoObj = Nothing

Where the bold ?? are how can i give as string a range?
I want to handle the code from userform so in case that i want to change path, just change, let's say sheet1.range("a1").value (e.g. c:\program files\kostas\)in a textbox and assign new value without having to change code all the time.
(if Const stPathFiles As String = sheet1.range("a1").value )

I hope my question is clear enough (i doubt)

Thank you
Kostas
 

Some videos you may like

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,047
Office Version
  1. 365
Platform
  1. Windows
Kostas

You won't be able to use constants for your ranges.

Why not just use regular string variables?
 
L

Legacy 98055

Guest
Or refer to the cells directly in your code...

<table border="1" bgcolor="White"><caption ALIGN=left><font size="2" face=Courier New>Example VBA Code:</FONT></caption><tr><td><font size="2" face=Courier New>  <font color="#0000A0">Dim</font> fsoObj <font color="#0000A0">As</font> Scripting.FileSystemObject
  <font color="#008000">'Const stPathFiles As String = ???????</font>
  <font color="#008000">'Const stTarget As String = ????????</font>
  <font color="#0000A0">Set</font> fsoObj = <font color="#0000A0">New</font> Scripting.FileSystemObject
  <font color="#0000A0">With</font> fsoObj
  <font color="#0000A0">If</font> <font color="#0000A0">Not</font> .FolderExists(stTarget) <font color="#0000A0">Then</font> .CreateFolder (stTarget)
  <font color="#0000A0">End</font> <font color="#0000A0">With</font>
  fsoObj.CopyFile Source:=Sheet1.Range("a1").Text, _
       Destination:=Sheet1.Range("a2").Text, OverWriteFiles:=True
  <font color="#0000A0">Set</font> fsoObj = <font color="#0000A0">Nothing</font>
</FONT></td></tr></table>

Tom
 

ktab

Well-known Member
Joined
Apr 21, 2005
Messages
1,297
Thank you Norie, that did he job.

Just to help others that might need the code, you got to relace also at:
If Not .FolderExists(stTarget) Then .CreateFolder (stTarget)
where stTarget is the desired cell range
 

Watch MrExcel Video

Forum statistics

Threads
1,118,032
Messages
5,569,767
Members
412,291
Latest member
marypolitan
Top