Get file path from a cell, run a check to see if the file has a specific sheet if yes copy

**Martha44** · 03-03-2013, 06:56 PM

Hello

I am trying to build a master purchase order spreadsheet, which will run a macro from a specific cell and check if the file has a sheet called extract if it has then it will copy a name range in the sheet which is also called extract and paste to the master spread sheet moving one column to the left each time.

So far I found a spreadsheet on another website that runs a macro to get the filepaths into individual cells in a spreadsheet, I have found code on here that opens the workbooks, but now I'm thinking as there are over 500 files each in a seperate folder that it will be quicker to do this with the workbooks closed. The code I have so far is

Sub Openfile()

Workbooks.Open Range("A1") & ".xlsm"

End Sub

Book2.xlsm

I need this code to loop throught the cells also.

I am fairly new to vba, is what im lookng for achievable?

**JBeaucaire** · 03-03-2013, 11:23 PM

Perhaps there are some concepts here you can adapt to your project. There are many example base macros on my site that deal with common ideas like this. For instance:
===========
'WORKBOOKS TO 1 SHEET STACKED
Here's a macro for collecting data from all files in a specific folder.

Workbooks to 1 Sheet

The parts of the code that need to be edited are colored to draw your attention.
===========

This macro will teach you how to cycle through all the workbooks in a single folder. That gets you most of the way there. Once the workbook is open, you need only test if the "sheet" exists and if so execute your copy commands, then the macro closes that wb and repeats with the next.

Give it a shot.

**Martha44** · 03-04-2013, 03:11 AM

Thanks for the link I have seen similiar code on here to copy from a folder my problem is that there is only one file in each folder that I require, and I have over 500 folders, so am looking for code to get into to folder and file then move to next folder.

I have copied your code and will give it a go in the office.

**JBeaucaire** · 03-04-2013, 03:18 AM

If you complete your macro for processing all the files in a single folder, post it here and I'll help you turn into a "spelunker", a macro that will dive down through all the folders starting from a main folder and process all files in all folders.

The technique I use can be found here.

**event21** · 03-04-2013, 03:35 AM

Hi -

you can try this function to check whether the sheet named "extract" is in the closed workbook.

Function checkSheetName(fName As String) As Boolean
Dim con As Object, cata As Object, tbl As Object
Set con = CreateObject("ADODB.Connection")
Set cata = CreateObject("ADOX.Catalog")
con.Open "Provider=MSDASQL.1;Data Source=Excel Files;Initial Catalog=" & fName
Set cata.ActiveConnection = con
For Each tbl In cata.Tables
    If InStr(tbl.Name, "extract") > 0 Then
        checkSheetName = True: Exit For
    End If
Next
Set cata = Nothing: Set con = Nothing
End Function

use it like;

sub test()
msgbox checkSheetName("c:\test.xlsx")
end sub

event

Get file path from a cell, run a check to see if the file has a specific sheet if yes copy

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Get file path from a cell, run a check to see if the file has a specific sheet if yes copy

Re: Get file path from a cell, run a check to see if the file has a specific sheet if yes

Re: Get file path from a cell, run a check to see if the file has a specific sheet if yes

Re: Get file path from a cell, run a check to see if the file has a specific sheet if yes

Re: Get file path from a cell, run a check to see if the file has a specific sheet if yes

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions