Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Aug 2001
    Location
    Cape Town, South Africa, South Africa
    Posts
    399
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Looking at file without opening (Excel 2000)

    Is it possible to look inside a file without opening the file. I'm opening about 10 files to update a consolidated file. I would like to first check whether there is any data in range (A2:A20) and then open the file if there's data

    Thanks

  2. #2
    5 Star Lounger
    Join Date
    Oct 2002
    Location
    Wellington, Wellington, New Zealand
    Posts
    621
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Looking at file without opening (Excel 2000)

    I think the answer is "no"

    The reason is that Open is merely a function to make a file available for you to look in - it involves security checks and other necessary housekeeping that are a function of a secure operating system. Even if you use some other method, it will almost certainly have to do the equivalent of an open.

    Obviously, though it depends on what you mean by "open" - a VBA routine could certainly "peek" into a workbook without having to go through the File > Open menu

  3. #3
    3 Star Lounger
    Join Date
    Aug 2001
    Location
    Cape Town, South Africa, South Africa
    Posts
    399
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Looking at file without opening (Excel 2000)

    Thanks for the reply. I would like to know how to do the peak.

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Looking at file without opening (Excel 2000)

    I don't think you can use VB "directly" to look into a file. The only way I know of would be to use VB to create formulas in a worksheet (a direct link) to "read the file" and extract the various cells then have VB check the worksheet to see if blank.

    This routine will create a new "temporary" worksheet. It will put links in the worksheet to look at the file: "C:myBook.xls" in the range A2:A20 on Sheet1. (change the info as desired). If at least 1 cell is not blank, then the file is opened. If all are blank the file is not opened. The temp worksheet is then deleted.

    Steve

    <pre>Option Explicit
    Sub PeekOpen()
    Dim wks As Worksheet
    Dim sPath As String
    Dim sFile As String
    Dim sSheet As String
    Dim sRange As String
    Dim sFilePath As String

    sPath = "C:"
    sFile = "myBook.xls"
    sSheet = "Sheet1"
    sRange = "A2:A20"

    sFilePath = "'" & sPath & "[" & sFile & "]" & sSheet & "'"
    Set wks = Worksheets.Add
    wks.Range(sRange).FormulaR1C1 = _
    "=isblank(" & sFilePath & "!RC)"
    If Not Application.Evaluate("=and(" & sRange & ")") Then _
    Workbooks.Open sPath & sFile
    Application.DisplayAlerts = False
    wks.Delete
    Application.DisplayAlerts = True
    Set wks = Nothing
    End Sub</pre>


  5. #5
    3 Star Lounger
    Join Date
    Aug 2001
    Location
    Cape Town, South Africa, South Africa
    Posts
    399
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Looking at file without opening (Excel 2000)

    Thanks Steve

    It work great

  6. #6
    5 Star Lounger
    Join Date
    Oct 2002
    Location
    Wellington, Wellington, New Zealand
    Posts
    621
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Looking at file without opening (Excel 2000)

    Stev
    Good one - although I don't think it had to be this clever <img src=/S/grin.gif border=0 alt=grin width=15 height=15> (as the file open occurs anyway).

    I'd have thought a simple -

    workbook.open
    If rangeisempty then
    workbook.close
    end if

    May also do the job and be suitable for automating the consolidation steps as well if built into a loop, or if working from a multi-select filedialog box.

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Looking at file without opening (Excel 2000)

    My method will not open the file except under the given conditions (which was the question). The direct formula links do not "open" the file (at least in excel, which I assumed was the question: look at the file before "opening" it)

    Of oourse you could, as you suggest, open it check the cells, then close it (which is simpler code). It all depends on what you want and what restrictions you want.

    Steve

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •