application.filesearch has abandoned me.

[warpedone]

I used to have some great code that would open each file in a given folder and copy bits and pieces of it into a master document. Well...I still have it but it doesn't work anymore since they upgraded me to 2007. I've looked around at the fixes (I found one on another site with 100s of posts) and it makes my head swim (I haven't written any VBA in a few years and everything I ever knew about it, I learned from trial and error or coming to this board).

I've copied and pasted some of the fixes and tweaked them to fit what I need to do but can't get very far. Even when I get to where I can open a file, I can't get the correct thing to copy because my old code will runs into an error.

All I want to do is open a file from a known folder; copy A1 and A3 into A1 and B1 of my master document; skip down a row in the master document; and repeat until I've done that with each file.

I'm going to keep plugging along but if someone wants to point me in a direction that doesn't entail reading War and Peace, I would appreciate it.

 

[GTO]

If you know where the file is, what is (or more accurately, was) .Filesearch doing?

 

[Trevor G]

I used to have some great code that would open each file in a given folder and copy bits and pieces of it into a master document. Well...I still have it but it doesn't work anymore since they upgraded me to 2007. I've looked around at the fixes (I found one on another site with 100s of posts) and it makes my head swim (I haven't written any VBA in a few years and everything I ever knew about it, I learned from trial and error or coming to this board).

I've copied and pasted some of the fixes and tweaked them to fit what I need to do but can't get very far. Even when I get to where I can open a file, I can't get the correct thing to copy because my old code will runs into an error.

All I want to do is open a file from a known folder; copy A1 and A3 into A1 and B1 of my master document; skip down a row in the master document; and repeat until I've done that with each file.

I'm going to keep plugging along but if someone wants to point me in a direction that doesn't entail reading War and Peace, I would appreciate it.

Look at this code as it will search through a folder using the Dir structure which will work in all versions of Excel. Change path in red

[SIZE=2]Sub x()[/SIZE]
[SIZE=2]Dim strFilename As String[/SIZE]
[SIZE=2]Dim strPath As String[/SIZE]
[SIZE=2]Dim wbkTemp As Workbook[/SIZE]
 
[SIZE=2]strPath = "[COLOR=red]C:\Access VBA Practice\[/COLOR]"[/SIZE]
[SIZE=2]strFilename = Dir(strPath & Range("a1").Value & "*.xls")[/SIZE]
[SIZE=2]Do While Len(strFilename) > 0[/SIZE]
[SIZE=2]Set wbkTemp = Workbooks.Open(strPath & strFilename)[/SIZE]
[SIZE=2][COLOR=teal]'[/COLOR][/SIZE]
[SIZE=2][COLOR=teal]' do your code with the workbook[/COLOR][/SIZE]
[SIZE=2][COLOR=teal]'[/COLOR][/SIZE]
 
[SIZE=2][COLOR=teal]' save and close it[/COLOR][/SIZE]
[SIZE=2][COLOR=teal]'wbkTemp.Close True[/COLOR][/SIZE]
 
[SIZE=2]strFilename = Dir[/SIZE]
[SIZE=2]Loop[/SIZE]
 
[SIZE=2]End Sub[/SIZE]

 

[warpedone]

@ GTO - I was getting a runtime error at the command: With Application.Filesearch

@ Trevor - thanks man...this looks like something I can work with.

 

[warpedone]

Got it....finally - many thanks guys.
I love this board!

 

[Trevor G]

Pleased to read you have a working solution.

 

[Jaafar Tribak]

application.filesearch has abandoned me.