none
Import data from another file with ThisWorkbook.Activate RRS feed

  • Question

  • So the problem is the following:

    I have a file that I want to get imported data from .csv files, which works wonderfully when I define the name of the active workbook as such:

    Sub ImportData()
    '
    'Import data from CSV file
    '
    
    '
        Dim Selected_FileName As Variant
        Selected_FileName = Application.GetOpenFilename
        If Selected_FileName <> False Then
        Workbooks.Open Filename:=Selected_FileName
        Range("A3:M94").Select
        Selection.Copy
        Windows("Potency assay ver. 1.0.xlsm").Activate
        Sheets("Potency Assay").Select
        Range("A1").Select
        ActiveSheet.Paste
        Range("A93").Value = Selected_FileName
        
        End If
    ' Keyboard Shortcut: Ctrl+Shift+I
    
    
    
    '
    End Sub
    

    However, I want the file name to be floating, aka I do not want to go into the macro when I update from version 1.0 to ver. 1.1.

    I have tried to define that I want the macro workbook to be destination for the data, but the result of the code below is just that the .CSV file opens, and then everything ends. 

    Sub ImportData()
    '
    'Import data from CSV file
    '
    
    '
        Dim Selected_FileName As Variant
        Selected_FileName = Application.GetOpenFilename
        If Selected_FileName <> False Then
        Workbooks.Open Filename:=Selected_FileName
        Range("A3:M94").Select
        Selection.Copy
        ActiveWindow.Close
        ThisWorkbook.Activate
        Sheets("Potency Assay").Select
        Range("A1").Select
        ActiveSheet.Paste
        Range("A93").Value = Selected_FileName
        
        End If
    ' Keyboard Shortcut: Ctrl+Shift+I
    
    
    
    '
    End Sub
    

       Thank you in advance
    Friday, May 10, 2019 4:27 PM

All replies

  • Try this version:

    Sub ImportData()
    '
    'Import data from CSV file
    '
        Dim Selected_FileName As Variant
        Dim wbk As Workbook
        Dim wsh As Worksheet
        Set wsh = ThisWorkbook.Sheets("Potency Assay")
        Selected_FileName = Application.GetOpenFilename
        If Selected_FileName <> False Then
            Set wbk = Workbooks.Open(Filename:=Selected_FileName)
            wbk.Worksheets(1).Range("A3:M94").Copy Destination:=wsh.Range("A1")
            wsh.Range("A93").Value = Selected_FileName
            wbk.Close
        End If
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Friday, May 10, 2019 7:21 PM