Open & loop through worksheets based on filename cell value

**ryanpetersen** · 03-04-2014, 02:09 AM

It would be great if someone could please help me with a macro to open worksheets by looping through a list of cells that contain the file name and folder location and then perform a macro on each of the worksheets.

I have the list of file names in cells C3:C20 and their related folders from D3:D20. Row 20 might change and should not be fixed.

I would like to open the file based on the cell values of C3 and the folder address in D3, perform a task then close the file before moving onto the next cell value in C4 and D4.

Many thanks for your help. I'm not yet at the stage where I can easily modify code but hopefully this will come with time.

**ajryan88** · 03-04-2014, 02:51 AM

Hi,

This should get you started:

Private Sub CommandButton1_Click()
	Dim lastRow As Long
	Dim i As Integer
	Dim WB As Workbook
	
	lastRow = 20
	
	For i = 3 To lastRow
		Set WB = Workbooks.Open(Me.Cells(i, "D").Value & "\" & Me.Cells(i, "C").Value)
		
		'Call your macro here. If need be, use WB to refer to the new workbook
		
		WB.Close
		Set WB = Nothing
	Next i
End Sub

Let me know

**HaHoBe** · 03-04-2014, 03:12 AM

Hi, ajryan88,

maybe use

		WB.Close True

to save the workbook after manipulating. Any reason why LastRow is Long and i only Integer?

Private Sub CommandButton1_Click()
  Dim lastRow As Long
  Dim i As Long
  Dim WB As Workbook
  
  On Error Resume Next
  For i = 3 To Range("D" & Rows.Count).End(xlUp).Row
    Set WB = Workbooks.Open(Cells(i, "D").Value & "\" & Cells(i, "C").Value)
    If Not WB Is Nothing Then
      'Call your macro here. If need be, use WB to refer to the new workbook
      
      WB.Close True
    Else
      MsgBox "Please check path & forlder for Row " & i, vbExclamation
      Exit Sub
    End If
  Next i
  Set WB = Nothing
End Sub

**ajryan88** · 03-04-2014, 03:15 AM

@HaHoBe:
Your first comment: good point. I didn't test this code, it was just off the top of my head
Your second comment: habit. That's the only reason. It should also be of type Long

Thanks for spotting those

**ryanpetersen** · 03-04-2014, 06:16 PM

Thank you for the quick replies.
I am receiving an error re: a folder cannot be found. It's referring to the root directory where the macro file is located.
Do I need to add code to set the folder location because the files I am opening are stored in another folder location?

**ajryan88** · 03-04-2014, 06:23 PM

Yes you will need to set the folder location, either by using absolute directories in column D ("C:\..."), or by using the ChDir("C:\...") command in VBA.

I hope this helps

**HaHoBe** · 03-05-2014, 12:54 AM

Hi, ajryan88,

I´d preferrred to use the complete address including drive and path to folder, ChDir would only work if all folders are located on the same drive otherwise ChDrive needs to be included as well before ChDir would work. And I´d stored the original drive and path in a variable in order to restore it at the end of the macro and not leave the end user in the last used folder but in the one that was assigned prior to starting the macro.

Ciao,
Holger

**ryanpetersen** · 03-05-2014, 01:56 AM

Thank you very much. It's working. I'll mark as resolved.

**ajryan88** · 03-05-2014, 03:16 PM

No worries.

Please don't forget to click on the * next to the post(s) that have been helpful to you to say thanks to the poster(s)

Open & loop through worksheets based on filename cell value

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Open & loop through worksheets based on filename cell value

Re: Open & loop through worksheets based on filename cell value

Re: Open & loop through worksheets based on filename cell value

Re: Open & loop through worksheets based on filename cell value

Re: Open & loop through worksheets based on filename cell value

Re: Open & loop through worksheets based on filename cell value

Re: Open & loop through worksheets based on filename cell value

Re: Open & loop through worksheets based on filename cell value

Re: Open & loop through worksheets based on filename cell value

Thread Information

Users Browsing this Thread

Similar Threads

VBA to use cell contents to loop through folder to see if filename exists

Open workbooks with variables in filename using loop

Lookup & open file based on partial filename

[SOLVED] set filename to <filename-date> on open

Macro to read filename in cell and open new file

Bookmarks

Bookmarks

Posting Permissions