Copy data from multiple files. Xls to a new

[elsg]

Hi.

File 123AAA321.xls want to copy the cell G16 and copy to cell A1 of the new sheet (new workbook), copy the cell E22 and put in cell B1, the new spreadsheet (new workbook).
File 789BBB987.xls "E22" and paste in A2, copy "G16" and paste in B2. the new spreadsheet (new workbook)

 

[dermie_72]

Click in G16 on file 123AAA321.xls, press control + C, Press Control + N, Press Control + V. Press alt + Tab, click cell E22, Press Control + C, press Alt tab, click on B1, press Control + V.
Repeat for all file. with the various cells.
This cannot be done via a macro, unless there is some logic behind which cells, are being copied.

 

[elsg]

it is possible to do this via VBA?

Thanks

 

[elsg]

Hi.

I'm try this.

Sub DataImport()    Dim fs, f, f1, fc
    Dim Pasta As String
    Dim Linha As Integer
    
    Application.FileDialog(msoFileDialogFolderPicker).Show
    Pasta = Application.FileDialog(msoFileDialogFolderPicker).SelectedItems(1)
    
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set f = fs.getfolder(Pasta)
    Set fc = f.Files
    Linha = 1
    For Each f1 In fc
        If Right(f1.Name, 3) = "xls" Then
            Workbooks.Open f1.Name
            Sheets("Plan1").Select
            ActiveSheet.Range("G16").Copy ThisWorkbook.Sheets("Plan1").Cells(Linha, 1)
            Linha = Linha + 1
            Workbooks(f1.Name).Close SaveChanges:=False
        End If
    Next
End Sub

This macro works for me, but when you copy to the new worksheet instead of going by adding lines, adds columns ... Cola in A1, B1, C1 ... and want in A1, A2, A3 ...


Another problem when the macro opens the file I have to always put the same password, in order to automate and always put the same?


In short:


I want to adapt this macro to put data in multiple rows instead of columns.
I want her to copy more than one die.
I want her to always put the same password in the dialog box that appears when I open the files.

 

[John_w]

it is possible to do this via VBA?

Yes. Starting with the new workbook, do the steps with the Macro Recorder, et voilà!

 

[elsg]

Hi.

john, maybe you can help me with the last posting (#4) ...

 

[John_w]

File 123AAA321.xls want to copy the cell G16 and copy to cell A1 of the new sheet (new workbook), copy the cell E22 and put in cell B1, the new spreadsheet (new workbook).
File 789BBB987.xls "E22" and paste in A2, copy "G16" and paste in B2. the new spreadsheet (new workbook)

This isn't a logical order because there is a mismatch between the source and destination cells. In summary:

File 123AAA321.xls copy G16 to A1
copy E22 to B1

File 789BBB987.xls copy E22 to A2
copy G16 to B2

Is that correct? Or do you mean, for the 2nd file:

File 789BBB987.xls copy G16 to A2
copy E22 to B2

to match the cells copied from the 1st file?

And you want to copy these same cells from all the .xls files in a folder, going down the rows for columns A and B in the destination workbook?

If so, try this:

Sub DataImport()

    Dim file As String
    Dim Pasta As String
    Dim Linha As Integer
    Dim wb As Workbook
    
    With Application.FileDialog(msoFileDialogFolderPicker)
        If .Show <> 0 Then
            Pasta = .SelectedItems(1) & "\"
            Linha = 0
            file = Dir(Pasta & "*.xls")
            While file <> ""
                Linha = Linha + 1
                Set wb = Workbooks.Open(Pasta & file)
                wb.Sheets("Plan1").Range("G16").Copy ThisWorkbook.Sheets("Plan1").Cells(Linha, "A")
                wb.Sheets("Plan1").Range("E22").Copy ThisWorkbook.Sheets("Plan1").Cells(Linha, "B")
                wb.Close SaveChanges:=False
                file = Dir
            Wend
        End If
    End With
    
End Sub

If the above code is incorrect, please clarify exactly which cells should be copied and to where, because your first post wasn't very clear.

 

[elsg]

Hi.

Very good!!!!

Thank you!!