Open a workbook with a string - failing with file not found

[MariaO]

I am creating a macro that will open a list of files - as held as strings in the main workbook - and then copy a named sheet from each into the active workbook. The problem I have is that the code keeps failing with file not found even though the string content is correct.

Sub Consolidate_MI()'Start timer to record time elapsed
Dim StartTime As Double
Dim SecondsElapsed As Double
Dim fromwb As Workbook 'workbook from which to copy
Dim towb As Workbook 'this workbook
Dim strfullfile As String  'file full path from which to copy
Dim strfromsh As String  'from sheet to copy
Dim strtosh As String    'to sheet to copy into
Dim lngfromrow As Long   'start row from where data will be found in the copy from worksheet
Dim strfromcell As String    'cell to copy from


StartTime = Timer

Set towb = ThisWorkbook
'Maximise running speed
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Application.CalculateBeforeSave = False

' Open each file listed, clear the original copy of the tab and recopy.
    Sheets("Summary").Select  'Position on Summary tab in this spreadsheet
    
    Range("C12").Select  'Select the first file path
   
    Do Until ActiveCell.Value = ""   'Start DO - stop when a blank entry found i.e. no more files to consolidate
        strfullfile = ActiveCell.Value  'Populate variables to be used in the copy
                           
        strfromsh = ActiveCell.Offset(0, 1).Value  'now populate the from sheet
        strtosh = ActiveCell.Offset(0, 2).Value   'now the copy To sheet
        lngfromrow = ActiveCell.Offset(0, 3).Value  'The start position of the data - row  - A assumed
       
  
        Set fromwb = Workbooks.Open(strfullfile, False, True) 'open the workbook from which to copy

 

[RoryA]

the code keeps failing with file not found even though the string content is correct.

Are you absolutely certain about that? I'd be that it isn't quite correct - perhaps there are leading or trailing spaces, or spelling mistakes. Does the cell include the full path to the file as well?

 

[MariaO]

Hello, thank you for coming back so quickly! Yes, I've checked it. I've even created test files which are held on the local drive to test if it's a network problem but again same issue. Yes, the string contains the full path and file name. I've tried adding '\' at end of the file name just in case too. I've checked the extension to ensure it is correct etc. E.g. I've tried the following which is a valid path to a valid file and same error occurs. C:\Users\pxxxx8\OneDrive\documents\Marketing\Demand Analysis\Front Office Demand Analysis V1\

 

[MariaO]

Apologies, that link should read 'C:\Users\pxxxx8\OneDrive\documents\Marketing\Demand Analysis\Front Office Demand Analysis V1.xlsx\'

 

[MariaO]

Hi, re this, should the cells holding the file names with their paths be set up as hyperlinks. I've changed one and it appears to work?

 

[RoryA]

There should not be a backslash on the end of the file name, but the extension must be present - i.e. it should read:

C:\Users\pxxxx8\OneDrive\documents\Marketing\Demand Analysis\Front Office Demand Analysis V1.xlsx

To double-check, open the file manually, then in the VB Editor, in the immediate window (Ctrl+G) type:

?Activeworkbook.fullname

and press Enter. That will output the correct path and file name. Copy that into the relevant cell in your code workbook, then close the data workbook and try the code again.

 

[MariaO]

Thank you Rory. I've done as you suggested and also set the cells as hyperlinks.

 

[RoryA]

And does the code now work?

 

[MariaO]

Yes it does Thank you!