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

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
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.
 
Upvote 0
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!
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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).
 
Upvote 0
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.
 
Upvote 0
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 !!
 
Upvote 0

Forum statistics

Threads
1,213,483
Messages
6,113,919
Members
448,533
Latest member
thietbibeboiwasaco

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
Back
Top