Macro doesn't work since files each open in individual window

[LauraLewis2151]

I am using an old Excel macro that someone else wrote. It won't work in Excel 2013 since each file opens in its own window. The spreadsheet uses a VLOOKUP function to find the info it is looking for in each file. The problem is that the macro uses a loop and opens 390 different csv files to pull the needed information into the report. Any suggestions on how I can make the macro work in Excel 2013?

Current Macro:
Sub Open_CSVs()
'
' Open_CSVs Macro
' opens the csv files needed to update the report in the workbook
'
' Keyboard Shortcut: Ctrl+Shift+C
' open csv files so wb can be updated Macro
' modified version where files are just opened as csv's, not saved as excel files.
'
Dim open_me_billing As Object
Dim MyPath As String
MyPath = "C:\Users\laura_lewis\Documents\01-Model-Continuity\SFG monthly & weekly zip pkg processing"
ChDir _
MyPath & "\SFG_Raw_CSV"
Windows("Conty_billing_update_MACRO_WB_2013-10-22.xlsm").Activate '
Application.ScreenUpdating = False
For Each open_me_billing In Range("list_of_AAL_billing_files")
ChDir _
MyPath & "\SFG_Raw_CSV"
Workbooks.Open Filename:=open_me_billing

Next
Application.ScreenUpdating = True
End Sub

 

[JLGWhiz]

Try it with the highlighted changes.

Sub Open_CSVs()
 ' Open_CSVs Macro
 ' opens the csv files needed to update the report in the workbook 
 ' Keyboard Shortcut: Ctrl+Shift+C
 ' open csv files so wb can be updated Macro
 ' modified version where files are just opened as csv's, not saved as excel files. 
 Dim open_me_billing As [COLOR=#DAA520]Range[/COLOR]
 Dim MyPath As String
 MyPath = "C:\Users\laura_lewis\Documents\01-Model-Continuity\SFG monthly & weekly zip pkg processing"
 ChDir MyPath & "\SFG_Raw_CSV"
 Windows("Conty_billing_update_MACRO_WB_2013-10-22.xlsm").Activate '
 Application.ScreenUpdating = False
     For Each open_me_billing In Range("list_of_AAL_billing_files")
         ChDir MyPath & "\SFG_Raw_CSV"
         Workbooks.Open Filename:=open_me_billing.[COLOR=#DAA520]Value[/COLOR]
     Next
 Application.ScreenUpdating = True
 End Sub

 

[LauraLewis2151]

Opened some of the files but then gave me the error "There isn't enough memory to complete this action. Try using less data or closing other applications. To increase memory availability consider:...

 

[JLGWhiz]

Opened some of the files but then gave me the error "There isn't enough memory to complete this action. Try using less data or closing other applications. To increase memory availability consider:...

You probably need to be closing some of the workbooks you are opening if you have finished with them. 390 open workbooks is a lot.

 

[Norie]

What exactly is in the range named 'list_of_AAL_billing_files'?

Is it just the filenames or is it the full path and filenames?

Also, what do you want to do with the files you are opening?

All the code seems to do is open them, do nothing with them and then not close them.

PS Does the workbook the code is running from have links to the files being opened?

 

[LauraLewis2151]

What exactly is in the range named 'list_of_AAL_billing_files'?

Is it just the filenames or is it the full path and filenames?

Also, what do you want to do with the files you are opening?

All the code seems to do is open them, do nothing with them and then not close them.

PS Does the workbook the code is running from have links to the files being opened?

The "list_of_AAL_billing_files" named range is just the file names, the files are all in the same folder (SFG_Raw_CSV).
After the files are all opened, I go back to the WB the macro is in, and ReCalc so the values are pulled into the columns for the current week. I copy that column and paste values into it. I save the macro WB, then I close all the open Excel files.