?
Solved

Extract 3 grids to excel in format

Posted on 2012-12-22
7
Medium Priority
?
217 Views
Last Modified: 2012-12-27
Hello all

I have these 3 grids where i would like to extract the data to excel following this process.

In column 0 you have the door letter.

In column 1 from each grid have order groups that link to the door, the P Carrier that link to the door and the Carrier that link to the door.

I would like to extract to excel based on the door letter.

How can i do that?

Name of grids:
MSHFlexgrid1
MSHFlexgrid2
MSHFlexgrid3

Grid look like:
grid

Excel extract result would be like:
 excel result
Thanks again for your help
0
Comment
Question by:Wilder1626
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 11

Author Comment

by:Wilder1626
ID: 38716173
If this is impossible, is it possible to extract the 3 grid in the same excel file but in different spreadsheet?

The sheet could be named by the MSHFlexgrid name.

Thanks again for your help
0
 
LVL 53

Expert Comment

by:Rgonzo1971
ID: 38716991
Hi,

Where is your problem: In getting the data from flexgrid or in organizing it in excel?

Regards
0
 
LVL 11

Author Comment

by:Wilder1626
ID: 38716997
My problem is to organize in Excel and also transferring the data in excel the right way.

Is that possible to do?

How can i do that?

Thanks a lot for your help.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 53

Expert Comment

by:Rgonzo1971
ID: 38717941
Hi,

2 questions:
Is every door ever to be there(are all the doors in every flexgrid)?
Is your data already sorted?

Regards
0
 
LVL 11

Author Comment

by:Wilder1626
ID: 38718399
Hi

Question 1:
All doors are in every MSHFlexgrid.

Question 2:
Yes all MSHFlexgrid are sorted on column 0 (Door column value)


Should i had a 4th grid with all door name only?

Thanks again
0
 
LVL 53

Accepted Solution

by:
Rgonzo1971 earned 2000 total points
ID: 38720375
Hi,

Here is my code, with the file to test it

Sub DoTheJob()


Dim aryFlexGrid1 As Variant
Dim aryFlexGrid2 As Variant
Dim aryFlexGrid3 As Variant
Dim aryDoors As Variant

aryFlexGrid1 = Range("OrderGroup")
aryFlexGrid2 = Range("PCarrier")
aryFlexGrid3 = Range("carrier")
aryDoors = Range("OrderGroup").Resize(Range("OrderGroup").Rows.Count, 1)

Dim dictDoors As Scripting.Dictionary
Set dictDoors = New Scripting.Dictionary
For Each door In aryDoors
    If Not dictDoors.Exists(door) Then dictDoors.Add door, door
Next


Set rngActual = Range("A6")
rngActual.Activate

IdxFlexGrid1Line = 1
IdxFlexGrid2Line = 1
IdxFlexGrid3Line = 1

For Each DoorItem In dictDoors
    rngActual.Value = DoorItem
    myFormatCell rngActual
    IdxDoorLine = 0
    Do While aryFlexGrid1(IdxFlexGrid1Line, 1) = DoorItem
        Set rngTemp = rngActual.Offset(IdxDoorLine, 1)
        rngTemp.Value = aryFlexGrid1(IdxFlexGrid1Line, 2)
        myFormatCell rngTemp
        IdxDoorLine = IdxDoorLine + 1
        IdxFlexGrid1Line = IdxFlexGrid1Line + 1
        If IdxFlexGrid1Line > UBound(aryFlexGrid1) Then Exit Do
    Loop
    IdxDoorLine = 0
    Do While aryFlexGrid2(IdxFlexGrid2Line, 1) = DoorItem
        Set rngTemp = rngActual.Offset(IdxDoorLine, 2)
        rngTemp.Value = aryFlexGrid2(IdxFlexGrid2Line, 2)
        myFormatCell rngTemp
        IdxDoorLine = IdxDoorLine + 1
        IdxFlexGrid2Line = IdxFlexGrid2Line + 1
        If IdxFlexGrid2Line > UBound(aryFlexGrid2) Then Exit Do
    Loop
    IdxDoorLine = 0
    Do While aryFlexGrid3(IdxFlexGrid3Line, 1) = DoorItem
       Set rngTemp = rngActual.Offset(IdxDoorLine, 3)
        rngTemp.Value = aryFlexGrid3(IdxFlexGrid3Line, 2)
        myFormatCell rngTemp
        IdxDoorLine = IdxDoorLine + 1
        IdxFlexGrid3Line = IdxFlexGrid3Line + 1
        If IdxFlexGrid3Line > UBound(aryFlexGrid3) Then Exit Do
    Loop
    intRegionRows = rngActual.CurrentRegion.Rows.Count
    Set rngActual = rngActual.Offset(intRegionRows + 1)
    
Next

Range("A5").Formula = "Door"
Range("B5").Formula = "Order Group"
Range("C5").Formula = "P Carrier"
Range("D5").Formula = "carrier"
Range("A15:D15").Select
With Range("A5:D5")
    With .Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = -0.349986266670736
        .PatternTintAndShade = 0
    End With
    With .Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With .Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With .Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With .Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With .Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlMedium
    End With
End With



End Sub

Sub myFormatCell(ByVal r As Range)
With r
    With .Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With .Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With .Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With .Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlMedium
    End With
End With

End Sub

Open in new window


Regards
Doors-Carrriers.xlsm
0
 
LVL 11

Author Closing Comment

by:Wilder1626
ID: 38723837
Hi Rgonzo1971

I'm seeing this being a excel macro but i would like to do this in VB6

Since i do like the excel macro, here's the point and i will re open a new topic.

Thanks
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Suggested Courses

649 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question