Copy files from a list in excel?

[daveofgv]

I am using visual studio 2008 (vb.net). I have a long list of file names in column A on an excel sheet. These files are located in a directory on my network drive. I have to copy these files over to another folder. I know how to copy files in vb.net, however, I need to copy the files listed on column A. (example: column A has - 453245435.tif, 43453345.tif, american justice.tif etc....)

Right now, I am doing this with a macro in excel, but need to switch it over to a windows form and still keep column A with the list of names that need to be copied.

I hope I make sense.

Can anyone help me?

thanks

[Teme64]

There are quite a few examples how to open and read an Excel file here in DaniWeb. Like this or this. Just remember to add a reference to Excel (COM component). If you need more examples, there's a search box in the upper right corner of this page.

After opening the excel file, the rest of the code should be pretty similar to VBA code in your macros.

HTH

[daveofgv]

I looked at the urls you provided. Here is my code (with the code from my macro in my excel file named; Excel_VBA_Copy_Files.txt)

The code from the VBA doesnt really work to well in vb.net.

In the long run - what I want it to do - I will have 4 textboxes. first one will browse to the excel file. The 2nd textbox navigates to the source folder where all the files are at. The 3rd textbox navigates to where I want the files to be copied to. And the 4th textbox navigates to where a text file will be created on all the files that were not found.

I added a zip file with the work. Can you look at this and help?

Thanks

[Reverend Jim]

I believe you can only reference cells by column and row number in vb or vbScript, not by "A", or "B".

[daveofgv]

The code I have in the text doc is what I use right now in my excel sheet. It works just fine, however, I am trying to switch the process to a vb.net form application (instead of excel). All I need is to find the excel sheet with the files in column A and add the directory (and subfolders) with all the files in it..... then click where I want the copied files to end up and then search.

I wanted to provide the text doc to show how I have it right now

[Reverend Jim]

The following code is slightly modified from an example I just posted for another user. It should give you an idea on how to adapt it for what you want. If you need it to be more specific just let me know.

Imports Excel = Microsoft.Office.Interop.Excel

Public Class Form1

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        Dim xls As New Excel.Application
        Dim folder As String = "D:\myfolder\"

        xls.Workbooks.Open("d:\my documents\points-jim.xls")

        For Each sheet As Excel.Worksheet In xls.ActiveWorkbook.Worksheets

            Debug.WriteLine(sheet.Name)

            Dim row As Integer = 1

            Do Until sheet.Cells(row, 1) Is Nothing
                If Len(Trim(sheet.Cells(row, 1).value)) > 0 Then
                    Debug.WriteLine(folder & sheet.Cells(row, 1).value)
                End If
                row += 1
            Loop

        Next

    End Sub

End Class

[daveofgv]

Good. I see what you did....

I have change a couple lines to make it work with what I am trying to do. Now, after it reads the excel file I am trying to have it search a specific directory (My.Settings.sourcedirectory) and copy the files to another directory (My.Settings.desdirectory).

Here's what I have from above

Imports Excel = Microsoft.Office.Interop.Excel

Public Class Form1

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim xls As New Excel.Application
        Dim folder As String = My.Settings.excelpath
        ' Dim folder As String = "C:\Users\dbush\Desktop\Data\"

        xls.Workbooks.Open(My.Settings.excelpath)
        'xls.Workbooks.Open("d:\my documents\points-jim.xls")

        For Each sheet As Excel.Worksheet In xls.ActiveWorkbook.Worksheets
            MessageBox.Show(sheet.Name)
            ' Debug.WriteLine(sheet.Name)

            Dim row As Integer = 1

            Do Until sheet.Cells(row, 1) Is Nothing
                If Len(Trim(sheet.Cells(row, 1).value)) > 0 Then
                    MessageBox.Show(folder & sheet.Cells(row, 1).value)
                    ' Debug.WriteLine(folder & sheet.Cells(row, 1).value)
                End If
                row += 1
            Loop

        Next

    End Sub

[Reverend Jim]

You can restrict yourself to the first sheet by

