Jump to content
Sign in to follow this  
SOF-TECH

Write inputs values into Colum and Raw in CSV

Recommended Posts

Dear all,

Can someone show  me how to en hance the below function to write in CSV  into column  and rows the input values ? 

I am getting this result: 

image.png.2e82e90226750cb7316d1b0fbbd19e88.png

I would like the result to be as this 

image.png.3f5a787395de64c88672baabac1a07de.png

From A1:C1 is for headers

From A2:C2 is for input Data

Global Const $GUI_EVENT_CLOSE = -3

$sDataFilePath = @ScriptDir & "\Records.csv"

#region ### START Koda GUI section ### Form=
$Form1 = GUICreate("Demo1: New Record", 580, 115)
$Input1 = GUICtrlCreateInput("", 10, 30, 270, 21)
$Input2 = GUICtrlCreateInput("", 300, 30, 270, 21)
$Input3 = GUICtrlCreateInput("", 10, 80, 270, 21)
$Label1 = GUICtrlCreateLabel("Name:", 10, 10, 35, 17)
$Label2 = GUICtrlCreateLabel("ID:", 300, 10, 18, 17)
$Label3 = GUICtrlCreateLabel("Phone No:", 10, 60, 55, 17)
$Button1 = GUICtrlCreateButton("Save to CSV", 450, 70, 120, 30)
GUISetState(@SW_SHOW)
#endregion ### END Koda GUI section ###

While 1
    $nMsg = GUIGetMsg()
    Switch $nMsg
        Case $GUI_EVENT_CLOSE
            Exit
        Case $Button1
            _ExportData()
            MsgBox(64, @ScriptName, "Record Saved.")
    EndSwitch
WEnd

Func _ExportData()
    If Not FileExists($sDataFilePath) Then
        FileWriteLine($sDataFilePath, "Name;ID;Phone No.;")
    EndIf
    For $i = $Input1 To $Input3
        FileWrite($sDataFilePath, GUICtrlRead($i) & ";")
    Next
    FileWriteLine($sDataFilePath, "")
EndFunc   ;==>_ExportData

May be Excel UDF has be to be added but I can manage that my self  

Thank you in advance

Share this post


Link to post
Share on other sites

Read the csv file row by row using FileReadLine. Then split the result to an array using StringSplit. Then write the array to excel using _Excel_RangeWrite. Simple task.

Share this post


Link to post
Share on other sites

Good morning @SOF-TECH:)
You destination file is always in .CSV format?

Edit: Yes, I didn't pay attention reading your post, sorry.


Little question: which is the separator character set in your PC? :)

Edited by FrancescoDiMuro

Click here to see my signature:

Spoiler

Thoughts:

  • I will always thank you for the time you spent for me.
    I'm here to ask, and from your response, I'd like to learn.
    By my knowledge, I can help someone else, and "that someone" could help in turn another, and so on.

/*--------------------------------------------------------------------------------------------------------------------------------------------------------------------------*/

ALWAYS GOOD TO READ:

 

Share this post


Link to post
Share on other sites

Hi FrancescoDiMuro

Actually the location of the file csv will be @WorkingDir & "\" & @ComputerName & "_INFO.csv  in the future.

the above code is just simulation of the real one. I do nott know how to write into excel. that's it.

Thank you

Share this post


Link to post
Share on other sites

First off, you will not get the pretty  column separation in a CSV, you will need to save to Excel instead. For your _ExportData function you could do something like this:

Func _ExportData()
    Local $oExcel = _Excel_Open()
    Local $oWorkBook

    If Not FileExists($sDataFilePath) Then
        $oWorkBook = _Excel_BookNew($oExcel)
    Else
        $oWorkBook = _Excel_BookOpen($oExcel, $sDataFilePath)
    EndIf

    _Excel_RangeWrite($oWorkBook, Default, GUICtrlRead($Input1), "A1")
    _Excel_RangeWrite($oWorkBook, Default, GUICtrlRead($Input2), "B1")
    _Excel_RangeWrite($oWorkBook, Default, GUICtrlRead($Input3), "C1")

    If FileExists($sDataFilePath) Then
        _Excel_BookSave($oWorkBook)
    Else
        _Excel_BookSaveAs($oWorkBook, $sDataFilePath)
    EndIf

    _Excel_BookClose($oWorkBook)
    _Excel_Close($oExcel)
