How to Link Contents from Contents in an Excel workbook to another Excel workbook using VBA?

Question

I have built a VBA code that links cell contents from one Excel sheet Employee Form to another worksheet Employee Database in the same workbook as below.

Sub Submit_Form() 
    Dim LastRow As Long, ws As Worksheet
    Set ws = Sheets("Employee Database") 
    LastRow = ws.Range("A" & Rows.Count).End(xlUp).Row + 1 

    ws.Range("A" & LastRow).Value = Worksheets("Employee Form").Range("D7").Value
    ....
    ws.Range("Z" & LastRow).Value = Worksheets("Employee Form").Range("AB").Value
End Sub

The code works well, but how to modify the code now that these sheets are in separate Excel files? The new Excel files are the same as the respective names of these worksheets.

Thanks!

Hi user71812, Is your issue solved? I find that you did not follow up this thread after posting the issue.If your issue is solved then I suggest you to post your solution and mark it as an answer. — Lina

Lina Lina · Accepted Answer · 2018-08-30T03:27:06

We could try to code below:

Sub connect() ' ' connect Macro '

'
    ActiveWorkbook.Queries.Add Name:="Sheet1", Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Source = Excel.Workbook(File.Contents(""D:\Try\write.xlsx""), null, true)," & Chr(13) & "" & Chr(10) & "    Sheet1_Sheet = Source{[Item=""Sheet1"",Kind=""Sheet""]}[Data]," & Chr(13) & "" & Chr(10) & "    #""Changed Type"" = Table.TransformColumnTypes(Sheet1_Sheet,{{""Column1"", Int64.Type}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Changed Type"""
    Sheets.Add After:=ActiveSheet
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=Sheet1;Extended Properties=""""" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [Sheet1]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = False
        .ListObject.DisplayName = "Sheet1"
        .Refresh BackgroundQuery:=False
    End With
    Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
End Sub

You can also link to another worksheet by doing an action： Connect to another workbook

Hope that helps!

How to Link Contents from Contents in an Excel workbook to another Excel workbook using VBA?

1 Answers