Excel 2003/2007 macro problem

P

pjoub06

Senior Member
Joined
Aug 4, 2007
Messages
575
Reaction score
2
Location
Cape Town
Hey, guys I am having a problem with a macro and was hoping you guys could find the problem. This macro worked in excel 2003 but now that I'm trying it in excel 2007 it is giving an error.


Sub AddFiles()

Dim rng As Range
Dim lastRow As Integer

reportfile = ActiveWorkbook.Name
Worksheets(1).Activate
Range("A1").Activate
'
'Kopieer lêers oor
With Application.FileSearch
.LookIn = "C:\Users\Pjoub06\Documents\Greenplan\Report\Excel Results + Graphs\Results"
.Filename = "*.csv"

If .Execute > 0 Then 'workbook exists
For i = 1 To .FoundFiles.Count

Workbooks.OpenText Filename:=.FoundFiles(i), DataType:=xlDelimited, comma:=True

wbText = ActiveWorkbook.Name
Worksheets(1).Activate
Set rng = Range("A1").CurrentRegion
rng.Copy
Windows(reportfile).Activate
Worksheets(i).Activate
ActiveCell.PasteSpecial
Worksheets(i).Name = Left(wbText, Len(wbText) - 4)
Worksheets.Add After:=Worksheets(i)
Windows(wbText).Activate
Application.CutCopyMode = False
Workbooks(wbText).Close SaveChanges:=False

Next i
Else 'there is not a workbook'
MsgBox "no workbook"
End If
End With

Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub

The red text is where the error is occuring. The idea of the macro is to import all the files into a single one (using multiple spreadsheets however) so that I can draw graphs/chart with the information. Plz help (btw I'm noob at this)
 
thanx for the reply...could be. My friends mom wrote it for me so you could be right. Is that like including libraries in c?
 
Are you sure the path is correct, right below the red line?

This looks like a horrible version of VB (and VB is horrible already)
 
pjoub06 said:
thanx for the reply...could be. My friends mom wrote it for me so you could be right. Is that like including libraries in c?
Click to expand...

Not sure if its that, or it could be an Excel addin called something like VBA analysis toolpack.
Whatever it is, it has something to do with "FileSearch".

Google "Application.FileSearch" and see what comes up.

P.S. That's not a noob macro. Piry I don't have office at home.
 
ok ill google it and report back on my progress...I'm kinda keen to learn VBA because it looks to be quite powerful!(lame language but excel is my friend)
 
Have a look at this:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=450752&SiteID=1

I think Application.FileSearch has been depreciated, none the less post number 5 seems a solution (looks more akin to VB.NET). Try adapt to your code (If you've done C it should be a walk in the park). I don't have M$ office nor the desire to install it so can't test & fix it for you sorry.
 
yes i have changed to filelocation to c:\results and still the same error.Oms they removed the function from office 2007...wtf?Now I'm gonna have to figure out this other crap...unless someone can rewrite it for me :D?
 
Slightly different approach...

Sub AddFiles()

Dim rng As Range
Dim lastRow As Integer
Dim sFil As String
Dim sPath As String

reportfile = ActiveWorkbook.Name
Worksheets(1).Activate
Range("A1").Activate

'Cpoy files
sPath = "C:\Results" 'location of files
ChDir sPath
sFil = Dir("*.csv") 'change or add formats


If .Execute > 0 Then 'workbook exists
For i = 1 To .FoundFiles.Count

Workbooks.OpenText filename:=.FoundFiles(i), DataType:=xlDelimited, comma:=True

wbText = ActiveWorkbook.Name
Worksheets(1).Activate
Set rng = Range("A1").CurrentRegion
rng.Copy
Windows(reportfile).Activate
Worksheets(i).Activate
ActiveCell.PasteSpecial
Worksheets(i).Name = Left(wbText, Len(wbText) - 4)
Worksheets.Add After:=Worksheets(i)
Windows(wbText).Activate
Application.CutCopyMode = False
Workbooks(wbText).Close SaveChanges:=False

Next i
Else 'there is not a workbook'
MsgBox "no workbook"
End If
End With

Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub

The if statment now doesnt correlate with the function I used. How do I fix this?
 
Basically I cant figure out how to do this loop now...should I just use a normal loop function?
 
Their method copies files, the method the kind lady wrote for you copies data from within the files. So in that regard it's completely different but to fix the problem you had earlier try (Note I am talking about your first piece of code, the code she wrote):

Replace THIS:
Code: 
With Application.FileSearch
.LookIn = "C:\Users\Pjoub06\Documents\Greenplan\Report\E xcel Results + Graphs\Results"
.Filename = "*.csv"

If .Execute > 0 Then 'workbook exists
For i = 1 To .FoundFiles.Count

With This:

Code: 
With Access.Application.FileSearch
    .NewSearch
    .LookIn = "C:\Users\Pjoub06\Documents\Greenplan\Report\E xcel Results + Graphs\Results"
    .SearchSubFolders = False 'This depends on you
    .filename = "*.csv"

If .Execute > 0 Then 'workbook exists
For i = 1 To .FoundFiles.Count

Does that still give you the same error?

If so we can try:
(just match this with the With from above :p)

Code: 
Dim hack as new Access.Application
With hack.FileSearch

I've only ever used VB.Net and since I can't test code I'm just winging it, so I can't really say what is wrong without the exact error message ;)
 
filesearch has been destroyed to the point of no return I'm afraid. Get error 424 (object required)
 
Does the it need to recurse directories? (IE. get all files in subdirectories also?)
 
no its only in that specific folder. ie. c:\results or whatever. There are no sub folders actually
 
Eh, this is a bit hard to do without a debugger (ie. Office), best ask the lady if she could rewrite it using: http://advisor.com/doc/16279 one of those methods, I tried the dir method but I'm not exactly sure how you use the variables since there's a lot that's different from VB.Net (which is the only VB I've ever used)

Or if someone else has Office installed or knows how perhaps they can help you.

I can work it out if I had office, but I don't (I only use OpenOffice.org).
 
I will ask her but she is going on holiday tomorrow...So I hope I can catch her before she goes. Otherwise I will just cry!
 
You must log in or register to reply here.
Back
Top
Sign up to the MyBroadband newsletter
X