comment lire les données à partir d'une feuille de calcul excel de façon dynamique à l'aide de VBA 2003

Votre problème n'est pas vraiment VBA pour Excel, le problème est que c'est une poubelle de la source de données. Si les blocs peuvent se déplacer et être dans n'importe quel ordre, vous n'avez vraiment pas de moyen facile de dire où ils seront et quelle quantité de données est en eux. Cela étant le cas, vous feriez beaucoup mieux de poser les questions:

• La source de données doivent être dans ce format; et
• Est-il un moyen que nous pouvons faire mieux?

("Mieux" = plus structuré, plus prévisible.)

Aussi que vous êtes de trop réfléchir en termes de chaque ligne, un record. Chaque bloc est tout à fait distinct de celui des autres et puisque chacun est unique "enregistrement d'en-tête" (en cours de mois), je serais enclin à traiter chaque bloc de la tour plutôt que d'essayer de sauter de l'un à l'autre comme votre exemple de code essaie de faire.

Ce qui suit devrait vous donner assez d'une mise à la terre pour être en mesure de naviguer à travers une feuille de calcul Excel. C'est juste quelque chose que j'ai buter rapidement et n'ont pas de balles de l'épreuve même si je n'ai tester avec une maquette de la feuille dans votre deuxième illustration et il a fait un travail. Il devrait être suffisant pour aider à vous mettre sur la bonne voie, mais j'ai de nouveau souligner... ce que vous avez n'est pas une véritable source de données. C'est un rapport qui doit être presque arbitrairement analysée. Vous avez besoin de voir si cela peut être abordés avant de faire quoi que ce soit.

Sub DemonstrateReadingFromExcel()
'Every cell in Excel is a range.
'A range can also be a collection of cells.
'Ranges have properties that you can query. More importantly
'you can redefine a range by offsetting it from
'your current range, which makes it easy to step through a block.
Dim rng_Month As Excel.Range
Dim rng_Data As Excel.Range
'Let's define some string variables that you can use to assign
'to your recordset's fields.
Dim s_Month As String
Dim s_Product As String
Dim s_Type As String
Dim s_Value As String
Dim l_RowCurrent As Long
Dim l_RowLastType As Long
Dim l_ColumnOfMonth As Long
'We have to start with the cell containing the month.
'Rather than reading row by row, you'd be better off
'reading a whole block at a time.
'Your big problem will be telling WHERE the cell containing
'that month is and for that reason I think you need to seriously
'look at WHY the data is in the format that it is and whether
'you can actually use a much more structured data source.
'For now though let's pretend that you have some magic way of knowing
'where the range is and assign it to a range variable.
'ActiveSheet is a reference to the active worksheet but just as you
'can use a range variable to store a reference to a range,
'you can use a worksheet variable to store a reference to a worksheet
'(even one which is not the active sheet) if you want to.
'I'm only using ActiveSheet for convenience.
'You need to use the Set statement because you're assigning an object.
Set rng_Month = ActiveSheet.Range("C2")
'Ranges have properties like their column number.
'We already know the column number for this range but let's
'assume that we don't in a more general solution.
l_ColumnOfMonth = rng_Month.Column
'Now let's check that the range is valid.
'Don't worry about what the number means,
'just look at the error description.
'If this is True then there must be something wrong with the range.
If l_ColumnOfMonth < 2 Then
Err.Raise vbObjectError + 20000, , "There are no columns to the left of the month. " _
& "The range is invalid."
End If
'Now let's find out where the last Type entry occurs in the current
'block. We go up from the bottom of the column to do that.
'In this case we're passing the row and column to the Cells
'property. This defines a range for us representing the bottom
'of the Type column. Using End(xlUp) is the same as pressing the
'[End] key then the [Up arrow] key. It takes us to the last
'populated row, which we read the .Row property of.
l_RowLastType = ActiveSheet.Cells( _
ActiveSheet.Rows.Count, l_ColumnOfMonth).End(xlUp).Row
'If this is the same as the Month's own row, there's no data
'in that block.
If l_RowLastType = rng_Month.Row Then
Err.Raise vbObjectError + 20000, , "There are no Type entries in this block. "
End If
'So we've checked that the range looks OK.
'Before we proceed, let's store the month for this block.
'We just get the Value property of the range.
s_Month = rng_Month.Value
'We know that the first product should be 3 rows down and
'one row to the left of the month, so let's just keep looping
'through and reading the values for each row in the block
'until we reach the end of it. We know the end because
'we have its row stored in the l_RowLastType variable.
Set rng_Data = rng_Month.Offset(3, -1)
'Let's get the name of the first product.
s_Product = rng_Data.Value
'If that's nothing, there's a problem.
If s_Product = "" Then
Err.Raise vbObjectError + 20000, , "No valid product in the expected location. "
End If
'Now let's loop through each row.
For l_RowCurrent = rng_Month.Row + 3 To l_RowLastType
'Let's look at another way that we can get a reference to
'a range; by using the Cells property of the sheet.
'For that we specify the row number and column number.
Set rng_Data = ActiveSheet.Cells(l_RowCurrent, rng_Month.Column - 1)
'We know that there won't be a product on each row,
'so if there isn't one we just use the previous one.
'Otherwise we assign the new product.
If rng_Data.Value <> "" Then
s_Product = rng_Data.Value
End If
'Now let's get the type, which is offset 0 rows, 1 column
'to the right of the product.
s_Type = rng_Data.Offset(0, 1)
'If that's a blank cell (like row 8 in your
'second example we won't do anything else.
'We only proceed if it's populated.
If s_Type <> "" Then
s_Value = rng_Data.Offset(0, 2)
'Now at this point you have gathered all of your values
'into variables, and can feed them to your recordset.
'In this case though we'll just output
'a messagebox.
MsgBox "Row " & rng_Data.Row & " is for month " & s_Month _
& ", product " & s_Product & ", Type " & s_Type _
& ", Value " & s_Value
End If
Next
ExitPoint:
On Error Resume Next
Set rng_Month = Nothing
On Error GoTo 0
Exit Sub
ErrorHandler:
MsgBox "Error " & Err.Number & vbCrLf & Err.Description
Resume ExitPoint
End Sub