PDA

View Full Version : Problem with copy data from a closed workbook



ioncila
05-18-2010, 11:33 AM
Hi
This is a small but annoying issue:

I have this proccedure that copies a range of data from a closed book without opening
(The below in a module)
Sub actualize() 'Update data from a closed book
Dim fPath As String
Dim fName As String
Dim sName As String
Dim cellRange As String

'Details of the closed book
fPath = "\\Server\SG\SGmatrix\SGorc\"
fName = "SGregEquip.xls"
sName = "INDICE" 'worksheet
cellRange = "E11:E500" 'range

GetValuesFromAClosedWorkbook fPath, fName, _
sName, cellRange
End Sub

Sub GetValuesFromAClosedWorkbook(fPath As String, _
fName As String, sName, cellRange As String)
'cellRange copies range to the same range in destination book. For another range must use OFFSET
With Worksheets("Calc EQUIP").Range(cellRange).Offset(0, -4)
.FormulaArray = "='" & fPath & "\[" & fName & "]" _
& sName & "'!" & cellRange
.Value = .Value
End With
End Sub
The bellow in Workbook Open

Private Sub Workbook_Open()
Run "actualize"
End Sub
This works very fine but, for that, I need to open a new or another workbook. If I only open my workbook, it generates an error #REF.

How do I pass over this issue?

Thank you

ioncila
05-19-2010, 07:19 AM
Hi
I was thinking of a way to get through the problem posted and this occurred to me:

One of posibilities is to open Excel in computer startup, so a workbook would be active when I open my workbook, but then I realized that other users would not do the same.

So I thought, if I introduce a proccedure in workbook event of my workbook to create a new book (add method) and make it active for, say, 2 or 3 minutes and then delete it...

Is this reasonable (or logical, or stupid)?

If you consider this logical and relevant, I would ask for help in the code of make it active for a while, if you please.

Thank you in advance

ioncila
05-19-2010, 09:58 AM
I've found this article here in the forum that apparently suits my question

http://www.vbaexpress.com/kb/getarticle.php?kb_id=454#instr

But, after trying hard, I can't adapt it to my needs.

What I really want is to copy range E11:E500 from a closed book sheet to Range A11:A500 in Calc EQUIP sheet in an open workbook.

As the article says, the closed book is very big and takes too much time to open, and all I need is that range.

Some help would be apprecciated.

Thanks

ioncila
05-20-2010, 02:37 AM
Hi

In my searching for a solution saga, I've found this thread here in the forum, which is similar to the question I have posted and it has some suggestions (particulary from GTO (Mark) that I tried to adapt:

http://www.vbaexpress.com/forum/showthread.php?t=22737

But I'm getting an error with formulaarray.

Here is what I've done:

Sub auto_open()

Dim FilePath$, Row&, Column&, Address$
Const FileName$ = "SGregEquip.xls"
Const SheetName$ = "INDICE"
Const NumRows& = 500
Const NumColumns& = 1
FilePath = ThisWorkbook.Path & "\"

DoEvents
Application.ScreenUpdating = False
If Dir(FilePath & FileName) = Empty Then
MsgBox "The file " & FileName & " was not found", , "File Doesn't Exist"
Exit Sub
End If
With ThisWorkbook.Worksheets("Calc EQUIP")
.Range("A11:A500").FormulaArray = "=[SGregEquip.xls]INDICE!$E$11:$E$500"
.Range("A11:A500") = .Range("A11:A500").Value
.Columns("A:A").AutoFit
End With
' For Row = 3 To NumRows
' For Column = 1 To NumColumns
' Address = Cells(Row, Column).Address
' Cells(Row, Column) = GetData(FilePath, FileName, SheetName, Address)
' Columns.AutoFit
' Next Column
' Next Row

ActiveWindow.DisplayZeros = False
End Sub
To remind, all I want is to copy range E11:E500 from the closed book (witout open) to A11:A500 in the activebook "Calc EQUIP" sheet.
Both books are in same folder (\\Server\SG\SGmatrix\SGorc\)

ioncila
05-20-2010, 07:28 AM
Well, I think I did it.

Or better, I think I found the solution in another thread with a GTO's suggestion.

http://www.mrexcel.com/forum/showthread.php?t=384375&page=2

I have experienced it for several times and I guess this is it.

Thanks to GTO and to you all so much
Ioncila

GTO
05-20-2010, 08:22 AM
Hi Ioncila,

Sorry I didn't catch this before now. If it is working, that is great :thumb . If you run into any problems, please post again.


Mark