PDA

View Full Version : Solved: Excel - Opening files in string or in cell



realitybend
06-27-2008, 01:21 PM
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
:think:

goodwin57
06-30-2008, 01:56 AM
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
06-30-2008, 02:11 AM
You open workbooks with string stored in a cell like this:
Workbooks.Open (Range("A1").Value)

realitybend
06-30-2008, 07:56 AM
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
06-30-2008, 09:50 AM
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

realitybend
07-01-2008, 08:42 AM
Thanks!

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

realitybend
07-01-2008, 09:00 AM
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
07-01-2008, 09:11 AM
Set a variable for the "original" workbook at the beginning of your code:

Dim wb1 As Workbook
Set wb1 = ActiveWorkbook 'Original workbook.

Then use wb1 in your code:

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

Then at the end, set wb1 to nothing:

Set wb1 = Nothing


Alan

realitybend
07-01-2008, 09:57 AM
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?:think:

mdmackillop
07-01-2008, 10:24 AM
You need the sheet as well.

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

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

realitybend
07-01-2008, 10:32 AM
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?