Macro working well in Excel 97 but not in excel 2003

Y

yvautrin

Hi everybody.

I got a macro to batch process multiple files, this macro works well in
Excel 97, but when trying to run it under excel 2003, I got the following
error message :

"Compile error - Wend without a while"

Any Idea?

Thanks fr your help

Here is the macro :


Sub Macro1()
'
' Macro1 Macro
' Macro enregistrée le 18/04/05 par bougeard_gregory '
chemin = InputBox("Entrez le chemin du dossier contenant les fichiers")
yann3 (chemin)
'
End Sub

Sub yann3(chemin As String)
'
' yann3 Macro
'

'
Dim cpt As Integer
Dim nbFichier As Integer
Dim numLigneConcatene As Integer

nbFichier = 0
numLigneConcatene = 0
'chemin = InputBox("Entrez le chemin du dossier contenant les fichiers")
ChDir chemin Fichier = Dir(chemin) While Fichier <> ""

Workbooks.OpenText Filename:=Fichier, Origin:=xlWindows, _
StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False,
Comma:=True, _
Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2,
1), Array( _
3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1))

nbFichier = nbFichier + 1

Rows("1:1").Select
Selection.Delete Shift:=xlUp
Rows("1:1").Select
Selection.Delete Shift:=xlUp
Rows("1:1").Select
Selection.Delete Shift:=xlUp

If numLigneConcatene <> 0 Then
Rows("1:1").Select
Selection.Delete Shift:=xlUp
End If

cpt = 1
Range("G1").Select
Do While (ActiveCell.Text <> "")

Cells(cpt, 8).Value = ActiveWorkbook.Name
cpt = cpt + 1
Cells(cpt, 7).Select

Loop

ActiveWorkbook.Save

'go to next file
Fichier = Dir

Wend

'merge data into a single file
Shell ("command.com /C copy " + chemin + "*.txt" + chemin +
"monfichier.txt")
End Sub
 
B

Bob Phillips

You seem to have three lines in one in

ChDir chemin Fichier = Dir(chemin) While Fichier <> ""

Change to

ChDir chemin
Fichier = Dir(chemin)
While Fichier <> ""


--

HTH

RP
(remove nothere from the email address if mailing direct)
 
Y

yvautrin

Hi Bob,

I did the suggested changes, and now I got an error message "400"

here is the macro including the chages :


Sub Macro1()
'
'chemin = InputBox("Entrez le chemin du dossier contenant les fichiers")
macro2 (chemin)
'
End Sub

Sub macro2(chemin As String)
'
' yann3 Macro
'

'
Dim cpt As Integer
Dim nbFichier As Integer
Dim numLigneConcatene As Integer

nbFichier = 0
numLigneConcatene = 0
chemin = InputBox("Entrez le chemin du dossier contenant les fichiers")
ChDir chemin
Fichier = Dir(chemin)
While Fichier <> ""

Workbooks.OpenText Filename:=Fichier, Origin:=xlWindows, _
StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False,
Comma:=True, _
Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2,
1), Array( _
3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1))

nbFichier = nbFichier + 1

Rows("1:1").Select
Selection.Delete Shift:=xlUp
Rows("1:1").Select
Selection.Delete Shift:=xlUp
Rows("1:1").Select
Selection.Delete Shift:=xlUp

If numLigneConcatene <> 0 Then
Rows("1:1").Select
Selection.Delete Shift:=xlUp
End If

cpt = 1
Range("G1").Select
Do While (ActiveCell.Text <> "")

Cells(cpt, 8).Value = ActiveWorkbook.Name
cpt = cpt + 1
Cells(cpt, 7).Select

Loop

ActiveWorkbook.Save

'passe au fichier suivant
Fichier = Dir

Wend

'on copie les donnees dans un seul fichier
Shell ("command.com /C copy " + chemin + "*.txt" + chemin +
"monfichier.txt")
End Sub



thanks for your help
Yann
 
Y

yvautrin

Now, with the macro herebelow, it imports the first file of the defined
folder, and stops with the following error "Run Time error 1004" Qpplication
defined or object defined error


macro :

Sub Macro1()
'
' Macro1 Macro
' Macro enregistrée le 18/04/05 par bougeard_gregory '
'1- On ouvre les fichiers et les convertits chemin = InputBox("Entrez le
chemin du dossier contenant les fichiers")
yann3 (chemin)
'
End Sub

Sub yann3(chemin As String)
'
' yann3 Macro
'

'
Dim cpt As Integer
Dim nbFichier As Integer
Dim numLigneConcatene As Integer

nbFichier = 0
numLigneConcatene = 0
chemin = InputBox("Entrez le chemin du dossier contenant les fichiers")
ChDir chemin
Fichier = Dir(chemin)
While Fichier <> ""

Workbooks.OpenText Filename:=Fichier, Origin:=xlWindows, _
StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False,
Comma:=True, _
Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2,
1), Array( _
3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1))

nbFichier = nbFichier + 1

Rows("1:1").Select
Selection.Delete Shift:=xlUp
Rows("1:1").Select
Selection.Delete Shift:=xlUp
Rows("1:1").Select
Selection.Delete Shift:=xlUp

If numLigneConcatene <> 0 Then
Rows("1:1").Select
Selection.Delete Shift:=xlUp
End If

cpt = 1
Range("G1").Select
Do While (ActiveCell.Text <> "")

Cells(cpt, 8).Value = ActiveWorkbook.Name
cpt = cpt + 1
Cells(cpt, 7).Select

Loop

ActiveWorkbook.Save

'passe au fichier suivant
Fichier = Dir
Wend

'on copie les donnees dans un seul fichier
Shell ("command.com /C copy " + chemin + "*.txt" + chemin +
"monfichier.txt")
End Sub
 
B

Bob Phillips

Yes, but which line of code gives the error?

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
Y

yvautrin

I don't know how to find this ? Is there a way to have it displayed in the
macro editor?

Sorry, but I am a really newbie in VB.

thanks for your help

Yann
 
B

Bob Phillips

When it fails, you usually get a dialog box that says End, Debug, Help.
Click the Debug button, and you should be taken to the VBE on the offending
line.

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
Y

yvautrin

I don't get the choices you mention, just "ok/help", so I added breakpoints
in the code ( I don't know if it is the right way to do it but...)

I get the error 1004 when running this line :


nbFichier = nbFichier + 1
 
B

Bob Phillips

Try declaring nbFichier as a long.

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
Y

yvautrin

I just tried with this change :

Dim cpt As Integer
Dim nbFichier As Long
Dim numLigneConcatene As Integer


I get the same error message at the same stop point....
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

[PB] Tableau croisé dynamique et excel 2011 2
Word macro that can call cells out of Excel for a find and replace 0
Complie Syntax Error 6
Macro Error 6
Speeding up a delete rows Macro 7
Problems with a Run-time error of 1004.... 7
Different Results from the Same Macro 3
Macro Needed 1

Top