0

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:

VBA change value in another workbook

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?

enter image description here

MKR
  • 865
  • 1
  • 11
  • 33
  • 2
    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. – MKR 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 – MKR Apr 28 '20 at 13:58

1 Answers1

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

enter image description here

Community
  • 1
  • 1
Warcupine
  • 4,082
  • 3
  • 14
  • 23
  • This is exactly what I have now. The file is opening, but no changes have been reported inside. – MKR 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 – MKR 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! – MKR Apr 28 '20 at 14:27