0

Let's say I have a list of file path in Excel column BD. How do I use VBA command to select all the file paths in column BD and save it to anywhere I want?

I couldn't find any tutorials on this.

This was the closest:

https://www.youtube.com/watch?v=pHFucY4VMT4

But I think this is restricted to excel and a specific file path.

Would appreciate any easy-to-use code advice here! New to VBA so please don't thrash me too badly :P

P.S I tried this but I am receiving an error:

Private Sub CommandButton8_Click()

Option Explicit
Sub CopyFiles()
' (1) requires a reference to the object library "Microsoft Scripting Runtime" under Options > Tools > References in the VBE.

    Dim FSO As Scripting.FileSystemObject
    Dim DesPath As String
    Dim C As Range
    Dim LastRow As Long
    Set FSO = New Scripting.FileSystemObject
'your destination path
    DesPath = "C:\"
    With ThisWorkbook.Sheets("MySheet") 'change MySheet for your sheet name
        LastRow = .Cells(.Rows.Count, "BD").End(xlUp).Row 'last row on column BD
        For Each C In .Range("BD2:BD" & LastRow) 'loop through all the cells from 2 to LastRow
            If Not C = vbNullString Then 'check that the cell isn't blank
                FSO.CopyFile C.Value, DesPath, True 'True means overwrite
            End If
        Next C
    End With




End Sub

The error message I received is "Compile error: Invalid inside procedure"

This error highlights in yellow this VBA command: "Private Sub CommandButton8_Click()"

