VBA Iteratively Open File when path and name is in worksheet cell?

Question

How would you open a file whose name is contained in a specific column in an excel workbook? Note: the catch here is that the file path is external, and I'd like to do this iteratively

I've got a macro that gives me a list of files to open. I want to make a macro to open each of those files from E1 to E100. The current code I've tried to start with is below. Once I am able to open a single file, I'll branch out to iteration. The intent is to open a file whose path is connected to a network like: \nam.corp.company.com\location\folder\subfolder\subfolder2\file.csv

If it helps here's a working site to pull data from. (ex: //chart.finance.yahoo.com/table.csv?s=MSFT)

Code Below:

Sub openEZ()
Dim FilePath2 As String
Application.DisplayAlerts = False
FilePath2 = Range("E4").Value
Workbooks.Open FileName:=FilePath2
Application.DisplayAlerts = True
'ActiveWorkbook.Close

End Sub

[Error image][1] [1]: https://i.stack.imgur.com/a7ZVu.png

What's the specific problem you're having? What happens when you run this? Your code looks fine but without knowing exactly what's in E4 there's not much we can suggest. — Tim Williams, Jul 30 '21 at 20:59
@TimWilliams, Run-time error '1004': method 'Open' of object 'Workbooks' failed. The file path is long (311 characters) which may be the issue if WB.open is limited on the # of characters — Matt, Jul 30 '21 at 21:02
@GMalc, the issue is not with the data contained in the file it's either the code or the file path that is problematic. Do you know what the character limit is for Workbook.Open? with the file path at 300+ characters I suspect that is the issue. Any work around? — Matt, Jul 30 '21 at 21:12
How to deal with long file paths: https://stackoverflow.com/questions/21194530/what-does-mean-when-prepended-to-a-file-path — Tim Williams, Jul 30 '21 at 21:31
See also https://stackoverflow.com/questions/38432250/access-files-with-long-paths-over-260 — Tim Williams, Jul 31 '21 at 00:31

score 0 · Accepted Answer · answered Jul 30 '21 at 21:36

If you need to deal with long paths then instead of (eg)

\\nam.corp.company.com\location\folder\subfolder\subfolder2\file.csv

Try

\\?\UNC\nam.corp.company.com\location\folder\subfolder\subfolder2\file.csv

That's the recommended approach, though I find it may not always work.

VBA Iteratively Open File when path and name is in worksheet cell?

1 Answers1