Imports Excel = Microsoft.Office.Interop.Excel

Public Class Form1

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        Dim xls As New Excel.Application
        Dim folder As String = "D:\myfolder\"
        Dim sheet As Excel.Worksheet

        xls.Workbooks.Open("d:\my documents\points-jim.xls")
        sheet = xls.Workbooks(1).Worksheets(1)

        Debug.WriteLine(sheet.Name)

        Dim row As Integer = 1

        Do Until sheet.Cells(row, 1) Is Nothing
            If Len(Trim(sheet.Cells(row, 1).value)) > 0 Then
                Debug.WriteLine(folder & sheet.Cells(row, 1).value)
            End If
            row += 1
        Loop

    End Sub

End Class

Is there anything more you need help with?

[daveofgv]

May I ask - how does that copy the first column in sheet one? If I have 500 file names in the first sheet (column A) it needs to search the directory specified and when it finds that filename it copies the file to another folder that is specified?

[Reverend Jim]

I thought you had that part figured out. If you define the source and destination folders in My.Setting variables then you can do

'get the source and destination folder names

srceFolder = My.Settings.SourceFolder
destFolder = My.Settings.DestinationFolder

'then inside the loop you compose the full path and file names

srce = srceFolder & sheet.Cells(row,1)
dest = destFolder & sheet.Cells(row,1)

objFSO.CopyFile srce,dest

A couple of potential gotchas. When you get the values of srceFolder and destFolder from Settings, make sure they end with "\". If they don't then add it. Also, you don't have to specify a file name for the destination. If it ends with "\" then the CopyFile will assume the destination is a folder and copy the file to there with the same file name as the source file.

It's up to you to do error checking to make sure the file copy succeeds and to handle any potential errors like insufficient access rights.

Have you considered that putting the source and destination folder names in the application setting makes it more difficult to modify later if you change folders?

[daveofgv]

I have:

Imports Excel = Microsoft.Office.Interop.Excel
Imports System.Object
Imports System.IO.Directory
Imports System.IO.File

