tangoman
03-15-2010, 02:59 AM
am currently working on an excel file whcih contains a list of songs. The users can select a song by using a pulldown list.
An option of dosnloading the song is to be given to the user. All he / she has to do is to click a command button to save the MP4 file on their hard disc.
How can I achieve this?
I have one code as below which works coying all files from one folder to another. But, I am looking for one file at a time and the file name is to be picked up from a cell in excel file.
Sub Download_file()
'This macro copy a file from From Source Path to To Destination Path.
Dim FSO As Object
Dim FromFolder As String
Dim ToFolder As String
Dim FileTyp As String
FromFolder = "C:\My Folder" ' The source can be a folder on a server. In this case use \\server\folder\
ToFolder = "C:\Backup" ' The destination folder cab ne on user's hard disc
FileTyp = "*.mp4" 'can use *.* for all files or *.doc for word files
If Right(FromFolder, 1) <> "\" Then
FromFolder = FromFolder & "\"
End If
Set FSO = CreateObject("scripting.filesystemobject")
' If sorce folder is not found, gives message & exits the program
If FSO.FolderExists(FromFolder) = False Then
MsgBox FromFolder & " doesn't exist. Exiting..."
Exit Sub
End If
' Checks if destination folder exists. If not, it will be created
If FSO.FolderExists(ToFolder) = False Then
MsgBox ToFolder & " doesn't exist, it will create now"
MkDir ToFolder
End If
' File downloading process
FSO.CopyFile Source:=FromFolder & FileTyp, Destination:=ToFolder
MsgBox "File " & FileTyp & " downloaded in " & ToFolder
End Sub
What I want is to add following lines at the start of code
' Get the file name from Cell "G7"
Dim file_name As String
file_name = Range("G7") ' picks up the file name from Cell G7 where a pulldown list appears
Then I want to replace
FileTyp = "*.mp4" with
FileTyp = file_name
However, it dosen't work. I am getting an error as
Run-time error "70"
permission denied.
Looking for some help in this regard.
Thanks,
tangoman
An option of dosnloading the song is to be given to the user. All he / she has to do is to click a command button to save the MP4 file on their hard disc.
How can I achieve this?
I have one code as below which works coying all files from one folder to another. But, I am looking for one file at a time and the file name is to be picked up from a cell in excel file.
Sub Download_file()
'This macro copy a file from From Source Path to To Destination Path.
Dim FSO As Object
Dim FromFolder As String
Dim ToFolder As String
Dim FileTyp As String
FromFolder = "C:\My Folder" ' The source can be a folder on a server. In this case use \\server\folder\
ToFolder = "C:\Backup" ' The destination folder cab ne on user's hard disc
FileTyp = "*.mp4" 'can use *.* for all files or *.doc for word files
If Right(FromFolder, 1) <> "\" Then
FromFolder = FromFolder & "\"
End If
Set FSO = CreateObject("scripting.filesystemobject")
' If sorce folder is not found, gives message & exits the program
If FSO.FolderExists(FromFolder) = False Then
MsgBox FromFolder & " doesn't exist. Exiting..."
Exit Sub
End If
' Checks if destination folder exists. If not, it will be created
If FSO.FolderExists(ToFolder) = False Then
MsgBox ToFolder & " doesn't exist, it will create now"
MkDir ToFolder
End If
' File downloading process
FSO.CopyFile Source:=FromFolder & FileTyp, Destination:=ToFolder
MsgBox "File " & FileTyp & " downloaded in " & ToFolder
End Sub
What I want is to add following lines at the start of code
' Get the file name from Cell "G7"
Dim file_name As String
file_name = Range("G7") ' picks up the file name from Cell G7 where a pulldown list appears
Then I want to replace
FileTyp = "*.mp4" with
FileTyp = file_name
However, it dosen't work. I am getting an error as
Run-time error "70"
permission denied.
Looking for some help in this regard.
Thanks,
tangoman