Solved: Excel - Opening files in string or in cell

[realitybend]

I'm taking the files in one directory and placing the ones I need in a string. Next, I need to open those files, and I need the users to be able to see which files are opened. Using the code below, I place them in the cells starting in cell A2 and going down. The cells have the whole file path and name in them. How would I take that and open each of those files?

 Dim fso, Folder, FileList, File, SearchChar, i
    
    'Search String for this word or phrase, so file will be skipped
    SearchChar = Trim(Range("J1").Value)
 
    Set fso = CreateObject("Scripting.FileSystemObject")
    'Set to appropriate folder by selecting the cell address
    'for example if the address is stored in cell D5
    Set Folder = fso.GetFolder(Trim(Range("A2").Value))
    Set FileList = Folder.Files
 
    'i=3 will ensure that the data will start pasting from 3rd Row
    i = 3
    For Each File In FileList
        'if Instr is 0 that means the file doesn't have the word
        'package in it
        If InStr(File.Name, SearchChar) = 0 Then
            '"A" will ensure that the data will start pasting from Column A
            Range("A" & i).Value = File.Name
            i = i + 1
        End If
    Next File

[goodwin57]

This may not help, as I can't tell you how to do it (because I don't know) but if you record a macro that turns those cells into hyperlinks to the files, and click on them you could possibly set something to turn each into a hyperlink, and automatically activate each hyperlink...Hyperlink insertion is of the form:


ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:= _
"C:\Documents and Settings\All Users\Documents\My Pictures\Sample Pictures\Blue hills.jpg" _
, TextToDisplay:= _
"C:\Documents and Settings\All Users\Documents\My Pictures\Sample Pictures\Blue hills.jpg"

[Simon Lloyd]

You open workbooks with string stored in a cell like this:
[VBA]Workbooks.Open (Range("A1").Value)
[/VBA]

[realitybend]

Thanks Simon,

Your post was very helpful. I also, however, need to know how to open files listed in the string FileList. How would I select each one?

[mdmackillop]

[VBA] If InStr(File.Name, SearchChar) > 0 Then
'"A" will ensure that the data will start pasting from Column A
Range("A" & i).Value = File.Name
Workbooks.Open (Folder & "\" & File.Name)
i = i + 1
End If
[/VBA]

[realitybend]

Thanks!

How would I tell the workbooks not to Update their references to other data sources?

[realitybend]

Here's what I have now.

The problem is, I'm trying to paste the list of file names into the first workbook I had open; however, it puts them in each successive file that gets opened. How do I tell it to place it in the original?

        If InStr(File.Name, SearchChar) = 0 Then
            '"A" will ensure that the data will start pasting from Column A
            Range("A" & i).Value = File.Name
            Workbooks.Open (Folder & "\" & File.Name), Password:=PwStr, UpdateLinks:=xlUpdateLinksNever
            i = i + 1
        End If

[R1C1]

Set a variable for the "original" workbook at the beginning of your code:

[VBA] Dim wb1 As Workbook
Set wb1 = ActiveWorkbook 'Original workbook.[/VBA]

Then use wb1 in your code:

[VBA] wb1.Range("A" & i).Value = File.Name[/VBA]

Then at the end, set wb1 to nothing:

[VBA] Set wb1 = Nothing[/VBA]


Alan

[realitybend]

Thanks, but I get a Run-time error '438'. The Object doesn't support this property or method. (referring to wb1.Range)

What do I do now?

[mdmackillop]

You need the sheet as well.
[VBA]

Dim ws1 As Worksheet
Set ws1 = ActiveWorkbook.Sheets(1) 'Original workbook/sheet

ws1.Range("A" & i).Value = File.Name

[/VBA]

[realitybend]

Thanks. It works! That's always a good thing.

Now I need to copy cells L3, O3, O1, O2, and paste them in that order on the first sheet. I'm completely stumped. Do you know how I could do that?