Public Class Form1

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        Dim xls As New Excel.Application
        Dim folder As String = "D:\myfolder\"
        Dim sheet As Excel.Worksheet
        'get the source and destination folder names
        Dim srceFolder = My.Settings.SourceFolder
        Dim destFolder = My.Settings.DestinationFolder
        ' xls.Workbooks.Open("d:\my documents\points-jim.xls")
        xls.Workbooks.Open(My.Settings.excelpath)

        ' Debug.WriteLine(sheet.Name)

        Dim row As Integer = 1

        Do Until sheet.Cells(row, 1) Is Nothing
            If Len(Trim(sheet.Cells(row, 1).value)) > 0 Then
                'then inside the loop you compose the full path and file names
                Dim objFSO As New System.Object
                Dim srce = srceFolder & sheet.Cells(row, 1)
                Dim dest = destFolder & sheet.Cells(row, 1)

                objFSO.CopyFile(srce, dest)

                'Debug.WriteLine(folder & sheet.Cells(row, 1).value)
            End If
            row += 1
            

            
        Loop
      
    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        OpenFileDialog1.Title = "Please Select a File"
        OpenFileDialog1.InitialDirectory = "C:temp"

        OpenFileDialog1.ShowDialog()
    End Sub

    Private Sub OpenFileDialog1_FileOk(ByVal sender As System.Object, ByVal e As System.ComponentModel.CancelEventArgs) Handles OpenFileDialog1.FileOk
        Dim strm As System.IO.Stream
        strm = OpenFileDialog1.OpenFile()
        TextBox1.Text = OpenFileDialog1.FileName.ToString()
        If Not (strm Is Nothing) Then
            'insert code to read the file data
            strm.Close()
            MessageBox.Show("file closed")
        End If
    End Sub

    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        My.Settings.Save()

    End Sub

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

    End Sub

    Private Sub ComboBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ComboBox1.SelectedIndexChanged

    End Sub

    Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
  
        FolderBrowserDialog1.ShowDialog()
    End Sub

    Private Sub FolderBrowserDialog1_HelpRequest(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles FolderBrowserDialog1.HelpRequest
    
    End Sub
End Class

When I started adding your recommendations errors showed on my end so I just to fix them with the above code.

Also, It says Object reference not set to an instance of an object on the:

Do Until sheet.Cells(row, 1) Is Nothing

Am I close here?

Thanks for all the help

[Reverend Jim]

First of all, your application is a form with some controls and not a single scrap of information on that form that indicates what the user is supposed to do with it, or any context as to what the controls do. Browse? Browse for what. You mention some interaction with an Excel file but you didn't provide one for me to look at. Your code has zero comments as to what it is supposed to do. If I don't know what the code is supposed to do I can't tell when it isn't doing it.

Give me some help here and I'll do the same.

[daveofgv]

Sorry about that. I know it wasn't well coded/commented... I tried to make it better.

Also, just in case:

1. button2 browses for an excel file with the list of names. (can be any excel with file names in column A

2. button4 is the source directory will all files in it (could be 5000 files). At work all the original files go into one directory (with subfolders) this is were they will be copied form.

3. button5 is were the files will be copied to.

4. button3 saves the My.Settings of the file/folder paths

5. button1 copies the files from one director to another (where the above code is at)

I hope this helps and I think I made the sample solution to reflect the same.
I put an excel file in there also to show how it will be. But the file names need to be actual files in a directory that will be copied.

Thanks for all this

[Reverend Jim]

It's 11:00 PM here at the moment. I'll have a look in the morning.

Happy to help.

[Reverend Jim]

I hope we all had a good night's sleep. I started from scratch. I am using Visual Studio 2010 but you should be able to use what I have. First of all, start a completely new project. Don't keep anything from your current project. I'm not implying that everything you did was wrong, it's just that when I used your project things went south due to funny settings, or possibly bad references. I've attached a zip file containing my new project. One of the files is mainform.jpg which will show you what my project form looks like. Using this plus the code you should be able to rebuild. The only reference you should have to add is the "Microsoft Excel 11.0 Object Library" or whatever version is on your system. Having said that, here is the code:

Imports Excel = Microsoft.Office.Interop.Excel

Public Class Form1

    Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        'get the last saved settings

        btnCopy.Enabled = False

        txtExcel.Text = My.Settings.ExcelFile
        txtSrce.Text = My.Settings.SourceFolder
        txtDest.Text = My.Settings.DestinationFolder

        SetCopyButtonText()

    End Sub

    Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click

        'save the user entered values for the next session

        My.Settings.ExcelFile = txtExcel.Text
        My.Settings.SourceFolder = txtSrce.Text
        My.Settings.DestinationFolder = txtDest.Text

    End Sub

    Private Sub btnExcel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExcel.Click

        'browse for an Excel file containing the list of files to copy

        OpenFileDialog1.Title = "Select an Excel File"

        If txtExcel.Text <> "" Then
            OpenFileDialog1.InitialDirectory = My.Computer.FileSystem.GetParentPath(txtExcel.Text)
        End If

        OpenFileDialog1.Filter = "Excel Files|*.xls"

        If OpenFileDialog1.ShowDialog() = DialogResult.OK Then
            txtExcel.Text = OpenFileDialog1.FileName
            SetCopyButtonText()
        End If

    End Sub

    Private Sub btnGetSrce_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnGetSrce.Click

        'browse for a folder containing the files to copy

        FolderBrowserDialog1.SelectedPath = txtSrce.Text

        If FolderBrowserDialog1.ShowDialog() = DialogResult.OK Then
            txtSrce.Text = FolderBrowserDialog1.SelectedPath
            SetCopyButtonText()
        End If

    End Sub

    Private Sub btnGetDest_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnGetDest.Click

        'browse for a folder that will contain the copied files

        FolderBrowserDialog2.SelectedPath = txtDest.Text

        If FolderBrowserDialog2.ShowDialog() = DialogResult.OK Then
            txtDest.Text = FolderBrowserDialog2.SelectedPath
            SetCopyButtonText()
        End If

    End Sub

    Private Sub SetCopyButtonText()

        'set copy button text and status - note - the button will remain disabled
        'until all required parameters have been supplied.

        btnCopy.Text = "Copy files" & vbCrLf _
                     & "    From: " & txtSrce.Text & vbCrLf _
                     & "      To: " & txtDest.Text

        btnCopy.Enabled = txtSrce.Text <> "" And txtDest.Text <> "" And txtExcel.Text <> ""

    End Sub

    Private Sub btnCopy_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCopy.Click

        'copy the files specified in column 1 of the first sheet of the Excel workbook
        'from the folder given by txtSrce to the folder given by txtDest

        Dim xls As New Excel.Application
        Dim sheet As Excel.Worksheet

        xls.Workbooks.Open(txtExcel.Text)
        sheet = xls.ActiveWorkbook.Sheets(1)

        Dim row As Integer = 1

        Do Until sheet.Cells(row, 1) Is Nothing OrElse Len(Trim(sheet.Cells(row, 1).value)) = 0

            Dim srce = My.Computer.FileSystem.CombinePath(txtSrce.Text, sheet.Cells(row, 1).value)
            Dim dest = My.Computer.FileSystem.CombinePath(txtDest.Text, sheet.Cells(row, 1).value)

            My.Computer.FileSystem.CopyFile(srce, dest)
            txtLog.AppendText("copy" & vbTab & srce & vbCrLf)
            txtLog.AppendText("  to" & vbTab & dest & vbCrLf)

            row += 1

        Loop

        xls.Quit()

    End Sub

End Class

I would ask you to make note of a few things:

1) Controls are named for their function (eg btnCopy, btnGetExcel, etc)
2) Use of white space
3) a brief comment for each sub/function
4) the text on the copy button changes dynamically so the user knows what will happen on "Click"
5) I'm obviously not known for my elegant user interfaces (but they are functional)

