Please help with solution to rename files when being copied.....

daveofgv

Well-known member
Joined
Sep 17, 2008
Messages
218
Location
Dallas, TX
Programming Experience
1-3
Hello All -

I need an expert pretty quick! My below solution copies files listed in an excel sheet from one directory to another, however, I need to add another step and can't seem to figure it out.

What I need is to have another column in the excel sheet and add the text from that column to the prefix to the file that is copied:

example:

column A
book1
book2
book3
specialbook1
specialbook2

column B
recordscount.tif
submarine.tif
chairprice.tif
deskprice.tif
computerprice.tif


The files after being copied should be named:
book1_recordscount.tif
book2_submarine.tif
book3_chairprice.tif
specialbook1_deskprice.tif
specialbook2_computerprice.tif

All I really need is to add the row in the first column to the name of the image????

I have added the solution for you to see also.

What I have now that looks into an excel sheet and copies the files:

VB.NET:
Imports Excel = Microsoft.Office.Interop.Excel


Public Class Form1

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        MessageBox.Show("Make sure you click the SAVE button after you have selected the excel and folder paths to save the parameters to the dataset before clicking the copy button")

        My.Settings.ExcelFile = ""
        My.Settings.SourceFolder = ""
        My.Settings.DestinationFolder = ""


        '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)
            If My.Computer.FileSystem.FileExists(dest) Then
                txtLog2.AppendText(sheet.Cells(row, 1).value & vbCrLf)

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

                'Else
                '    txtLog.AppendText(sheet.Cells(row, 1).value & vbCrLf)

            End If

            'End If
            'txtLog.AppendText("Missing Files" & vbCrLf & sheet.Cells(row, 1).value & vbCrLf)
            'If My.Computer.FileSystem.FileExists(dest) = False Then
            'txtLog2.AppendText(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

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        'My.Computer.FileSystem.WriteAllText(My.Settings.DestinationFolder, txtLog.Text, False)
        'Me.txtlog.SaveFile("C:\Users\dbush\Desktop\New folder\FileName.txt", RichTextBoxStreamType.PlainText)
        Me.txtlog.SaveFile(My.Settings.DestinationFolder & "\Missing_Log.txt", RichTextBoxStreamType.PlainText)
        ' My.Computer.FileSystem.WriteAllText("C:\Users\dbush\Desktop\New folder\list.txt", txtLog.Text, False)
    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        Me.txtLog2.SaveFile(My.Settings.DestinationFolder & "\Duplicates_Log.txt", RichTextBoxStreamType.PlainText)
    End Sub

    Private Sub RichTextBox1_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtlog.TextChanged

    End Sub
End Class

Here is the code (from above) that actually copies the files from the excel. There must be an easy to way connect the two columns together, but I am unsure

VB.NET:
    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)
            If My.Computer.FileSystem.FileExists(dest) Then
                txtLog2.AppendText(sheet.Cells(row, 1).value & vbCrLf)

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

                'Else
                '    txtLog.AppendText(sheet.Cells(row, 1).value & vbCrLf)

            End If

            'End If
            'txtLog.AppendText("Missing Files" & vbCrLf & sheet.Cells(row, 1).value & vbCrLf)
            'If My.Computer.FileSystem.FileExists(dest) = False Then
            'txtLog2.AppendText(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

Would anyone know quickly how to add this to my project so I can apply it here at work???

Thanks

daveofgv
 

Attachments

  • filecopy.zip
    18.8 KB · Views: 14
Last edited by a moderator:
Back
Top