Open files with values stored in cells

Question

Okay the title is a little ambiguous but what I'm trying to do is the following: I have a column of file names and I want to open them all using vba. I'm assuming it would be something like what I have below but I'm missing something. The number of files can change so I can't use a definite range.

For each cl in workbookC.worksheets("sheet1").range("A"). If cl.value <> "" then. Open (filename="cl.value"). End if. Next cl

To be sure, the files you are opening are excel files? Do you really want to just open them all? Or do you want to open them, do something, then close them? — mr.Reband, May 13 '13 at 21:43
@mr.Reband yes they are excel files; I would ideally like to open them two at a time and close them but I get a message each time the files open saying there are links to other files in them and I don't want the macro to keep stopping so I'd rather open all the files at once — Sean Connecticut, May 13 '13 at 21:55
For suppressing the warning, see this article: http://stackoverflow.com/questions/14908372/how-to-suppress-update-links-warning — mr.Reband, May 13 '13 at 22:47

score 1 · Accepted Answer · answered May 13 '13 at 23:01

The code below should work. Note that I would suggest not only making sure that the cell is not blank but also checking if the file exists using a FileSystemObject before attempting to opening.

Dim sourceWorksheet
Set sourceWorksheet = workbookC.Worksheets("sheet1")

Dim row
For row = 1 To 10       'rows containing filenames
    If sourceWorksheet.Cells(row, 1).Value <> "" Then
        Dim xlwb
        Set xlwb = Workbooks.Open(sourceWorksheet.Cells(row, 1).Value)
            'do stuff
        xlwb.Close
    End If
Next

Open files with values stored in cells

1 Answers1