Je cherche à mettre à jour automatiquement un fichier Excel avec un rapport
Business Objects exporté sous Excel. Le problème c'est que lorsque je lance
ma macro j'ai l'erreur "Compile Error: Next Without For" alors que j'ai bien
un "For" déclaré avant le Next...
Quelqu'un a-t-il une solution?
Le code se trouve ci-dessous
Merci d'avance
Private Sub Workbook_Open()
Application.ScreenUpdating = False
'Checks if the file is on Read Only mode. If it is, the user receives a
notification and the macro stops
If ThisWorkbook.ReadOnly = True Then
MsgBox ("The file is currently in Read Only mode. The macro will not
be executed until you have full access to the file.")
Exit Sub
Else
'If it is not, it checks if the source file exists. If not, the user
receives a notification and the macro stops
If CreateObject("Scripting.FileSystemObject").FileExists _
(Environ("HOMEDRIVE") & Environ("HOMEPATH") &
"\Desktop\BO-OL.XLS") = False Then
MsgBox ("The source file wasn't found. The macro will not be
executed")
Exit Sub
Else
'If both requirements are satisfied, then the macro begins
'We begin by opening the source file
Workbooks.Open Filename:= _
Environ("HOMEDRIVE") & Environ("HOMEPATH") &
"\Desktop\BO-OL.XLS"
'The variable for the loop depends on the number of line there is in the
source file
For i = 3 To Workbooks("BO-OL.XLS").Sheets("Report
1").Range("A1").Value + 2
'If the line of our Order List correspond to the one in the source file, it
goes to the next line
If Workbooks("BO-OL.XLS").Sheets("Report 1").Cells(i, 1) =
ThisWorkbook.Sheets("2009").Cells(i, 1) Then
Next i
Else
'Otherwise we copy the entire line of the source file and paste it at the
same place in our Order List
Workbooks("BO-OL.XLS").Sheets("Report 1").Rows(i).Copy
ThisWorkbook.Sheets("2009").Rows(i).Paste
Next i
Workbooks("BO-OL.XLS").Sheets("Report 1").Columns(2).Copy
ThisWorkbook.Sheets("2009").Columns(2).Paste
Workbooks("BO-OL.XLS").Close
End If
End If
End If
Cette action est irreversible, confirmez la suppression du commentaire ?
Signaler le commentaire
Veuillez sélectionner un problème
Nudité
Violence
Harcèlement
Fraude
Vente illégale
Discours haineux
Terrorisme
Autre
ElXav
Bonjour,
Sortir le "Next" de la structure If. Il ne doit y avoir qu'un seul Next par For:
Soit
For...
If ... Then Else Endif
Next
et donc:
For i = 3 To Workbooks("BO-OL.XLS").Sheets("Report 1").Range("A1").Value + 2 'If the line of our Order List correspond to the one in the source file, it goes to the next line If Workbooks("BO-OL.XLS").Sheets("Report 1").Cells(i, 1) = ThisWorkbook.Sheets("2009").Cells(i, 1) Then Else 'Otherwise we copy the entire line of the source file and paste it at the same place in our Order List Workbooks("BO-OL.XLS").Sheets("Report 1").Rows (i).Copy ThisWorkbook.Sheets("2009").Rows(i).Paste ENDIF Next i Workbooks("BO-OL.XLS").Sheets("Report 1").Columns(2).Copy ThisWorkbook.Sheets("2009").Columns(2).Paste Workbooks("BO-OL.XLS").Close End If End If
Cordialement.
On 20 juil, 14:09, YeTi wrote:
Bonjour à tous,
Je cherche à mettre à jour automatiquement un fichier Excel avec un r apport Business Objects exporté sous Excel. Le problème c'est que lorsque je lance ma macro j'ai l'erreur "Compile Error: Next Without For" alors que j'ai b ien un "For" déclaré avant le Next...
Quelqu'un a-t-il une solution?
Le code se trouve ci-dessous
Merci d'avance
Private Sub Workbook_Open()
Application.ScreenUpdating = False
'Checks if the file is on Read Only mode. If it is, the user receives a notification and the macro stops If ThisWorkbook.ReadOnly = True Then MsgBox ("The file is currently in Read Only mode. The mac ro will not be executed until you have full access to the file.") Exit Sub Else
'If it is not, it checks if the source file exists. If not, the user receives a notification and the macro stops If CreateObject("Scripting.FileSystemObject").FileExists _ (Environ("HOMEDRIVE") & Environ("HOMEPATH") & "DesktopBO-OL.XLS") = False Then MsgBox ("The source file wasn't found. The macro will not be executed") Exit Sub Else
'If both requirements are satisfied, then the macro begins 'We begin by opening the source file Workbooks.Open Filename:= _ Environ("HOMEDRIVE") & Environ("HOMEPATH" ) & "DesktopBO-OL.XLS"
'The variable for the loop depends on the number of line there is in the source file For i = 3 To Workbooks("BO-OL.XLS").Sheets("Rep ort 1").Range("A1").Value + 2 'If the line of our Order List correspond to the one in the source file, it goes to the next line If Workbooks("BO-OL.XLS").Sheets("Report 1").Cell s(i, 1) = ThisWorkbook.Sheets("2009").Cells(i, 1) Then Next i Else 'Otherwise we copy the entire line of the source file and paste it at the same place in our Order List Workbooks("BO-OL.XLS").Sheets("Report 1") .Rows(i).Copy ThisWorkbook.Sheets("2009").Rows(i).Paste Next i Workbooks("BO-OL.XLS").Sheets("Report 1") .Columns(2).Copy ThisWorkbook.Sheets("2009").Columns(2).Pa ste Workbooks("BO-OL.XLS").Close End If End If End If
Application.ScreenUpdating = True
End Sub
Bonjour,
Sortir le "Next" de la structure If.
Il ne doit y avoir qu'un seul Next par For:
Soit
For...
If ... Then
Else
Endif
Next
et donc:
For i = 3 To Workbooks("BO-OL.XLS").Sheets("Report
1").Range("A1").Value + 2
'If the line of our Order List correspond to the one in the source
file, it
goes to the next line
If Workbooks("BO-OL.XLS").Sheets("Report 1").Cells(i, 1)
= ThisWorkbook.Sheets("2009").Cells(i, 1) Then
Else
'Otherwise we copy the entire line of the source file and paste it at
the
same place in our Order List
Workbooks("BO-OL.XLS").Sheets("Report 1").Rows
(i).Copy
ThisWorkbook.Sheets("2009").Rows(i).Paste
ENDIF
Next i
Workbooks("BO-OL.XLS").Sheets("Report 1").Columns(2).Copy
ThisWorkbook.Sheets("2009").Columns(2).Paste
Workbooks("BO-OL.XLS").Close
End If
End If
Cordialement.
On 20 juil, 14:09, YeTi <Y...@discussions.microsoft.com> wrote:
Bonjour à tous,
Je cherche à mettre à jour automatiquement un fichier Excel avec un r apport
Business Objects exporté sous Excel. Le problème c'est que lorsque je lance
ma macro j'ai l'erreur "Compile Error: Next Without For" alors que j'ai b ien
un "For" déclaré avant le Next...
Quelqu'un a-t-il une solution?
Le code se trouve ci-dessous
Merci d'avance
Private Sub Workbook_Open()
Application.ScreenUpdating = False
'Checks if the file is on Read Only mode. If it is, the user receives a
notification and the macro stops
If ThisWorkbook.ReadOnly = True Then
MsgBox ("The file is currently in Read Only mode. The mac ro will not
be executed until you have full access to the file.")
Exit Sub
Else
'If it is not, it checks if the source file exists. If not, the user
receives a notification and the macro stops
If CreateObject("Scripting.FileSystemObject").FileExists _
(Environ("HOMEDRIVE") & Environ("HOMEPATH") &
"DesktopBO-OL.XLS") = False Then
MsgBox ("The source file wasn't found. The macro will not be
executed")
Exit Sub
Else
'If both requirements are satisfied, then the macro begins
'We begin by opening the source file
Workbooks.Open Filename:= _
Environ("HOMEDRIVE") & Environ("HOMEPATH" ) &
"DesktopBO-OL.XLS"
'The variable for the loop depends on the number of line there is in the
source file
For i = 3 To Workbooks("BO-OL.XLS").Sheets("Rep ort
1").Range("A1").Value + 2
'If the line of our Order List correspond to the one in the source file, it
goes to the next line
If Workbooks("BO-OL.XLS").Sheets("Report 1").Cell s(i, 1) =
ThisWorkbook.Sheets("2009").Cells(i, 1) Then
Next i
Else
'Otherwise we copy the entire line of the source file and paste it at the
same place in our Order List
Workbooks("BO-OL.XLS").Sheets("Report 1") .Rows(i).Copy
ThisWorkbook.Sheets("2009").Rows(i).Paste
Next i
Workbooks("BO-OL.XLS").Sheets("Report 1") .Columns(2).Copy
ThisWorkbook.Sheets("2009").Columns(2).Pa ste
Workbooks("BO-OL.XLS").Close
End If
End If
End If
Sortir le "Next" de la structure If. Il ne doit y avoir qu'un seul Next par For:
Soit
For...
If ... Then Else Endif
Next
et donc:
For i = 3 To Workbooks("BO-OL.XLS").Sheets("Report 1").Range("A1").Value + 2 'If the line of our Order List correspond to the one in the source file, it goes to the next line If Workbooks("BO-OL.XLS").Sheets("Report 1").Cells(i, 1) = ThisWorkbook.Sheets("2009").Cells(i, 1) Then Else 'Otherwise we copy the entire line of the source file and paste it at the same place in our Order List Workbooks("BO-OL.XLS").Sheets("Report 1").Rows (i).Copy ThisWorkbook.Sheets("2009").Rows(i).Paste ENDIF Next i Workbooks("BO-OL.XLS").Sheets("Report 1").Columns(2).Copy ThisWorkbook.Sheets("2009").Columns(2).Paste Workbooks("BO-OL.XLS").Close End If End If
Cordialement.
On 20 juil, 14:09, YeTi wrote:
Bonjour à tous,
Je cherche à mettre à jour automatiquement un fichier Excel avec un r apport Business Objects exporté sous Excel. Le problème c'est que lorsque je lance ma macro j'ai l'erreur "Compile Error: Next Without For" alors que j'ai b ien un "For" déclaré avant le Next...
Quelqu'un a-t-il une solution?
Le code se trouve ci-dessous
Merci d'avance
Private Sub Workbook_Open()
Application.ScreenUpdating = False
'Checks if the file is on Read Only mode. If it is, the user receives a notification and the macro stops If ThisWorkbook.ReadOnly = True Then MsgBox ("The file is currently in Read Only mode. The mac ro will not be executed until you have full access to the file.") Exit Sub Else
'If it is not, it checks if the source file exists. If not, the user receives a notification and the macro stops If CreateObject("Scripting.FileSystemObject").FileExists _ (Environ("HOMEDRIVE") & Environ("HOMEPATH") & "DesktopBO-OL.XLS") = False Then MsgBox ("The source file wasn't found. The macro will not be executed") Exit Sub Else
'If both requirements are satisfied, then the macro begins 'We begin by opening the source file Workbooks.Open Filename:= _ Environ("HOMEDRIVE") & Environ("HOMEPATH" ) & "DesktopBO-OL.XLS"
'The variable for the loop depends on the number of line there is in the source file For i = 3 To Workbooks("BO-OL.XLS").Sheets("Rep ort 1").Range("A1").Value + 2 'If the line of our Order List correspond to the one in the source file, it goes to the next line If Workbooks("BO-OL.XLS").Sheets("Report 1").Cell s(i, 1) = ThisWorkbook.Sheets("2009").Cells(i, 1) Then Next i Else 'Otherwise we copy the entire line of the source file and paste it at the same place in our Order List Workbooks("BO-OL.XLS").Sheets("Report 1") .Rows(i).Copy ThisWorkbook.Sheets("2009").Rows(i).Paste Next i Workbooks("BO-OL.XLS").Sheets("Report 1") .Columns(2).Copy ThisWorkbook.Sheets("2009").Columns(2).Pa ste Workbooks("BO-OL.XLS").Close End If End If End If
Application.ScreenUpdating = True
End Sub
LSteph
Bonjour, Tu as deux next i pour un seul for sans rien d'autre entre then et else en plus ils sont imbriqués à cheval dans le if:
Then Next i '1 ici Else 'Otherwise ..Paste Next i ' et 1 second là Workbooks...Close End If
..amha faudrait structurer uniquement le If dans le For ou selon ce qui est voulu autrement mais pas une partie enchevêtrée dans l'autre. ...a priori...
-- lSteph ... On 20 juil, 14:09, YeTi wrote:
Bonjour à tous,
Je cherche à mettre à jour automatiquement un fichier Excel avec un r apport Business Objects exporté sous Excel. Le problème c'est que lorsque je lance ma macro j'ai l'erreur "Compile Error: Next Without For" alors que j'ai b ien un "For" déclaré avant le Next...
Quelqu'un a-t-il une solution?
Le code se trouve ci-dessous
Merci d'avance
Private Sub Workbook_Open()
Application.ScreenUpdating = False
'Checks if the file is on Read Only mode. If it is, the user receives a notification and the macro stops If ThisWorkbook.ReadOnly = True Then MsgBox ("The file is currently in Read Only mode. The mac ro will not be executed until you have full access to the file.") Exit Sub Else
'If it is not, it checks if the source file exists. If not, the user receives a notification and the macro stops If CreateObject("Scripting.FileSystemObject").FileExists _ (Environ("HOMEDRIVE") & Environ("HOMEPATH") & "DesktopBO-OL.XLS") = False Then MsgBox ("The source file wasn't found. The macro will not be executed") Exit Sub Else
'If both requirements are satisfied, then the macro begins 'We begin by opening the source file Workbooks.Open Filename:= _ Environ("HOMEDRIVE") & Environ("HOMEPATH" ) & "DesktopBO-OL.XLS"
'The variable for the loop depends on the number of line there is in the source file For i = 3 To Workbooks("BO-OL.XLS").Sheets("Rep ort 1").Range("A1").Value + 2 'If the line of our Order List correspond to the one in the source file, it goes to the next line If Workbooks("BO-OL.XLS").Sheets("Report 1").Cell s(i, 1) = ThisWorkbook.Sheets("2009").Cells(i, 1) Then Next i Else 'Otherwise we copy the entire line of the source file and paste it at the same place in our Order List Workbooks("BO-OL.XLS").Sheets("Report 1") .Rows(i).Copy ThisWorkbook.Sheets("2009").Rows(i).Paste Next i Workbooks("BO-OL.XLS").Sheets("Report 1") .Columns(2).Copy ThisWorkbook.Sheets("2009").Columns(2).Pa ste Workbooks("BO-OL.XLS").Close End If End If End If
Application.ScreenUpdating = True
End Sub
Bonjour,
Tu as deux next i pour un seul for sans rien d'autre entre then et
else
en plus ils sont imbriqués à cheval dans le if:
Then
Next i '1 ici
Else
'Otherwise ..Paste
Next i ' et 1 second là
Workbooks...Close
End If
..amha faudrait structurer uniquement le If dans le For
ou selon ce qui est voulu autrement
mais pas une partie enchevêtrée dans l'autre. ...a priori...
--
lSteph
...
On 20 juil, 14:09, YeTi <Y...@discussions.microsoft.com> wrote:
Bonjour à tous,
Je cherche à mettre à jour automatiquement un fichier Excel avec un r apport
Business Objects exporté sous Excel. Le problème c'est que lorsque je lance
ma macro j'ai l'erreur "Compile Error: Next Without For" alors que j'ai b ien
un "For" déclaré avant le Next...
Quelqu'un a-t-il une solution?
Le code se trouve ci-dessous
Merci d'avance
Private Sub Workbook_Open()
Application.ScreenUpdating = False
'Checks if the file is on Read Only mode. If it is, the user receives a
notification and the macro stops
If ThisWorkbook.ReadOnly = True Then
MsgBox ("The file is currently in Read Only mode. The mac ro will not
be executed until you have full access to the file.")
Exit Sub
Else
'If it is not, it checks if the source file exists. If not, the user
receives a notification and the macro stops
If CreateObject("Scripting.FileSystemObject").FileExists _
(Environ("HOMEDRIVE") & Environ("HOMEPATH") &
"DesktopBO-OL.XLS") = False Then
MsgBox ("The source file wasn't found. The macro will not be
executed")
Exit Sub
Else
'If both requirements are satisfied, then the macro begins
'We begin by opening the source file
Workbooks.Open Filename:= _
Environ("HOMEDRIVE") & Environ("HOMEPATH" ) &
"DesktopBO-OL.XLS"
'The variable for the loop depends on the number of line there is in the
source file
For i = 3 To Workbooks("BO-OL.XLS").Sheets("Rep ort
1").Range("A1").Value + 2
'If the line of our Order List correspond to the one in the source file, it
goes to the next line
If Workbooks("BO-OL.XLS").Sheets("Report 1").Cell s(i, 1) =
ThisWorkbook.Sheets("2009").Cells(i, 1) Then
Next i
Else
'Otherwise we copy the entire line of the source file and paste it at the
same place in our Order List
Workbooks("BO-OL.XLS").Sheets("Report 1") .Rows(i).Copy
ThisWorkbook.Sheets("2009").Rows(i).Paste
Next i
Workbooks("BO-OL.XLS").Sheets("Report 1") .Columns(2).Copy
ThisWorkbook.Sheets("2009").Columns(2).Pa ste
Workbooks("BO-OL.XLS").Close
End If
End If
End If
Bonjour, Tu as deux next i pour un seul for sans rien d'autre entre then et else en plus ils sont imbriqués à cheval dans le if:
Then Next i '1 ici Else 'Otherwise ..Paste Next i ' et 1 second là Workbooks...Close End If
..amha faudrait structurer uniquement le If dans le For ou selon ce qui est voulu autrement mais pas une partie enchevêtrée dans l'autre. ...a priori...
-- lSteph ... On 20 juil, 14:09, YeTi wrote:
Bonjour à tous,
Je cherche à mettre à jour automatiquement un fichier Excel avec un r apport Business Objects exporté sous Excel. Le problème c'est que lorsque je lance ma macro j'ai l'erreur "Compile Error: Next Without For" alors que j'ai b ien un "For" déclaré avant le Next...
Quelqu'un a-t-il une solution?
Le code se trouve ci-dessous
Merci d'avance
Private Sub Workbook_Open()
Application.ScreenUpdating = False
'Checks if the file is on Read Only mode. If it is, the user receives a notification and the macro stops If ThisWorkbook.ReadOnly = True Then MsgBox ("The file is currently in Read Only mode. The mac ro will not be executed until you have full access to the file.") Exit Sub Else
'If it is not, it checks if the source file exists. If not, the user receives a notification and the macro stops If CreateObject("Scripting.FileSystemObject").FileExists _ (Environ("HOMEDRIVE") & Environ("HOMEPATH") & "DesktopBO-OL.XLS") = False Then MsgBox ("The source file wasn't found. The macro will not be executed") Exit Sub Else
'If both requirements are satisfied, then the macro begins 'We begin by opening the source file Workbooks.Open Filename:= _ Environ("HOMEDRIVE") & Environ("HOMEPATH" ) & "DesktopBO-OL.XLS"
'The variable for the loop depends on the number of line there is in the source file For i = 3 To Workbooks("BO-OL.XLS").Sheets("Rep ort 1").Range("A1").Value + 2 'If the line of our Order List correspond to the one in the source file, it goes to the next line If Workbooks("BO-OL.XLS").Sheets("Report 1").Cell s(i, 1) = ThisWorkbook.Sheets("2009").Cells(i, 1) Then Next i Else 'Otherwise we copy the entire line of the source file and paste it at the same place in our Order List Workbooks("BO-OL.XLS").Sheets("Report 1") .Rows(i).Copy ThisWorkbook.Sheets("2009").Rows(i).Paste Next i Workbooks("BO-OL.XLS").Sheets("Report 1") .Columns(2).Copy ThisWorkbook.Sheets("2009").Columns(2).Pa ste Workbooks("BO-OL.XLS").Close End If End If End If