Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
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.... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2007 Macro Help (Excel 2003 not working in 2007) | Excel Discussion (Misc queries) | |||
Excel 2000 macro not working in Excel 2003 | Excel Discussion (Misc queries) | |||
excel 2003 slow when working with excel 2000 files | Excel Discussion (Misc queries) | |||
Macro not working with Excel 2003 | Excel Discussion (Misc queries) | |||
Macro working in Excel 2003; not working in Excel 2000 | Excel Programming |