Select a File and copy to a folder in Excel VBA

Question

I need help with the following: After clicking browse, I want users to be able to select a file. The path to the selected file will show in a text box. After they select the file, I want them to click my 2nd browse button and select the folder to copy the file to. The second text box will contain the path to the folder that they select. After they click Create CSV button, The path to the .csv file will show in the 3rd text box (this path will be the Folder selected + the file with the time stamp. I have attached an image of my textboxes. Here is my code so far:

Sub CreateCSV_Click()
    
    Dim CamelotXl As Workbook
    Dim strFile As String
    Dim SelectedFolder As String
    Dim CSVFilePath As String
    
    '//  CSVFilePath = "C:\tmp\CamelotSL\Camelot_csv_export" & "_" & Format(DateTime.Now, "MMddyyyyhhmmss") & ".csv"
    SelectedFolder = Worksheets(1).txtCamelotfolder.Text
    strFile = Worksheets(1).txtCamelotExcelFile.Text
   
     CSVFilePath = SelectedFolder & "\" & strFile & "_" & Format(DateTime.Now, "MMddyyyyhhmmss") & ".csv"
     
    If strFile = "False" Then
        Exit Sub
    End If
    
    '// Select "Detail Attachment" worksheet
    Set CamelotXl = Workbooks.Open(strFile)
    CamelotXl.Sheets("Detail Attachment").Activate
            
    '// Delete first row
    CamelotXl.Sheets("Detail Attachment").Range("1:1").Delete Shift:=xlUp
    
    Dim LastRow As Long: LastRow = CamelotXl.Sheets("Detail Attachment").UsedRange.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim LastCol As Long: LastCol = CamelotXl.Sheets("Detail Attachment").UsedRange.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
    Dim i As Long, j As Long
    Dim cellContent As String
    Dim fieldSeperator As String
    Dim fileLine As String
    
    fieldSeperator = ","
    
    Close #1
    Open CSVFilePath For Output As #1
        
    
    With CamelotXl.Sheets("Detail Attachment")
    
        '// Add header row
        For j = 1 To LastCol
                    
            cellContent = CamelotXl.Sheets("Detail Attachment").Cells(1, j)
            fileLine = fileLine & Chr(34) & cellContent & Chr(34) & fieldSeperator
            
        Next j
        
        Print #1, fileLine
        fileLine = ""
        
        For i = 2 To LastRow
            For j = 1 To LastCol
                cellContent = CamelotXl.Sheets("Detail Attachment").Cells(i, j)
                
                '//
                '// Remove double quotes from the cell content so they do not
                '// interfere with the enclosing double quotes for the field
                '// that will be generated in the csv file.
                '//
                '// For example, if the cell content is A24-104 5/8-S", we remove
                '// the trailing " so we don't have an extra double quote after
                '// enclosing the field, i.e "A24-104 5/8-S""
                '//
                cellContent = Replace(cellContent, """", "")
                
                fileLine = fileLine & Chr(34) & cellContent & Chr(34) & fieldSeperator
            Next j
            
            Print #1, fileLine
            fileLine = ""
        Next i
        
        Close #1
    End With
                    
    CamelotXl.Close (False)
    
    Worksheets(1).txtCamelotCSV.Text = CSVFilePath
             
End Sub

How do I copy the selected file in the path from textbox1 to the folder in textbox 2 and show that path in textbox 3 after I click the createcsv button? — Boltz, Oct 23 '20 at 14:12
Does this answer your question? [VBA to copy a file from one directory to another](https://stackoverflow.com/questions/16943003/vba-to-copy-a-file-from-one-directory-to-another) — Nacorid, Oct 23 '20 at 14:20

score 0 · Answer 1 · answered Oct 23 '20 at 15:58

Worksheets(1).txtCamelotExcelFile.Text = Application.GetOpenFilename("Excel Files (.xls), .xlsx ", , "Browse for your File & Import Range")

Worksheets(1).txtCamelotExcelFile.Text = Application.GetSaveAsFilename(FileFilter:="Excel Files (*.csv), *.csv")

Dim fso As Object

Set fso = VBA.CreateObject("Scripting.FileSystemObject")

Worksheets(1).txtCamelotCSV.Text = txtCamelotExcelFile.Text & "_" & Format(DateTime.Now) & ".csv"

Call fso.CopyFile(strFile, CSVFilePath, True)

Select a File and copy to a folder in Excel VBA

1 Answers1