Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 2 of 2

Thread: Making headway on formula

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Posts
    75
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks to Al, I have made some head way on placing variables in this formula.

    strfile = Source
    ActiveCell.FormulaR1C1 = "='" & strfile & "'!R21C4"

    I adjusted my code so it will not open the source file. It made the the formula work well and a popup box come up and reqested the sheet in the given file I wanted to link. The question now is how do I get it so it just automatically goes through each sheet and links the data..

    I am so close, and again thanks al you inderectly eleminate about 5 lines of code and solved a couple of other probs I had when I realized I did not need the file open.

  2. #2
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    You can also follow the following code by Jake Marx:


    VBA CODE EXAMPLES

    by Jake Marx

    ??? read the names of all Sheets in a closed workbook ???

    Here's a way to do it through ADO (ActiveX Data Objects) in Excel
    2000. To use this code, you must first set a reference to "Microsoft
    ActiveX Data Objects 2.5 Library" and "Microsoft ADO Ext. 2.5 for DDL and
    Security".

    Sub ReadSheetNames(TheCompleteFilePath As String)
    Dim cnn As New ADODB.Connection
    Dim cat As New ADOX.Catalog
    Dim tbl As ADOX.Table

    cnn.Open "Provider=MSDASQL.1;Data Source=" _
    & "Excel Files;Initial Catalog=" & TheCompleteFilePath
    cat.ActiveConnection = cnn
    For Each tbl In cat.Tables
    MsgBox Left$(tbl.Name, Len(tbl.Name) - 1)
    Next tbl

    Set cat = Nothing
    cnn.Close
    Set cnn = Nothing
    End Sub


    _________________
    Hope this helps.
    Kind regards, Al.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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