How do I have a user select an already open excel file to import data to, and paste into the file th-CodePudding

Amateur Coder here.

Currently, I have this Macro linked via button. The goal is the following:

A. Once button is pressed, prompts users to select an excel workbook to Import data to, I have the following code for this part:

  Sub select_file()

Dim FileSelect As FileDialog
Dim PathA As String

Set FileSelect = Application.FileDialog(msoFileDialogFilePicker)

    With FileSelect
        .Title = "Please Select the Doc you want to import Data to"
        .AllowMultiSelect = False
        .ButtonName = "Confirm"
            If .Show = -1 Then
                PathA = .SelectedItems(1)
            Else
                End
            End If
    End With
    
Workbooks.Open Filename:=PathA

End Sub

B. Once selected, begins to copy and paste data in an absolute manner/HardCoded. (I know its discouraged, but cell will not change ever) OR hard write the following formula into the destination cell. Please Assume F26 is from the destination document.

=IF(F26='[source.xlsm]Sheet1'!E10,'[Source.xlsm]Sheet1'!G10,"#REF")

How do I make Part B occur without recording it as a macro? Better yet, how do I make it plop that data into the destination?

Edit 1: The paste portion is me using the if function for verification.

CodePudding user response：

If you insist on making the user identify the correct workbook/worksheet, then you can try something like this:

Sub Main_Sub()

    Dim SourceWB As Workbook
    Set SourceWB = ManualSelectWorkbook
    
    ' >>> Following line are only to demonstrate
    '     selected workbook is saved as "SourceWB"
        Debug.Print SourceWB.Sheets(1).Range("A1").Value
        SourceWB.Activate

End Sub
Function ManualSelectWorkbook() As Workbook

    ' > Variables
    Dim WB As Workbook          'Possible Destination Workbook
    Dim Dict As Object          'Dictionary of workbook names
    Dim PromptText As String    'Inputbox Text Prompt
    Dim WBselected As Integer   'Index number of selected workbook
    Dim I As Integer            'Iterations
    
    ' > Variable Prep
    I = 1
    PromptText = ""
    Set Dict = CreateObject("Scripting.Dictionary")
    
    ' > Store names of each open workbook
    For Each WB In Application.Workbooks
        Dict.Add I, WB.Name
        PromptText = PromptText & "#" & I & " = " & WB.Name & vbCrLf
        I = I   1
    Next WB
    
    ' > Ask user which workbook they wish to use
    WBselected = InputBox("Please indicate which workbook you would like this data copied to:" & vbCrLf & vbCrLf & _
        PromptText & vbCrLf & _
        "Must be integer.", "Workbook Selection", "1")
        
    ' > Set Function = selected workbook
    'Debug.Print Dict(WBselected)
    Set ManualSelectWorkbook = Workbooks(Dict(WBselected))
    
    ' > Clear dictionary
    Dict.RemoveAll
    
End Function

You can even do the exact same thing inside the selected workbook to identify the correct worksheet.

On the otherhand, if you know the partial name of the output workbook, you can use something like this:

Sub ExampleSub()

    'Sub to initiate function
    AlternateEnding.Activate

End Sub

Function AlternateEnding() As Workbook
    
    Dim WB As Workbook
    
    For Each WB In Application.Workbooks
        If WB.Name Like "*Insert partial workbook name*" Then '<<< Must Leave asterixes. 
            AlternateEnding = WB
            Exit Function
        End If
    Next WB
    
End Function