Add File Name to Cell B1 and Date in A1 when importing File

[jrjobe]

Hi All!

Is there a way to modify the code below so that when I import a new file, it starts the import on row 2 and adds the file name of the import to cell B1 and the date of import to cell A1? There isn't a header row in the file, it just imports the raw data from a csv file.

I've been messing around with it to at least get it to start the import on row 2, but keep getting a Type Mismatch error. As indicated from the comment in the code, I did find this code from MrExcel and modified it to work a while back....so far it's been working excellent, but now I need to include the file name so I know I'm working with the latest file.

Sub Import()
'info came from MrExcel.com and modified to work for this workbook

Dim strFile As String, wb As Workbook
'Open the File Dialog
    With Application.FileDialog(3)
         .AllowMultiSelect = False
         'Show the dialog box
         If .Show Then
            'Store in fullpath variable
            fullpath = .SelectedItems.Item(1)
            
            'open the file
            Set wb = Workbooks.Open(fullpath)
         End If
         If wb Is Nothing Then Exit Sub
        'copy range from selected item into current worksheet
        wb.Sheets(1).Range("A:L").COPY
        ThisWorkbook.Worksheets("Import").Activate
        ActiveSheet.Range("A:L").PasteSpecial
        Columns("A:L").EntireColumn.AutoFit
        Application.CutCopyMode = False
        wb.Close False
    End With
End Sub

Thanks, everyone!

 

[Fluff]

How about

Sub Import()
'info came from MrExcel.com and modified to work for this workbook

Dim strFile As String, wb As Workbook
'Open the File Dialog
    With Application.FileDialog(3)
         .AllowMultiSelect = False
         'Show the dialog box
         If .Show Then
            'Store in fullpath variable
            fullpath = .SelectedItems.Item(1)
            
            'open the file
            Set wb = Workbooks.Open(fullpath)
         End If
         If wb Is Nothing Then Exit Sub
        'copy range from selected item into current worksheet
        With wb.Sheets(1)
            .Range("A1:L" & .Range("A" & Rows.Count).End(xlUp).Row).Copy ThisWorkbook.Worksheets("Import").Range("A2")
        End With
        With ThisWorkbook.Worksheets("Import")
            .Range("A1:B1").Value = Array(Date, wb.Name)
            .Columns("A:L").EntireColumn.AutoFit
        End With
        Application.CutCopyMode = False
        wb.Close False
    End With
End Sub

This assumes that col A will always have a value.

 

[jrjobe]

@Fluff That did the trick! I really appreciate the quick reply. Have a great day!

 

[Fluff]

You're welcome & thanks for the feedback.