Link between two .xls files

auguste

New Member
Joined
Sep 21, 2006
Messages
4
I have a macro copying data from one to another sheets within a .xls file. I would like now the do the same, however the data should be copied into another file (workbook) without opening this file. I cannot find a way to do so. Can anyone help? Thx
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

rjp

Board Regular
Joined
Jan 5, 2006
Messages
148
If you always know the name of the target workbook then this is quite simple:

Replace:

Worksheets("Sheet Name").Cells(...) = ...

With:

Workbooks("Book Name").Worksheets("Sheet Name").Cells(...) = ...

If you don't the name then it's a bit more tricky. Let me know if the above example is insufficient and I'll try to help.
 

rjp

Board Regular
Joined
Jan 5, 2006
Messages
148
Ooops, missed that bit! You could open the file, carry out the operation, then close it in code so the user would know very little about it. That would also mean that you would know the name of the workbook.

However, Andrew is correct, there's no way of doing this without opening the workbook.

Apologies once again for the sloppy answer!
 

auguste

New Member
Joined
Sep 21, 2006
Messages
4

ADVERTISEMENT

Here is the code, i am trying to record my entries into a central file...
I would like to have the sheets "New" and "RECORD" in two separate workbooks... have tried rjp proposal by adding the complete path to all sheets (ie Workbooks("C:\Admin\LogBook.xls").Worksheets("RECORD").Calculate) but this doesn't work...
Any other idea?


Sub Submit()

Dim LineCounter As Integer
Dim LineCounter2 As Integer

Sheets("New").Calculate
Sheets("RECORD").Calculate
LineCounter = Sheets("New").Range("LinesRef").Value
LineCounter2 = Sheets("RECORD").Range("LinesRef2").Value
Set RangeStart = Range("InputRef").Offset(1, 0)
Set RangeEnd = Range("InputRef2").Offset(LineCounter, 0)
Set PasteStart = Range("DateRef").Offset(LineCounter2, 0)

Range(RangeStart, RangeEnd).Select
Selection.Copy
Sheets("RECORD").Select
PasteStart.Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
The Workbooks collection only contains open workbooks (in memory). It does not contain workbooks on disk. So you can't include a drive and folder(s) when using the Workbooks property.
 

rjp

Board Regular
Joined
Jan 5, 2006
Messages
148

ADVERTISEMENT

Try this, although with your named ranges etc it's difficult for me to test.

Code:
Sub Submit()

    Dim LineCounter As Integer
    Dim LineCounter2 As Integer

    Dim currWB As String
    Dim wbPath As String
    Dim wbName As String

    Application.EnableEvents = False
    Application.ScreenUpdating = False

    currWB = ActiveWorkbook.Name

    wbPath = "C:\Admin\"
    wbName = "LogBook.xls"

    Workbooks.Open wbPath & wbName

    Workbooks(currWB).Activate
    Sheets("New").Calculate
    Workbooks(wbName).Sheets("RECORD").Calculate
    LineCounter = Sheets("New").Range("LinesRef").Value
    LineCounter2 = Workbooks(wbName).Sheets("RECORD").Range("LinesRef2").Value

    Set RangeStart = Range("InputRef").Offset(1, 0)
    Set RangeEnd = Range("InputRef2").Offset(LineCounter, 0)
    Set PasteStart = Range("DateRef").Offset(LineCounter2, 0)

    Range(RangeStart, RangeEnd).Select
    Selection.Copy
    Workbooks(wbName).Activate
    ActiveWorkbook.Sheets("RECORD").Select
    PasteStart.Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

    ActiveWorkbook.Save
    ActiveWorkbook.Close

    Application.ScreenUpdating = True
    Application.EnableEvents = True

End Sub
 

auguste

New Member
Joined
Sep 21, 2006
Messages
4
Thx a lot Rob, this looks almost perfect!
I get a bug at
PasteStart.Select
By remplacing it by a cell (ie Range("A6")) this works well, but for some reason it doesn't recognise the PasteStart value anymore (this works within the same file).
 

rjp

Board Regular
Joined
Jan 5, 2006
Messages
148
I'm guessing that PasteStart is a named range and you haven't set that up in the target workbook. Unfortunately I can't really investigate any further without access to the workbook.
 

auguste

New Member
Joined
Sep 21, 2006
Messages
4
It is actually defined in the Sub but I forgot to add the whole path in the code:
Set PasteStart = Workbooks(wbName).Sheets("RECORD").Range("DateRef").Offset(LineCounter2, 0)

All good now, thx !!
 

Forum statistics

Threads
1,136,352
Messages
5,675,274
Members
419,559
Latest member
BraytonM

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top