Macro working well in Excel 97 but not in excel 2003
 

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

Macro working well in Excel 97 but not in excel 2003
 

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)


"yvautrin" wrote in message
...
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

Macro working well in Excel 97 but not in excel 2003
 

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

Macro working well in Excel 97 but not in excel 2003
 

where?

--

HTH

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


"yvautrin" wrote in message
...
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

Macro working well in Excel 97 but not in excel 2003
 

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

Macro working well in Excel 97 but not in excel 2003
 

Yes, but which line of code gives the error?

--

HTH

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


"yvautrin" wrote in message
...
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

Macro working well in Excel 97 but not in excel 2003
 

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

Macro working well in Excel 97 but not in excel 2003
 

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)


"yvautrin" wrote in message
...
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

Macro working well in Excel 97 but not in excel 2003
 

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

Macro working well in Excel 97 but not in excel 2003
 

Try declaring nbFichier as a long.

--

HTH

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


"yvautrin" wrote in message
...
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

Macro working well in Excel 97 but not in excel 2003
 

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....