Macro to open file, select a sheet and copy this to another fileq

[bearcub]

I have file where I want to open an existing file, select a sheet, copy this sheet and paste onto the sheet which is housing the macro.

This is what I have so far using the macro recorder. I've been searching online for something that is dynamic but a lot of the code I've seen doesn't allow me to select the destination worksheet or allow me to go to a destination of my choice.

I realize this is inefficient (since I'm using the recorder):

Sub PasteFalconData()

Dim wkbk As Workbook
Set wkbk = ThisWorkbook

    Application.FileDialog(msoFileDialogFilePicker).Show
    Workbooks(activework).Activate
    ActiveCell.Cells.Select
    Selection.Copy
    wkbk.Activate
    Sheets("Extract").ClearContents
    ActiveSheet.Paste
    Application.CutCopyMode = False
End Sub
[code]/

I want to open the open dialog box->select a file->open that file-> select and copy a sheet in that file->activate the file where the data is to be pasted ->close this file ->clear the destination sheet -> paste the data that was just copied from the file that was just opened (and close). I'm lost as to open the workbook that I've selected using the file picker (or show it be the folder picker). I using 2016 and the file I'm looking for will be either an xlsx file or a xlsb file (or maybe a CSV). I'm creating a template for our admins to use.

Thank you for your help.

Michael

 

[Trebor76]

Hi Michael,

This will open the selected file (if a file is selected). You don't say the name of the tab or its location in the workbook being imported so I've left that section blank for you fill in. The code also filters on Excel and CSV files:

Option Explicit
Sub PasteFalconData()

    Dim wbThisWB    As Workbook
    Dim wbImportWB  As Workbook
    Dim strFullPath As String
    
    With Application.FileDialog(msoFileDialogFilePicker)
        .AllowMultiSelect = False
        .Title = "Please select a file to open:"
        .Filters.Add "Excel and CSV files", "*.csv; *.xls; *.xls*", 1
        .Show
        On Error Resume Next 'In case the user has clicked the <Cancel> button
            strFullPath = .SelectedItems(1)
            If Err.Number <> 0 Then
                Exit Sub 'Error has occurred so quit
            End If
        On Error GoTo 0
    End With
    
    Set wbImportWB = Workbooks.Open(strFullPath)
    'code here to copy and paste tab from Import WB into the current workbook
    
    'wbImportWB.Close False 'Close the Import WB without saving any changes. Uncomment when the code goes live.
    
End Sub

HTH

Robert

 

[bearcub]

Thank you, the destination sheet is called "Extract"

wkbk.Activate
Sheets("Extract").ClearContents
ActiveSheet.Paste

Does the code allow me to select a sheet in the file I just opened, copy data located on a particular sheet in the file and close it? Then, how do I go about pasting it into the previous Active book called "Extract"?

When I get to work tomorrow I'll play with it some more.

thank you for the help.

Michael

 

[Trebor76]

This should do everything you're after:

Option Explicit
Sub PasteFalconData()

    Dim wbThisWB    As Workbook
    Dim wbImportWB  As Workbook
    Dim strFullPath As String
    Dim lngLastRow  As Long
    Dim lngLastCol  As Long
    
    Set wbThisWB = ThisWorkbook
    
    With Application.FileDialog(msoFileDialogFilePicker)
        .AllowMultiSelect = False
        .Title = "Please select a file to open:"
        .Filters.Add "Excel and CSV files", "*.csv; *.xls; *.xls*", 1
        .Show
        On Error Resume Next 'In case the user has clicked the  button
            strFullPath = .SelectedItems(1)
            If Err.Number <> 0 Then
                wbThisWB = Nothing
                Exit Sub 'Error has occurred so quit
            End If
        On Error GoTo 0
    End With
    
    Application.ScreenUpdating = False
    
    Set wbImportWB = Workbooks.Open(strFullPath)
    'code here to copy and paste tab from Import WB into the current workbook
    On Error Resume Next 'In case there's no data or tab doesn't exist
        With wbImportWB.Sheets("Extract")
            lngLastRow = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
            lngLastCol = .Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
            If lngLastRow > 0 And lngLastCol > 0 Then
                'If the 'lngLastRow' and 'lngLastCol' variable have been set there's data to be copied.
                'The following copies the entire range from tab 'Extract' in the import workbook to cell A1 in 'Sheet1' of this workbook (change to suit).
                Range(.Cells(1, 1), .Cells(lngLastRow, lngLastCol)).Copy wbThisWB.Sheets("Sheet1").Cells(1, 1)
            End If
        End With
    On Error GoTo 0
    
    wbImportWB.Close False 'Close the Import WB without saving any changes.
    
    Set wbThisWB = Nothing
    Set wbImportWB = Nothing
    
    Application.ScreenUpdating = True
    
End Sub

Regards,

Robert

 

[Trebor76]

Note this has been cross-posted here.

 

[bearcub]

I ran the code this morning and for some reason it is creating an infinite loop. The source file isn't opening. the fileopendialog box opened and I navigated to the file I needed but after I selected the file my computer froze. Is this something that I did incorrectly here?

I just wonder if I set something up wrong,

Michael

 

[Fluff]

@bearcub
Cross-Posting
While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[bearcub]

Yes, I got an email from Ozgrid this morning alerting me to this. I didn't know that it was a no-no or that both boards were related. Sorry, I was getting desperate to find a solution. Won't happen again,

thank you for the heads up.

Michael

 

[Trebor76]

I ran the code this morning and for some reason it is creating an infinite loop.

I can't see how as there's no loop in the code? Is there's an workbook_open macro on the file you're opening?

The source file isn't opening. the fileopendialog box opened and I navigated to the file I needed but after I selected the file my computer froze.

No idea. It worked for me.

Note the code also closes the workbook so it may appear that it didn't open. If you want the workbook left open comment out this line:

wbImportWB.Close False 'Close the Import WB without saving any changes.

Is this something that I did incorrectly here?

There's no way for me know that. I will have to leave this for another board member to take up.

Good luck with it.

Robert

 

[bearcub]

No worries. For whatever reason, it was just sitting there doing nothing which is why I send it was looping. I'm believe that the code should work but for whatever reason it isn't. Thank you for sharing it with me. I'l go back again and try to find out why it doesn't run.

Michael