Ideally I would have included a block header that includes sections
Name:
Description:
Audit:

Where Audit contains an entry like
2011-10-02 original code
and a new entry when changes are made. I leave that up to you. Feel free to post further questions if you need more help.

[daveofgv]

Great.... I appreaciate your help.... I tried opening the file, however, it says the solution was created in a newer version (I have 2008)so it wont open.... :(

[Reverend Jim]

That's why I included the picture of the for - so you could rebuild it step by step. Build a new form and name the controls based on the source code. You can open the main vb file in notepad or whatever then copy each block of code in to the VB 2008 form. I suggest you not just copy the entire text in. Rather (for example), double click on each button in the form, then copy the body of each block of code (ie everything between but not including "Private Sub..." and "End Sub") into the vb 2008 code page. Just make sure you name the controls properly.

I'll be online all day in case you need more.

[daveofgv]

Looks great.... Thanks a ton.... Quick question - if a file is not in the source location it will error out.... how would I make a note thta it is not there but keep looking for the other files?

[Reverend Jim]

You can check if the file exists just before the copy using something like

if My.Computer.FileSystem.FileExists(srce) Then
    My.Computer.FileSystem.CopyFile(srce, dest)
Else
    'log the event to the txtLog box or pop up a MsgBox
    'or do something else to indicate a skipped file
End If

Or you could put the file copy in a Try/Catch block. That would also catch any other errors that might crop up during the copy like access rights violations, or trying to copy over a write protected file.

[daveofgv]

Awsome!!! Here's what I did:

     Do Until sheet.Cells(row, 1) Is Nothing OrElse Len(Trim(sheet.Cells(row, 1).value)) = 0

            Dim srce = My.Computer.FileSystem.CombinePath(txtSrce.Text, sheet.Cells(row, 1).value)
            Dim dest = My.Computer.FileSystem.CombinePath(txtDest.Text, sheet.Cells(row, 1).value)
            If My.Computer.FileSystem.FileExists(srce) Then
                My.Computer.FileSystem.CopyFile(srce, dest)
            Else
                txtLog.AppendText("Missing Files" & vbCrLf & sheet.Cells(row, 1).value & vbCrLf)
            End If
            txtLog.AppendText("copy" & vbTab & srce & vbCrLf)
            txtLog.AppendText("  to" & vbTab & dest & vbCrLf)

            row += 1

        Loop

        xls.Quit()

    End Sub

Thank you so much Reverend Jim......

This thread can now be closed with the awsome help!!!!

[Reverend Jim]

My pleasure. Only you or a moderator can mark this thread as solved.

[daveofgv]

The only problem, that I am trying to do trial and error on, is if the same file name exsists twice on the excel sheet it errors out saying already exists. I don't want to bother you with it, but thats the only problem I have. :)

I am sure it's a simple if...then statment that can be used

[Reverend Jim]

If you do My.Computer.FileSystem.CopyFile(srce,dest,True), the "True" parameter says overwrite the file if it already exists in dest.

[daveofgv]

I found out the answer: I created another txtlog (txtlog2) to put the duplicate filenames

   'copy the files specified in column 1 of the first sheet of the Excel workbook
        'from the folder given by txtSrce to the folder given by txtDest

        Dim xls As New Excel.Application
        Dim sheet As Excel.Worksheet

        xls.Workbooks.Open(txtExcel.Text)
        sheet = xls.ActiveWorkbook.Sheets(1)

        Dim row As Integer = 1
        Do Until sheet.Cells(row, 1) Is Nothing OrElse Len(Trim(sheet.Cells(row, 1).value)) = 0

            Dim srce = My.Computer.FileSystem.CombinePath(txtSrce.Text, sheet.Cells(row, 1).value)
            Dim dest = My.Computer.FileSystem.CombinePath(txtDest.Text, sheet.Cells(row, 1).value)
            If My.Computer.FileSystem.FileExists(dest) Then
                txtLog2.AppendText(sheet.Cells(row, 1).value & vbCrLf)


            ElseIf My.Computer.FileSystem.FileExists(srce) Then
                My.Computer.FileSystem.CopyFile(srce, dest)
            Else : txtLog.AppendText(sheet.Cells(row, 1).value & vbCrLf)


            End If

[Teme64]

If you do My.Computer.FileSystem.CopyFile(srce,dest,True), the "True" parameter says overwrite the file if it already exists in dest.

That's true but maybe something you don't want to do. Renaming may be a better solution. And usually the best solution is to ask user what to do :)

