Jump to content
SOF-TECH

Write inputs values into Colum and Raw in CSV

Recommended Posts

SOF-TECH

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
JLogan3o13

Moved to the appropriate forum. Please pay attention to where you post in the future.


√-1 2^3 ∑ π, and it was delicious!

How to get your question answered on this forum!

Share this post


Link to post
Share on other sites
SOF-TECH

Sorry for the inconvinience caused:>

Share this post


Link to post
Share on other sites
Juvigy

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
SOF-TECH

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

Share this post


Link to post
Share on other sites
FrancescoDiMuro
Posted (edited)

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
SOF-TECH

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
JLogan3o13
Posted (edited)

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
  • Like 1

√-1 2^3 ∑ π, and it was delicious!

How to get your question answered on this forum!

Share this post


Link to post
Share on other sites
Juvigy
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
SOF-TECH

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
SOF-TECH
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
JLogan3o13

@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?


√-1 2^3 ∑ π, and it was delicious!

How to get your question answered on this forum!

Share this post


Link to post
Share on other sites
SOF-TECH

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
Juvigy

Have you tried with #requireadmin ?

Share this post


Link to post
Share on other sites
SOF-TECH

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

  • Similar Content

    • robertocm
      By robertocm
      I'm using this for replacing text strings in the VBProject of all excel files in a folder and subfolders.
      I have the same text string in several lines and those lines could have some differences between files: then not feasible for .ReplaceLine method
      I'm not interested in placing all the code in a xla AddIn, because the code is similar but not exactly the same in all the files.
       
      Opt("WinTitleMatchMode", 2) ;1=start, 2=subStr, 3=exact, 4=advanced, -1 to -4=Nocase #include <File.au3> #include <WinAPIFiles.au3> #include <Excel.au3> $oMyError = ObjEvent("AutoIt.Error", "ErrFunc") ;Install a custom error handler Global $iEventError ; to be checked to know if com error occurs. Must be reset after handling. Global Const $sMessage = "Select Folder" Global $sFileSelectFolder = FileSelectFolder($sMessage, "") If @error Then MsgBox(0, "", "No folder was selected.") Exit EndIf Global $bFileOpen ;Look for excel files in selected directory and all subdirectories Global $aFileList = _FileListToArrayRec($sFileSelectFolder, "*.xlsm", $FLTAR_FILES, $FLTAR_RECUR, $FLTAR_NOSORT, $FLTAR_FULLPATH) If Not @error Then Local $oAppl = _Excel_Open(Default, Default, False, Default, True) ;If @error Then Exit MsgBox(0, "Error", "Error _Excel_Open" & @CRLF & "@error = " & @error & ", @extended = " & @extended) For $i = 1 To $aFileList[0] $bFileOpen = _WinAPI_FileInUse($aFileList[$i]) If $bFileOpen = 0 Then ;ShellExecute($aFileList[$i]) Local $oWorkbook = _Excel_BookOpen($oAppl, $aFileList[$i]) ;If @error Then Exit MsgBox(0, "Error", "Error _Excel_BookOpen: " & $sFilePath & @CRLF & "@error = " & @error & ", @extended = " & @extended) Local $oProject = $oWorkbook.VBProject ;From: Adapt VBA in a workbook using VBA / http://www.snb-vba.eu/index_en.html ;2.7.2 Macromodule delete With $oProject .VBComponents.Remove(.VBComponents("SplashText")) If $iEventError Then Consolewrite("SplashText Form not found: " & $aFileList[$i] & @CRLF) $iEventError = 0 ; Reset after displaying a COM Error occurred EndIf .VBComponents.Import("C:\Documents and Settings\XP\Escritorio\PLANTILLAS_EXPORT\SplashText.frm") EndWith ;3.2.1.8 Macro: delete With $oProject.VBComponents("Actual").CodeModule ;3.2.1.2 Macro: find If .Find("Sub Check_NumPed(", 1, 1, -1, -1) Then ;Note: using '+ 1' at the end of the line because i'm used to add an empty line between procedures (see vba help for ProcCountLines) .DeleteLines( .ProcStartLine("Check_NumPed", 0), .ProcCountLines("Check_NumPed", 0) + 1) EndIf EndWith ;Check if range name exists. If not create named ranges If Not IsObj($oWorkbook.Sheets("DATOS").Evaluate("Booking_DestPort")) Then ;If Not IsObj($oWorkbook.Sheets("DATOS").Range("Booking_DestPort")) Then If $oWorkbook.Sheets("DATOS").Range("AC7").value = "DestPort" Then $oWorkbook.Names.Add("Booking_DestPort", "=DATOS!$AC$8") Else ConsoleWrite("-> Not: 'DestPort' in AC7" & @TAB & $aFileList[$i] & @CRLF) EndIf If $oWorkbook.Sheets("DATOS").Range("AD7").value = "FinalDest" Then $oWorkbook.Names.Add("Booking_FinalDest", "=DATOS!$AD$8") Else ConsoleWrite("-> Not: 'FinalDest' in AD7" & @TAB & $aFileList[$i] & @CRLF) EndIf EndIf ;Open VBE Editor (like Alt+F11) $oAppl.VBE.MainWindow.Visible = True ;$oAppl.VBE.Windows("Inmediato").Visible = True ;https://www.autoitscript.com/forum/topic/77545-resolved-vbaofficeexcel-experts/ ;Spiff59, Aug 2008 ;Local $oModules = $oProject.VBComponents ;Local $oModules = $oWorkbook.VBProject.VBComponents ;$oModules.Item(1).CodeModule.CodePane.Show ;$oModules.Item(1).Activate ; With $oModules.Item($y).CodeModule ; .ReplaceLine (1 , "Sub SpellCheck()") ; .DeleteLines (10, 1) ; .InsertLines (7 , "TEST") ; EndWith ;Wait 30 seconds for the window to appear. Local $hWnd = WinWait("Microsoft Visual Basic - ", "Proyecto - VBAProjec", 30) WinActivate($hWnd) WinWaitActive($hWnd, "", 30) If WinActive($hWnd, "") Then ;Sleep(100) ;Send("{F7}") $oProject.VBComponents("Actual").Activate ;Wait 30 seconds for the window to appear. Local $hWnd2 = WinWait(" - [Actual (Código)]", "Proyecto - VBAProject", 30) WinActivate($hWnd2) WinWaitActive($hWnd2, "", 30) ;First Replace If WinActive($hWnd2, "") Then Send("{CTRLDOWN}h{CTRLUP}") ;Wait 30 seconds for the window to appear. Local $hWnd3 = WinWait("Reemplazar", "&Procedimiento actua", 30) WinActivate($hWnd3) WinWaitActive($hWnd3, "", 30) Sleep(100) Send('Sheets("DATOS").Range("AC8")') Sleep(200) Send("{TAB}") Sleep(100) ;Send("{DEL}") Send('Range("Booking_DestPort")') Sleep(200) ControlClick("Reemplazar", "", "[ID:4892]") Sleep(100) Send("{ALTDOWN}z{ALTUP}") Local $hWnd4 = WinWait("Microsoft Visual Basic", "Se ha buscado en la ", 2) ;WinActivate($hWnd4) ;WinWaitActive($hWnd4, "", 3) If WinActive($hWnd4, "") Then Sleep(100) Send("{SPACE}") Else Consolewrite("Not found 1: " & $aFileList[$i] & @CRLF) Local $hWnd5 = WinWait("Microsoft Visual Basic", "No se encontró", 0) ;WinActivate($hWnd5) ;WinWaitActive($hWnd5, "", 2) Sleep(100) Send("{SPACE}") EndIf Sleep(100) If WinActive($hWnd3, "") Then ;Alt+F4 Send("!{F4}") Sleep(100) EndIf EndIf ;Second Replace If WinActive($hWnd2, "") Then Send("{CTRLDOWN}h{CTRLUP}") ;Wait 30 seconds for the window to appear. Local $hWnd3 = WinWait("Reemplazar", "&Procedimiento actua", 30) WinActivate($hWnd3) WinWaitActive($hWnd3, "", 30) Sleep(100) Send('Sheets("DATOS").Range("AD8")') Sleep(200) Send("{TAB}") Sleep(100) Send('Range("Booking_FinalDest")') Sleep(200) ControlClick("Reemplazar", "", "[ID:4892]") Sleep(100) Send("{ALTDOWN}z{ALTUP}") Local $hWnd4 = WinWait("Microsoft Visual Basic", "Se ha buscado en la ", 2) ;WinActivate($hWnd4) ;WinWaitActive($hWnd4, "", 3) If WinActive($hWnd4, "") Then Sleep(100) Send("{SPACE}") Else Consolewrite("Not found 2: " & $aFileList[$i] & @CRLF) Local $hWnd5 = WinWait("Microsoft Visual Basic", "No se encontró", 0) ;WinActivate($hWnd5) ;WinWaitActive($hWnd5, "", 2) Sleep(100) Send("{SPACE}") EndIf Sleep(100) If WinActive($hWnd3, "") Then ;Alt+F4 Send("!{F4}") Sleep(100) EndIf EndIf ;Close VBE Editor If WinActive($hWnd2, "") Then $oAppl.VBE.ActiveWindow.Close ;Send("^{F4}") ;Sleep(100) ;Send("^s") $oAppl.VBE.MainWindow.Visible = False ;Sleep(100) ;Send("!{F4}") EndIf EndIf _Excel_BookClose($oWorkbook, True) ;If @error Then Exit MsgBox(0, "Error", "Error _Excel_BookClose: " & $sFilePath & @CRLF & "@error = " & @error & ", @extended = " & @extended) EndIf Next Else MsgBox(16, "Error", "No files were found in the folder specified.") EndIf _Excel_Close($oAppl) ;If @error Then Exit MsgBox(0, "Error", "Error _Excel_Close" & @CRLF & "@error = " & @error & ", @extended = " & @extended) ;This is a custom error handler Func ErrFunc() $HexNumber = Hex($oMyError.number, 8) ;~ MsgBox(0, "", "We intercepted a COM Error !" & @CRLF & _ ;~ "Number is: " & $HexNumber & @CRLF & _ ;~ "WinDescription is: " & $oMyError.windescription) ConsoleWrite("-> We intercepted a COM Error !" & @CRLF & _ "-> err.number is: " & @TAB & $HexNumber & @CRLF & _ "-> err.source: " & @TAB & $oMyError.source & @CRLF & _ "-> err.windescription: " & @TAB & $oMyError.windescription & _ "-> err.scriptline is: " & @TAB & $oMyError.scriptline & @CRLF) $iEventError = 1 ; Use to check when a COM Error occurs EndFunc ;==>ErrFunc  
    • gahhon
      By gahhon
      Hi Guys,
      I was trying to read some data from the excel file and without opening the file. But I tried a lot of methods, it still open the file.
      And also, I am able to capture the ColumnA value but not Column B.
      Thanks for advance information.
      Global $oDataA, $oDataB Call ("ExcelRead", "B2", "C2") Func ExcelRead($oColumnA, $oColumnB) Local $oPath = @ScriptDir & "\MyFile.xlsx" Local $oExcel = _Excel_Open() Local $oWorkbook = _Excel_BookOpen($oExcel, $oPath, 1, 0) $oDataA = _Excel_RangeRead($oWorkbook, "Sheet 1", $oColumnA) $oDataB = _Excel_RangeRead($oWorkbook, "Sheet 1", $oColumnB) MsgBox(0, "Test Value", $oDataA & ", " & $oDataB) EndFunc  
    • ed973
      By ed973
      I really don't understand how to save as an open excel sheet.
      I run a script that at the end open the excel: just only need to save as the opened excel on my desktop (and overwrite it everytime).
      I'm trying to use .ActiveWorkBook.SaveAs("C:\Users\Enrico\Desktop\impegnato.xlsx") but...
       
      Thanks in advance for helping.
    • yasha
      By yasha
      i want am trying to select a nimber to run a program and then select where to save the excel result at before hand
      the problem is that it does not save in the folder i want but the folder before any solutions
      #.................
      $sFolder = ""
          ; Create a constant variable in Local scope of the message to display in FileSelectFolder.
          Local Const $sMessage = "Select a folder"
          ; Display an open dialog to select a file.
          $sFileSelectFolder = FileSelectFolder($sMessage, $sFolder)
          If @error Then
              ; Display the error message.
              MsgBox($MB_SYSTEMMODAL, "", "No folder was selected.")
          Else
              ; Display the selected folder.
              MsgBox($MB_SYSTEMMODAL, "", "You chose the following folder:" & @CRLF & $sFileSelectFolder)
          EndIf
      .......
      ........
      $oExcel = ObjCreate("Excel.Application")                   ; Create an Excel Object
      $oExcel.Visible = 1                                        ; Let Excel show itself
      $oExcel.Workbooks.Open("J:\OPS\OPS_Share\Planners\2 - Weekly Reports\Auto download\"& $YY & $MM & $DD & " ORDER.xls",0)
      $oExcel.ActiveWorkbook.Saveas ( $sFileSelectFolder,""& $YY & $MM & $DD & " ORDER.xlsx", 1)
      $oExcel.ActiveWorkBook.Close
      $oExcel.Quit
      i only want to save it as ddmmyy order inside documents but it saves in libraries as documents ddmmyy order.
    • PiyushJhawar
      By PiyushJhawar
      I am part of QA team of an analytics application. We support third party tools like Excel , Tableue .
      I have to write automation script that connect Excel to our analytics application. In short i want below to automate
      > Open Excel
      > Click on "Data" option available in header and then click on "From Other Services " then click on "From Analysis Services"
      > It will open pop up and then need to write username password there.
      I am new in this tool . Can any one please provide me link of any document that help me to create above script
×