VBA Excel taking control of the cells on the another workbook

Question

I would like to link the cells on the external Excel file with the cells from my active workbook.

I found something quite good here:

and tried to combine this code with mine:

 Sub Splicing()
 Dim VariableX As Long
 VariableX = ActiveWorkbook.Sheets("Frontsheet").Range("D10").Value
 Path = ActiveWorkbook.Path & "\Splicing Template_V1.0.xlsx"
 Workbooks.Open (Path)
 Worksheets("Frontsheet").Cells(4, 10).Value = VariableX

 End Sub

The debugger says: Type mismatch

Is there any way to link these cells between two separate workbooks?

The content in `D10` is not a `Long`, but a `String`. Or just use `Variant`. — BigBen, Apr 28 '20 at 13:48
Yes, indeed. I changed. Now the file is opening, but nothing changed. — Geographos, Apr 28 '20 at 13:51
You aren't using the workbook you opened. You are grabbing a value from a workbook, opening a different workbook, then pasting the value right back to where you got it from. — Warcupine, Apr 28 '20 at 13:56
You're never even accessing the other workbook. VariableX is from FrontSheet and then you assign a cell value on front sheet = Variable X — Dammer15, Apr 28 '20 at 13:56
@Warcupine this is what I need in VBA :) So far the Code for the opening workbook is wirking — Geographos, Apr 28 '20 at 13:58

score 1 · Accepted Answer · edited Jun 20 '20 at 09:12

1

Sub Splicing()
 Dim VariableX As string
 Dim newbook as workbook
 VariableX = ActiveWorkbook.Sheets("Frontsheet").Range("D10").Value
 Path = ActiveWorkbook.Path & "\Splicing Template_V1.0.xlsx"
 set newbook = Workbooks.Open(Path)
 newbook.sheets("Frontsheet").Cells(4, 10).Value = VariableX 'Not sure if this is the right worksheet name

 End Sub

edited Jun 20 '20 at 09:12

Community

1
1

answered Apr 28 '20 at 13:59

Warcupine

4,460
3
15
24

This is exactly what I have now. The file is opening, but no changes have been reported inside. – Geographos Apr 28 '20 at 14:01
If your picture is the two workbooks then the cell you are trying to write to already as the same value so you won't see a change. – Warcupine Apr 28 '20 at 14:03
Let me replace the picture then. Sorry I put just wrong – Geographos Apr 28 '20 at 14:06
1

Works for me, Though ```Cells(4, 10)``` is row 4 column J Not row 10 Column D – Warcupine Apr 28 '20 at 14:15
Yeah! That's true. Because I have I and J merged I couldn't see the result. Now is correct. I am very grateful for your hint. Thanks! – Geographos Apr 28 '20 at 14:27

VBA Excel taking control of the cells on the another workbook

1 Answers1

Linked