VBA excel: Copy specific cells and columns from multiple files

Question

I am trying to copy a specific cell and 3 columns from multiple files into a single column on another spreadsheet.

The part called "import" simply allows to select multiple files. The part "Datacopy" should copy the desired values.

Sub import()
Dim oFileDialog As FileDialog

    Set oFileDialog = Application.FileDialog(msoFileDialogFilePicker)
    oFileDialog.AllowMultiSelect = True
    oFileDialog.InitialFileName = "C:\Users\L18938\Desktop\New_folder"    ' can set your default directory here

    oFileDialog.Show

    Dim iCount As Integer
    For iCount = 1 To oFileDialog.SelectedItems.Count
        Call Datacopy(oFileDialog.SelectedItems(iCount))
    Next

End Sub

Public Function Datacopy(strPath As String)
Dim filePath As String
Dim FileNum As Integer

filePath = strPath

Dim startDate As String
If Range("A2").Value <> "" Then
    Range("A1").End(xlDown).Offset(1, 0).Select
Else:
    Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row).Offset(1, 0).Select
End If
currentRow = 0
rowNumber = 0

Open filePath For Input As #1

'EOF(1) checks for the end of a file
Do Until EOF(1)
   
        If rowNumber = 0 Then
            startDate = lineitems(2)
        End If
        If rowNumber > 18 And item <> "" Then
      
                ActiveCell.Offset(currentRow, 0) = startDate
                ActiveCell.Offset(currentRow, 1) = lineitems(0)
                ActiveCell.Offset(currentRow, 2) = lineitems(1)
                ActiveCell.Offset(currentRow, 3) = lineitems(2)
                  
                currentRow = currentRow + 1
            End If
        End If
   
    Next item
 rowNumber = rowNumber + 1
Loop
Close #1

End Function

When I run it I get the error "sub or function not defined". The cells I am targeting are:

C1 -> is a date, different in each file, to be copied in column A
Columns A18:A, B18:B, C18:C -> are data to be copied in columns B, C, D respectively.

It is important to copy multiple files, as I have more than 180.

What line are you getting the error on? Is it Open filePath For Input As #1 because that looks like it should be a comment — cybernetic.nomad
Public Function Datacopy(strPath As String) by the way there is also a part where calls "item" that probably will gives error as it not defined (the code comes from another work) — Luca91

Frank Ball Frank Ball · Accepted Answer · 2018-10-11T14:46:18

Your problem is "startDate = lineitems(2)". There's nothing in your code that assigns any kind of value to "lineitems".

VBA excel: Copy specific cells and columns from multiple files

1 Answers