EndFunc   ;==>_ExportData

This gives you a rough idea; you would have to work out cycling through the lines as you add further entries, but you should be able to do that simply enough.

The problem with this approach would be the constant opening and closing of Excel (or leaving it open and running into an issue if the script fails). It would be a better practice to save all of your entries in an array until you're ready to do a single commit. Something like this:

#include <Array.au3>
#include <Excel.au3>
#include <MsgBoxConstants.au3>

Global Const $GUI_EVENT_CLOSE = -3
Local $aArray[1][3] = [["Name","ID","Phone"]]
Local $sDataFilePath = @ScriptDir & "\Records"

#region ### START Koda GUI section ### Form=
$Form1 = GUICreate("Demo1: New Record", 580, 115)
$Input1 = GUICtrlCreateInput("", 10, 30, 270, 21)
$Input2 = GUICtrlCreateInput("", 300, 30, 270, 21)
$Input3 = GUICtrlCreateInput("", 10, 80, 270, 21)
$Label1 = GUICtrlCreateLabel("Name:", 10, 10, 35, 17)
$Label2 = GUICtrlCreateLabel("ID:", 300, 10, 18, 17)
$Label3 = GUICtrlCreateLabel("Phone No:", 10, 60, 55, 17)
$Button1 = GUICtrlCreateButton("Add Record", 450, 70, 120, 30)
$Button2 = GUICtrlCreateButton("Save to Excel", 300, 70, 120, 30)

GUISetState(@SW_SHOW)
#endregion ### END Koda GUI section ###

While 1
    $nMsg = GUIGetMsg()
    Switch $nMsg
        Case $GUI_EVENT_CLOSE
            Exit
        Case $Button1
            _ArrayAdd($aArray, GUICtrlRead($Input1) & "|" & GUICtrlRead($Input2) & "|" & GUICtrlRead($Input3))
            MsgBox($MB_OK, "Demo", "Record Added")
        Case $Button2
            _ExportData($aArray)
    EndSwitch
WEnd


Func _ExportData($aArray)
    Local $oExcel = _Excel_Open()
    Local $oWorkBook

    If Not FileExists($sDataFilePath) Then
        $oWorkBook = _Excel_BookNew($oExcel)
    Else
        $oWorkBook = _Excel_BookOpen($oExcel, $sDataFilePath)
    EndIf

    _Excel_RangeWrite($oWorkBook, Default, $aArray, Default)

    $oWorkBook.Worksheets("Sheet1").Columns("A:C").AutoFit

    If FileExists($sDataFilePath) Then
        _Excel_BookSave($oWorkBook)
    Else
        _Excel_BookSaveAs($oWorkBook, $sDataFilePath)
    EndIf

    _Excel_BookClose($oWorkBook)
    _Excel_Close($oExcel)
EndFunc   ;==>_ExportData

Any questions please ask :)

Edited by JLogan3o13

"Profanity is the last vestige of the feeble mind. For the man who cannot express himself forcibly through intellect must do so through shock and awe" - Spencer W. Kimball

How to get your question answered on this forum!

Share this post


Link to post
Share on other sites
23 hours ago, SOF-TECH said:

Can you demonstate it please. I still can not get your point :)

Those functions have very good documentation and examples. You can jut replace your filename in the examples and it will work. What more demonstration would you ever need?

Share this post


Link to post
Share on other sites

Hi JLogan3o13

Amazing, I followed your method and worked like o'clock. And as you said .xlsx is better than CSV. 

I really appreciate it. 

Thank you all by the way

 

Share this post


Link to post
Share on other sites
On 2018/5/3 at 8:58 PM, JLogan3o13 said:

First off, you will not get the pretty  column separation in a CSV, you will need to save to Excel instead. For your _ExportData function you could do something like this:

Func _ExportData()
    Local $oExcel = _Excel_Open()
    Local $oWorkBook

    If Not FileExists($sDataFilePath) Then
        $oWorkBook = _Excel_BookNew($oExcel)
    Else
        $oWorkBook = _Excel_BookOpen($oExcel, $sDataFilePath)
    EndIf

    _Excel_RangeWrite($oWorkBook, Default, GUICtrlRead($Input1), "A1")
    _Excel_RangeWrite($oWorkBook, Default, GUICtrlRead($Input2), "B1")
    _Excel_RangeWrite($oWorkBook, Default, GUICtrlRead($Input3), "C1")

    If FileExists($sDataFilePath) Then
        _Excel_BookSave($oWorkBook)
    Else
        _Excel_BookSaveAs($oWorkBook, $sDataFilePath)
    EndIf

    _Excel_BookClose($oWorkBook)
    _Excel_Close($oExcel)
EndFunc   ;==>_ExportData

This gives you a rough idea; you would have to work out cycling through the lines as you add further entries, but you should be able to do that simply enough.

The problem with this approach would be the constant opening and closing of Excel (or leaving it open and running into an issue if the script fails). It would be a better practice to save all of your entries in an array until you're ready to do a single commit. Something like this:

#include <Array.au3>
#include <Excel.au3>
#include <MsgBoxConstants.au3>

Global Const $GUI_EVENT_CLOSE = -3
Local $aArray[1][3] = [["Name","ID","Phone"]]
Local $sDataFilePath = @ScriptDir & "\Records"

#region ### START Koda GUI section ### Form=
$Form1 = GUICreate("Demo1: New Record", 580, 115)
$Input1 = GUICtrlCreateInput("", 10, 30, 270, 21)
$Input2 = GUICtrlCreateInput("", 300, 30, 270, 21)
$Input3 = GUICtrlCreateInput("", 10, 80, 270, 21)
$Label1 = GUICtrlCreateLabel("Name:", 10, 10, 35, 17)
$Label2 = GUICtrlCreateLabel("ID:", 300, 10, 18, 17)
$Label3 = GUICtrlCreateLabel("Phone No:", 10, 60, 55, 17)
$Button1 = GUICtrlCreateButton("Add Record", 450, 70, 120, 30)
$Button2 = GUICtrlCreateButton("Save to Excel", 300, 70, 120, 30)

GUISetState(@SW_SHOW)
#endregion ### END Koda GUI section ###

While 1
    $nMsg = GUIGetMsg()
    Switch $nMsg
        Case $GUI_EVENT_CLOSE
            Exit
        Case $Button1
            _ArrayAdd($aArray, GUICtrlRead($Input1) & "|" & GUICtrlRead($Input2) & "|" & GUICtrlRead($Input3))
            MsgBox($MB_OK, "Demo", "Record Added")
        Case $Button2
            _ExportData($aArray)
    EndSwitch
WEnd


Func _ExportData($aArray)
    Local $oExcel = _Excel_Open()
    Local $oWorkBook

    If Not FileExists($sDataFilePath) Then
        $oWorkBook = _Excel_BookNew($oExcel)
    Else
        $oWorkBook = _Excel_BookOpen($oExcel, $sDataFilePath)
    EndIf

    _Excel_RangeWrite($oWorkBook, Default, $aArray, Default)

    $oWorkBook.Worksheets("Sheet1").Columns("A:C").AutoFit

    If FileExists($sDataFilePath) Then
        _Excel_BookSave($oWorkBook)
    Else
        _Excel_BookSaveAs($oWorkBook, $sDataFilePath)
    EndIf

    _Excel_BookClose($oWorkBook)
    _Excel_Close($oExcel)
EndFunc   ;==>_ExportData

Any questions please ask :)

On 2018/5/3 at 8:58 PM, JLogan3o13 said:

