Sign in to follow this  
Followers 0
AcaS

Date variable, Get date from Excel

6 posts in this topic

I am trying to set date variable can anyone help?

#include <Date.au3>

$oEX = ObjCreate("excel.Application")

$search = FileFindFirstFile("\\server\folder\blah\*.xls")

$dat_start=2011/10/1

$dat_stop=2011/12/31

Then I want to pick day and month from date like day=1, month=10:

$day_start=Date.Day($dat_start)

$month_start=Date.Month($dat_start)

$day_stop=Date.Day($dat_stop)

$month_stop=Date.Month($dat_stop)

Also I want to get date from Excel and pick day and month like in previous example:

$dan=Date.Day($oWB.WorkSheets("ЕСП").Range("D" & $j).Value)

$mesec=Date.Month($oWB.WorkSheets("ЕСП").Range("D" & $j).Value)

Kind regards,

AcaS

Share this post


Link to post
Share on other sites



From excel you can do it like :

$date = $oExcel.WorkSheets("ЕСП").Range("D" & $j).Value

$day = $oExcel.Application.Evaluate("Day("&$date&")")

For the others check the _Date* and _String* UDF fulnctions

Share this post


Link to post
Share on other sites

Problem with geting day and month from date by excel functions Day and Month

$date=$oWB.WorkSheets("ESP").Range("D" & $j).Value

MsgBox(0,"date",$date)

result is : 20120106000000

Next code is not good result is blank MsgBox:

$day=$oEX.Application.Evaluate("Day("&$date&")")

MsgBox(0,"day",$day)

Same as previous code, blank MsgBox:

$month=$oEX.Application.Evaluate("Month("&$date&")")

MsgBox(0,"month",$month)

Share this post


Link to post
Share on other sites

Is $oWB the same as $oEX ?

Share this post


Link to post
Share on other sites

Here is declaration

Dim $oEX, $search,$oWB,$file

$oEX = ObjCreate("Excel.Application")

$search = FileFindFirstFile("F:\RADNI\Prpracun za 2013\Copy of BACKA 2013\*.xls")

$dat_start=2011/10/1

$dat_stop=2011/12/31

If $search = -1 Then

MsgBox(0, "Error", "No files/directories matched the search pattern")

Exit

EndIf

While 1

$file = FileFindNextFile($search)

MsgBox(0,"Tekuci fajl",$file)

If @error Then ExitLoop

$oWB = $oEX.Workbooks.Open("F:\RADNI\Prpracun za 2013\Copy of BACKA 2013\" & $file)

Share this post


Link to post
Share on other sites

This one works. Put one date in A1 in Sheet1 for the test:

$FilePath = FileOpenDialog("Please open file","C:\","Excel (*.xls;*.xlsx;*.xlsm)",1)

$oExcel = ObjCreate("Excel.Application")

$oExcel.Visible = 1

$oExcel.WorkBooks.Open($FilePath)

$dat_start=2011/10/1

$dat_stop=2011/12/31

$date = $oExcel.WorkSheets("Sheet1").Range("A1").FormulaR1C1

ConsoleWrite($date&@CRLF)

$day = $oExcel.Application.Evaluate("Day("& $date &")")

ConsoleWrite($day&@CRLF)

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  
Followers 0