[daveofgv]

I know this is waking up an older thread, but do you, by chance, know how I can rename the files that are being copied?

Example:

Column A
filename1.tif
filename2.tif

Column B
hello
goodbye

Results after renamed and copied =
hello_filename1.tif
goodbye_filename2.tif

(dosn't matter which column holds which.

also, I added the zip file of the solution to help you remember what is going on with the project.

Thanks and hope you can answer quick.

[Reverend Jim]

In the original solution I posted, the copy code looked like

Dim srce = My.Computer.FileSystem.CombinePath(txtSrce.Text, sheet.Cells(row, 1).value)
Dim dest = My.Computer.FileSystem.CombinePath(txtDest.Text, sheet.Cells(row, 1).value)

That code uses the same base filename (from column 1) for both the srce and dest file names. If you want to specify a different dest file name from column two just use 2 for the index in the second line as

Dim srce = My.Computer.FileSystem.CombinePath(txtSrce.Text, sheet.Cells(row, 1).value)
Dim dest = My.Computer.FileSystem.CombinePath(txtDest.Text, sheet.Cells(row, 2).value)

[Ferron]

Thank you for creating this tool. It's very helpfull, but is it possible to add a progress bar. I've found a lot of solution for just copy files, but not for files who are listed in a excel file.

[Reverend Jim]

Do you know how to program a progress bar? If yes then you should already know the answer. If no then please start a new thread with the question "how do I program a progress bar".

[muoixd]

Hello,
i try with filecopy.exe but it doesn't work.
all file i can find but tool said all is missing.
is there any guidline to use this tool?
Tks,