First off, you will not get the pretty  column separation in a CSV, you will need to save to Excel instead. For your _ExportData function you could do something like this:

Func _ExportData()
    Local $oExcel = _Excel_Open()
    Local $oWorkBook

    If Not FileExists($sDataFilePath) Then
        $oWorkBook = _Excel_BookNew($oExcel)
    Else
        $oWorkBook = _Excel_BookOpen($oExcel, $sDataFilePath)
    EndIf

    _Excel_RangeWrite($oWorkBook, Default, GUICtrlRead($Input1), "A1")
    _Excel_RangeWrite($oWorkBook, Default, GUICtrlRead($Input2), "B1")
    _Excel_RangeWrite($oWorkBook, Default, GUICtrlRead($Input3), "C1")

    If FileExists($sDataFilePath) Then
        _Excel_BookSave($oWorkBook)
    Else
        _Excel_BookSaveAs($oWorkBook, $sDataFilePath)
    EndIf

    _Excel_BookClose($oWorkBook)
    _Excel_Close($oExcel)
EndFunc   ;==>_ExportData

This gives you a rough idea; you would have to work out cycling through the lines as you add further entries, but you should be able to do that simply enough.

The problem with this approach would be the constant opening and closing of Excel (or leaving it open and running into an issue if the script fails). It would be a better practice to save all of your entries in an array until you're ready to do a single commit. Something like this:

#include <Array.au3>
#include <Excel.au3>
#include <MsgBoxConstants.au3>

Global Const $GUI_EVENT_CLOSE = -3
Local $aArray[1][3] = [["Name","ID","Phone"]]
Local $sDataFilePath = @ScriptDir & "\Records"

#region ### START Koda GUI section ### Form=
$Form1 = GUICreate("Demo1: New Record", 580, 115)
$Input1 = GUICtrlCreateInput("", 10, 30, 270, 21)
$Input2 = GUICtrlCreateInput("", 300, 30, 270, 21)
$Input3 = GUICtrlCreateInput("", 10, 80, 270, 21)
$Label1 = GUICtrlCreateLabel("Name:", 10, 10, 35, 17)
$Label2 = GUICtrlCreateLabel("ID:", 300, 10, 18, 17)
$Label3 = GUICtrlCreateLabel("Phone No:", 10, 60, 55, 17)
$Button1 = GUICtrlCreateButton("Add Record", 450, 70, 120, 30)
$Button2 = GUICtrlCreateButton("Save to Excel", 300, 70, 120, 30)

GUISetState(@SW_SHOW)
#endregion ### END Koda GUI section ###

While 1
    $nMsg = GUIGetMsg()
    Switch $nMsg
        Case $GUI_EVENT_CLOSE
            Exit
        Case $Button1
            _ArrayAdd($aArray, GUICtrlRead($Input1) & "|" & GUICtrlRead($Input2) & "|" & GUICtrlRead($Input3))
            MsgBox($MB_OK, "Demo", "Record Added")
        Case $Button2
            _ExportData($aArray)
    EndSwitch
WEnd


Func _ExportData($aArray)
    Local $oExcel = _Excel_Open()
    Local $oWorkBook

    If Not FileExists($sDataFilePath) Then
        $oWorkBook = _Excel_BookNew($oExcel)
    Else
        $oWorkBook = _Excel_BookOpen($oExcel, $sDataFilePath)
    EndIf

    _Excel_RangeWrite($oWorkBook, Default, $aArray, Default)

    $oWorkBook.Worksheets("Sheet1").Columns("A:C").AutoFit

    If FileExists($sDataFilePath) Then
        _Excel_BookSave($oWorkBook)
    Else
        _Excel_BookSaveAs($oWorkBook, $sDataFilePath)
    EndIf

    _Excel_BookClose($oWorkBook)
    _Excel_Close($oExcel)
EndFunc   ;==>_ExportData

Any questions please ask :)

Hi Glogan

Quoting on this, I compiled the script and run it from C Drive, every thing wornking fine and file excel is saved on script directory. however, when I run out of C drive (E or USB) Excel file is created but could not saved. even the above demo script you wrote has the same effect when run from out of C home drive. could explain or help for this matter

 