Nosferato4
  • 13
  • 4
  • A file path is a string, you can easily read a string from a cell. It might be worthwhile to [clarify your question](https://stackoverflow.com/posts/56528201/edit) by adding sample data, expected outcome, what you've tried so far and explaining where the problem is, exactly – cybernetic.nomad Jun 10 '19 at 14:21
  • Nosferato where does the error highlight?? – Damian Jun 11 '19 at 06:22
  • Hi @Damian, it highlights this: Private Sub CommandButton8_Click() – Nosferato4 Jun 11 '19 at 06:29
  • Did you do: `' (1) requires a reference to the object library "Microsoft Scripting Runtime" under Options > Tools > References in the VBE.` ? – Damian Jun 11 '19 at 06:31
  • Hi @Damian, yes - I made sure I was at the line code under command button 8 and I did that step. "Microsoft Scripting Runtime" is included as one of the list of available references. – Nosferato4 Jun 11 '19 at 06:39
  • It has no sense, usually when it comes to references or something like that it highlights in blue (like it was selected) something else. Can you check that? – Damian Jun 11 '19 at 06:40
  • Hi @Damian yes I did check that, still returning the same error D': – Nosferato4 Jun 11 '19 at 06:42
  • Yes, but what is being highlighted? Not in yellow, but in blue, like it is selected by you with the mouse. – Damian Jun 11 '19 at 06:43
  • @ Damian, could it be because Option Explicit is placed right under Private Sub CommandButton8_Click() Is that what is causing the problem? – Nosferato4 Jun 11 '19 at 06:43
  • @Damian Oh yes, actually, Option Explicit was highlighted in blue - the same color as mouse highlight – Nosferato4 Jun 11 '19 at 06:44
  • `Option Explicit` goes out of the procedures, check my answer where I put it. – Damian Jun 11 '19 at 06:45
  • Hi @ Damian Apologies I don't understand. In your code option Explicit was placed above sub CopyFiles(). If I want this code to run at the press of a button, shouldn't Option Explicit be placed below Private Sub CommandButton8_Click(), but before Sub CopyFiles() ? What am I missing here ? – Nosferato4 Jun 11 '19 at 06:53
  • @Nosferato4 `Option Explicit` goes **always** Outside the procedures, on the top of your module: In Solution Explorer, select a project. On the Project menu, click Properties. Click the Compile tab. Set the value in the Option Explicit box. – Damian Jun 11 '19 at 06:55
  • Hi @Damian I'm still stick at this step. So I left clicked the "VBAProject (Recharges)" but I can't find properties even after right click. I tried going to tools --> VBA properties but I can't find the VBA compile tab as well. Still stuck at this step trying atm – Nosferato4 Jun 11 '19 at 07:28
  • @Nosferato4 can't you just copy my code as it is and change the path? It will work that way. – Damian Jun 11 '19 at 07:29
  • @Damian I tried to, but even with different file path, it still highlights that option explicit in blue color. I have a feeling I need to do the "outside of procedures" thing you mentioned, but I can't find the properties section > – Nosferato4 Jun 11 '19 at 07:37
  • `Option Explicit` goes on the top of your module, nothing else goes above it. – Damian Jun 11 '19 at 07:38
  • Hi @Damian, This is what I have now, however there is a run time error '70' :permission denied: It highlighted this cell as seen in the picture: https://paste.pics/635c95e2e36601a8d5e79bfacd1ff72b – Nosferato4 Jun 11 '19 at 07:45
  • you missed a "\" the path must be ended in "\" because you want the files inside the Adobe folder. – Damian Jun 11 '19 at 08:06
  • Omg, yes it works! @Damian 2 very important questions: 1) How do I allow the users to choose the place where they want to save instead of manually putting the string to save? (E.G. when I run the code, there is a option for me to manually select where I want to save these attachments - something like a pop up window to save something when you download something from the internet 2) Right now, this code is in a module. How do I tie it to a command button so that whenever I press the command button, it runs? – Nosferato4 Jun 11 '19 at 08:17
  • your first question [here](https://stackoverflow.com/a/26392703/7558682). Your second question, add a button then right click on the button and select the add macro option and look for the name of your macro. – Damian Jun 11 '19 at 08:29
  • @Damian Ah ok many thanks! - another noob question. How do I combine both codes (i.e. the one you gave me to save all attachments to one place and the 1 that allows users to choose the place where they want to save) – Nosferato4 Jun 11 '19 at 08:44
  • The code to select gives you a string, so you make that string to be the output from the code. – Damian Jun 11 '19 at 08:46
  • Hi @ Damian I did reference DesPath =sItem but I had an error message --> Compile Error: Variable not defined. Below is the screenshot https://prnt.sc/o0c8nh – Nosferato4 Jun 11 '19 at 08:57

1 Answers1

0

You can use FileSystemObject like this:

Option Explicit
Sub CopyFiles()
' (1) requires a reference to the object library "Microsoft Scripting Runtime" under Options > Tools > References in the VBE.

    Dim FSO As Scripting.FileSystemObject
    Dim DesPath As String
    Dim C As Range
    Dim LastRow As Long
    Set FSO = New Scripting.FileSystemObject

    DesPath = "C:\Test\" 'your destination path
    With ThisWorkbook.Sheets("MySheet") 'change MySheet for your sheet name
        LastRow = .Cells(.Rows.Count, "BD").End(xlUp).Row 'last row on column BD
        For Each C In .Range("BD2:BD" & LastRow) 'loop through all the cells from 2 to LastRow
            If Not C = vbNullString Then 'check that the cell isn't blank
                FSO.CopyFile C.Value, DesPath, True 'True means overwrite
            End If
        Next C
    End With

End Sub
Damian
  • 5,152
  • 1
  • 10
  • 21
  • `code Private Sub CommandButton8_Click() Option Explicit Sub CopyFiles() Dim FSO As Scripting.FileSystemObject Dim DesPath As String Dim C As Range Dim LastRow As Long Set FSO = New Scripting.FileSystemObject DesPath = "C:\ " 'your destination path With ThisWorkbook.Sheets("MySheet") LastRow = .Cells(.Rows.Count, "BD").End(xlUp).Row For Each C In .Range("BD2:BD" & LastRow) If Not C = vbNullString Then FSO.CopyFile C.Value, DesPath, True End If Next C End With code` – Nosferato4 Jun 11 '19 at 04:44
  • thanks for the code, I tried to tie this code to CommandButton8_Click(), however it returns an error. Do you know how I can overcome this? Error message is: Compile error: Invalid inside procedure – Nosferato4 Jun 11 '19 at 04:46
  • @Nosferato4 Following code under comments is very hard, please edit your question (adding the new code) without deleting your previous text. But my guess is that ` DesPath = "C:\ " 'your destination path With ` this is empty, you have a blank space and there's no such path. – Damian Jun 11 '19 at 06:02
  • Hey @Damian I tried the C:\ thing and removed the space, but I still got errors. I updated the code in my original post. – Nosferato4 Jun 11 '19 at 06:16