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
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?
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.