Thank you 

Share this post


Link to post
Share on other sites

@SOF-TECH i just tested running this from a number of network drives E:, P: etc. and it saved just fine. I don't have a USB stick to try it with at the moment, but will test that out when I get a chance. Are you sure the user you are running the script under has write access to the directory you're trying to save the excel file to?


"Profanity is the last vestige of the feeble mind. For the man who cannot express himself forcibly through intellect must do so through shock and awe" - Spencer W. Kimball

How to get your question answered on this forum!

Share this post


Link to post
Share on other sites

My PC is under domain of company and I have admin rights. Frankly, I have to check with domain policy first regarding the point you mentionned. I will try the script on my personal Machine and update you.

Thank you for the reply and support :)

Share this post


Link to post
Share on other sites

Sorry for replying with delay. as I mentionned if run from usb excel could not be save after creation. the script have to be saved in 2 files, 1 is txt that there is no problem in saved it from any path in which the soft is run and 1xls file to store diffrent data where the probelm of saving occurs. Here is the globals to create the files :

Global $ExcelData = @ScriptDir & "\" & @ComputerName & "_Statistics.xlsx" 
Global $NotePadData = @ScriptDir & "\" & @ComputerName & "_Specifications.txt

I have also changed  to @WorkingDir and added #requireadmin reslult is :

Run from C Drive: txt & xlsx files got created 

