Logo 
Search:

MS Office Forum

 Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Need help

  Asked By: Almas    Date: Feb 26    Category: MS Office    Views: 608
  

I'm a complete newbie when it comes to coding in Excel, so I'm not
even sure I can articulate what I want, but I'll try.

I want to be able to read data from a file without having it
physically open in front of me. Example: I want an automatic macro in
spreadsheet I to read through successive rows of Column A of
Spreadsheet II to see if the value equals Z, then pull data from the
same row, but column B and store it into a certain cell in Spreadsheet
I. I hope that explained it well enough. Also, before this macro
gets ran, I want the user to be able to specify Spreadsheet II's file.

I'm pretty sure I can do what I want if Spreadsheet II is open, but I
wanted to avoid that so if anybody could explain to me how to do it,
I'd appreciate it.

Share: 

 

2 Answers Found

 
Answer #1    Answered By: Gilberto Thompson     Answered On: Feb 26

Here are some examples of accessing a workbook as a hidden object:

Sub Copy2()
'Open Book2.xls as hidden object and copy
'some data  from it to Book1.xls
twp = ThisWorkbook.Path
Set Wb1 = GetObject(twp & "\Book1.xls")
Set Wb2 = GetObject(twp & "\Book2.xls")
Wb1.Sheets("Sheet1").Range("A4:B14").Formula _
= Wb2.Sheets("Sheet1").Range("A4:B14").Formula
Wb2.Close
Set Wb1 = Nothing
Set Wb2 = Nothing
End Sub

Sub Modify2()
'Modify Book2.xls and save it
twp = ThisWorkbook.Path
Set Wb2 = GetObject(twp & "\Book2.xls")
Wb2.Sheets("Sheet1").Range("count").Value _
= Wb2.Sheets("Sheet1").Range("count").Value + 1
'Make the hidden workbook visible before saving
'else it won't be visible when opened later!
Wb2.Windows("Book2.xls").Visible = True
Wb2.Save
Wb2.Close
Set Wb2 = Nothing
End Sub

 
Answer #2    Answered By: Abbas Hashmi     Answered On: Feb 26

I presume, you would define both Wb1 and Wb2 as follows:
Dim Wb1 as Object, Wb2 as Object
Right ?

 
Didn't find what you were looking for? Find more on Need help Or get search suggestion and latest updates.




Tagged:  

 
 
 
 

Related Post