hexagon logo
Register Sign in
  • Replies 22 replies
  • Subscribers 34 subscribers
  • Views 540 views
  • Users 0 members are here
Options
Related

PC-Dmis to Excel, through PC-Dmis Script

Rploughe
I know there is currently a VB project that runs in Excel during execution that will pull information into the workbook during execution. I decided to venture out and try some of my own approaches with a combination of data I found online here and there.

What I have is a script that can be executed in program, without too much setup and hassle, that will export data into an excel workbook like a print command would. I feel this script offers a little more versatility.

I will not claim ownership of this program. I just tweaked it to fit my needs. So far I have it running, and it works quite well.

I will answer what questions I can, but for the most part I wanted to post it to have as a reference.

It's a long program........
Parents
  • There the monster is finished.

    I can personally say it was fun writing this.

    The first section posted goes through my company directory by a pulled variable and locations to find my end "address"
    the second section posted actually controls all the excel population. Still needs cleaned up.
    The third section is the final part of the program for clean up, and formula crunching.

    If you have any questions, I will answer what I can.

    ---------
    I had some time and decided to play with the excel functions some more. Finding the correct syntax for the "Excel" "WorkbookFunction" was a little annoying but I got it. I also figured out how to properly select a cell range for data crunching.

    Not sure if my formula for Cp, CpK are correct. I did it off of memory real quick.

    So here you go, a working excel output script with examples on excel function/formula implementation. There are other logic statements I have been playing with like cell shading and cell width/height. Once I get a "neat" layout I will post them.
    ------- 
    Dim WidthSet
 WidthSet = xlSheet.Range("A4").Columns.AutoFit()
WidthSet = xlSheet.Cells(RCount - 1, 2).Columns.AutoFit()[SIZE=2][/SIZE]

    ^Controls to AutoSet Cell Width for information display length 
    If xlsheet.Cells(RCount+5,CCount).value > xlsheet.cells(Rcount+2,Ccount).Value Then
     xlsheet.Cells(RCount+5,Ccount[B]).Interior.ColorIndex [/B]= 38
End If

    ^Controls to AutoSet Cell Shade based on cell value comparatively. 
    objExcel.Cells(1, 2).Font.ColorIndex = 44

    ^Controls to AutoSet Cell Text Color. 
    Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Set objWorkbook = objExcel.Workbooks.Add()
Set objWorksheet = objWorkbook.Worksheets(1)

For i = 1 to 14
    objExcel.Cells(i, 1).Value = i
    objExcel.Cells(i, 2).Interior.ColorIndex = i
Next

For i = 15 to 28
    objExcel.Cells(i - 14, 3).Value = i
    objExcel.Cells(i - 14, 4).Interior.ColorIndex = i
Next

For i = 29 to 42
    objExcel.Cells(i - 28, 5).Value = i
    objExcel.Cells(i - 28, 6).Interior.ColorIndex = i
Next

For i = 43 to 56
    objExcel.Cells(i - 42, 7).Value = i
    objExcel.Cells(i - 42, 8).Interior.ColorIndex = i
Next

    ^Code snippet to determine color code
Reply
  • There the monster is finished.

    I can personally say it was fun writing this.

    The first section posted goes through my company directory by a pulled variable and locations to find my end "address"
    the second section posted actually controls all the excel population. Still needs cleaned up.
    The third section is the final part of the program for clean up, and formula crunching.

    If you have any questions, I will answer what I can.

    ---------
    I had some time and decided to play with the excel functions some more. Finding the correct syntax for the "Excel" "WorkbookFunction" was a little annoying but I got it. I also figured out how to properly select a cell range for data crunching.

    Not sure if my formula for Cp, CpK are correct. I did it off of memory real quick.

    So here you go, a working excel output script with examples on excel function/formula implementation. There are other logic statements I have been playing with like cell shading and cell width/height. Once I get a "neat" layout I will post them.
    ------- 
    Dim WidthSet
 WidthSet = xlSheet.Range("A4").Columns.AutoFit()
WidthSet = xlSheet.Cells(RCount - 1, 2).Columns.AutoFit()[SIZE=2][/SIZE]

    ^Controls to AutoSet Cell Width for information display length 
    If xlsheet.Cells(RCount+5,CCount).value > xlsheet.cells(Rcount+2,Ccount).Value Then
     xlsheet.Cells(RCount+5,Ccount[B]).Interior.ColorIndex [/B]= 38
End If

    ^Controls to AutoSet Cell Shade based on cell value comparatively. 
    objExcel.Cells(1, 2).Font.ColorIndex = 44

    ^Controls to AutoSet Cell Text Color. 
    Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Set objWorkbook = objExcel.Workbooks.Add()
Set objWorksheet = objWorkbook.Worksheets(1)

For i = 1 to 14
    objExcel.Cells(i, 1).Value = i
    objExcel.Cells(i, 2).Interior.ColorIndex = i
Next

For i = 15 to 28
    objExcel.Cells(i - 14, 3).Value = i
    objExcel.Cells(i - 14, 4).Interior.ColorIndex = i
Next

For i = 29 to 42
    objExcel.Cells(i - 28, 5).Value = i
    objExcel.Cells(i - 28, 6).Interior.ColorIndex = i
Next

For i = 43 to 56
    objExcel.Cells(i - 42, 7).Value = i
    objExcel.Cells(i - 42, 8).Interior.ColorIndex = i
Next

    ^Code snippet to determine color code
Children
No Data
© 2024 Hexagon AB and/or its subsidiaries. Privacy Policy | Cloud Services Agreement