Run from external Drive: Only txt. Execel file is created but could not be saved.

 

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

  • Recently Browsing   0 members

    No registered users viewing this page.

  • Similar Content

    • By GOSM
      Hey,
      as part of a larger Programm I am trying to copy the values from one cell in an Excel file to another. The code I am using is the folowing.
       
      Local $oExcel = _Excel_Open() If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeCopy Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended) _Excel_RangeCopyPaste($oExcel.ActiveSheet, "V4:W43", "X4", False, $xlPasteValues, Default, True) If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeCopy Example 4", "Error copying rows." & @CRLF & "@error = " & @error & ", @extended = " & @extended) MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeCopy Example 4", "2 Rows successfully pasted from the clipboard to row 1.") As I understood it the option $xlPasteValues should do the trick  but it is still copying the inserted formulas. I attached a test code and excel file.
      Thanks for the help Simon
      Copy Values Test.zip
    • By Chimp
      An "improper" "unusual" use of Excel
      the script allows the creation of artistic images by simply coloring the background of the individual cells of the Excel workbook.
      Although definitely useless ... I find it quite funny though
      have a good time
      many thanks to @UEZ , @Malkey , @water
      p.s.
      I think the pixelite + color to array process can be simplified, but I used the two ready-made functions provided by UEZ and Malkey. I thank both of you (credits  and links in listing)
      p.p.s.
      strange behaviour: while excell is filling cells, if you move the mouse pointer off the excell window, the fill speed increases ... (?)

       
      ; =============================================================================================================================== ; Name ..........: Excel in art ; Description ...: This script allows the creation of artistic images in Excel from a choosed picture. ; The picture is done by simply coloring the background of the individual cells of an Excel workbook. ; Although definitely useless, I find it quite funny though ; ; Return values .: An artistic image in an Excel workbook ; Author ........: Addiego Gianni (chimp) ; Modified ......: ; Remarks .......: Many thanks to UEZ, Malkey and Water ; Related .......: ; Link ..........: ; Example .......: ; =============================================================================================================================== #include <GDIPlus.au3> #include <Excel.au3> _PixelsToCells(50) ; <-- Parameter 50 is the cumber of horizontal Excel cells to be filled MsgBox(64, "All done", "Excel art is ready") Func _PixelsToCells($iHcells = 50) ; Check application object Local $oExcel = _Excel_Open() If Not IsObj($oExcel) Then MsgBox(16, "Error", "Sorry, You need to have 'Excel' intalled") ; Choose Image File Local $sPath = FileOpenDialog("Choose Image File", @ScriptDir & "", "Images (*.gif;*.png;*.jpg;*.bmp)| All (*.*)") If $sPath = '' Then Exit MsgBox(16, "Error", "Sorry, no image was chosen") ; Create a new Excel workbook $oWorkbook = _Excel_BookNew($oExcel, 1) ; initialize GDI+ _GDIPlus_Startup() Local $hBmp = _GDIPlus_BitmapCreateFromFile($sPath) Local $iWidth = _GDIPlus_ImageGetWidth($hBmp) ; get image width ; Local $iHeight = _GDIPlus_ImageGetHeight($hBmp) ; not needed here Local $iStep = $iWidth / $iHcells ; calculate the pixelation factor Local $hBitmap_new = _GDIPlus_PixelateBitmap($hBmp, $iStep) ; pixelate the image Local $aPixelColors = _FileImageToArray($hBitmap_new) ; get pixel colors ; reduce Excel columns width Local $xx = 1, $yy = 1 For $iCol = 0 To UBound($aPixelColors, 2) - 1 Step $iStep $oWorkbook.Sheets(1).Columns($xx).ColumnWidth = 1 $xx += 1 Next ; reduce Excel rows height For $iRow = 0 To UBound($aPixelColors) - 1 Step $iStep $oWorkbook.Sheets(1).Rows($yy).RowHeight = 9 $yy += 1 Next $xx = 1 $yy = 1 For $iRow = 0 To UBound($aPixelColors) - 1 Step $iStep For $iCol = 0 To UBound($aPixelColors, 2) - 1 Step $iStep $oWorkbook.Sheets(1).Range(_Excel_ColumnToLetter($xx) & $yy).Interior.Color = Number("0x" & $aPixelColors[$iRow][$iCol]) $xx += 1 Next $yy += 1 $xx = 1 Next _GDIPlus_BitmapDispose($hBmp) _GDIPlus_Shutdown() EndFunc ;==>_PixelsToCells ; by UEZ ; https://www.autoitscript.com/forum/topic/167707-imagepixelate/?do=findComment&comment=1227509 Func _GDIPlus_PixelateBitmap($hBitmap, $iPixelate, $bSmooth = 1) Local $iWidth = _GDIPlus_ImageGetWidth($hBitmap), $iHeight = _GDIPlus_ImageGetHeight($hBitmap) Local $iNewW = Round($iWidth / $iPixelate, 0), $iNewH = Round($iHeight / $iPixelate, 0) Local $hBitmap_scaled = _GDIPlus_BitmapCreateFromScan0($iNewW, $iNewH) Local $hCtxt = _GDIPlus_ImageGetGraphicsContext($hBitmap_scaled) Local $iInterpolation = 5 If $bSmooth Then $iInterpolation = $GDIP_INTERPOLATIONMODE_BILINEAR _GDIPlus_GraphicsSetInterpolationMode($hCtxt, $iInterpolation) _GDIPlus_GraphicsDrawImageRect($hCtxt, $hBitmap, 0, 0, $iNewW, $iNewH) _GDIPlus_GraphicsDispose($hCtxt) Local $hBitmap_pixelated = _GDIPlus_BitmapCreateFromScan0($iWidth, $iHeight) $hCtxt = _GDIPlus_ImageGetGraphicsContext($hBitmap_pixelated) _GDIPlus_GraphicsSetInterpolationMode($hCtxt, $GDIP_INTERPOLATIONMODE_NearestNeighbor) _GDIPlus_GraphicsDrawImageRectRect($hCtxt, $hBitmap_scaled, 0, 0, $iNewW, $iNewH, -$iPixelate, -$iPixelate, $iWidth + 2 * $iPixelate, $iHeight + 2 * $iPixelate) _GDIPlus_GraphicsDispose($hCtxt) Return $hBitmap_pixelated EndFunc ;==>_GDIPlus_PixelateBitmap ; by Malkey ; https://www.autoitscript.com/forum/topic/112540-is-there-a-function-for-reading-images-into-2d-arrays/?do=findComment&comment=788472 Func _FileImageToArray($hImage) Local $Reslt, $stride, $format, $Scan0, $iIW, $iIH ; , $hImage Local $v_Buffer, $width, $height ; _GDIPlus_Startup() ; $hImage = _GDIPlus_ImageLoadFromFile($sFileName) $iIW = _GDIPlus_ImageGetWidth($hImage) $iIH = _GDIPlus_ImageGetHeight($hImage) ProgressOn("Progress Bar", "Filling a " & $iIW & " x " & $iIH & " size array.", "0 percent") $Reslt = _GDIPlus_BitmapLockBits($hImage, 0, 0, $iIW, $iIH, $GDIP_ILMREAD, $GDIP_PXF32ARGB) ;Get the returned values of _GDIPlus_BitmapLockBits () $width = DllStructGetData($Reslt, "width") $height = DllStructGetData($Reslt, "height") $stride = DllStructGetData($Reslt, "stride") $format = DllStructGetData($Reslt, "format") $Scan0 = DllStructGetData($Reslt, "Scan0") Local $aArray[$height][$width] For $j = 0 To $iIH - 1 For $i = 0 To $iIW - 1 $v_Buffer = DllStructCreate("dword", $Scan0 + ($j * $stride) + ($i * 4)) $aArray[$j][$i] = StringRegExpReplace(Hex(DllStructGetData($v_Buffer, 1), 6), "(.{2})(.{2})(.{2})", "\3\2\1") ; To RGB format Next ProgressSet(Int(100 * $j / ($iIH)), Int(100 * $j / ($iIH)) & " percent") Next _GDIPlus_BitmapUnlockBits($hImage, $Reslt) ProgressOff() _GDIPlus_ImageDispose($hImage) Return $aArray EndFunc ;==>_FileImageToArray  
    • By Zaoka
      Hi guys
      Need little help with filtering.
      I'm trying to filter specific weeks from power pivot table using this code recorded with Excel VBA :
       
      #include <Excel.au3> Global $oExcel = _Excel_Open() Global $oWorkbook = _Excel_BookOpen($oExcel, "C:\Users\....\Orders.xlsb") $oWorkbook.PivotTables("PivotTable1").PivotFields( _ "[Report 2].[Week].[Week]").VisibleItemsList = Array( _ "[Report 2].[Week].&[10]", "[Report 2].[Week].&[11]", _ "[Report 2].[Week].&[12]", "[Report 2].[Week].&[13]", _ "[Report 2].[Week].&[14]", "[Report 2].[Week].&[15]") But get error
      error: Array(): undefined function. Not sure how to resolve this.
    • By Rajat231
      I am trying this code to create multiple workbooks eachone  shall be copy of one worksheet from a workbook having multiple sheets ( keeing the name same)
      SavingWorksheets.au3
    • By Zaoka
      Hi guys,
      i have simple report in PowerPivot that shows Orders (Values) by Regions (Row) and Weeks (Columns). In Filter field is WeekDAYS (Monday,Tuesday,Wednesday,Thursday etc )
      how to filter WeekDAYS Filed on WEEKDAYYesterday with autoit ?
      my junky try
      #include <Date.au3> #include <Excel.au3> Local $sWEEKDAYYesterday = _DateDayOfWeek(@WDAY-1) Global $oExcel = _Excel_Open() Global $oWorkbook = _Excel_BookOpen($oExcel, "C:\Users\.......\Orders.xlsb") Sleep (5000) $oExcel.ActiveWorkbook.RefreshAll Sleep (5000) $oExcel.Application.Sheets("Sheet1").PivotTables("PivotTable1").PivotFields("WeekDAYS").PivotFilters($sWEEKDAYYesterday)  
      Error result
      $oExcel.Application.Sheets("PivotTable1").PivotTables("PivotTable1").PivotFields("WeekDAYS").PivotFilters($sWEEKDAYYesterday) $oExcel.Application^ ERROR  
×
×
  • Create New...