K
Kay
Hi all,
I have an excel file with a series of date values as column. Each day I
need to populate some data under an appropriate column.
Now I'm writting a programme to automate this. In order to locate the
location of the column I use the find method :
=========
Set XlsApp = New Excel.Application
XlsApp.Visible = False
Set WkBk = XlsApp.Workbooks.Open("C:\Temp\Daily.xls")
Set WkSh = WkBk.Worksheets(1)
Set Rng = WkSh.UsedRange
Rng.Select
sDate = Format(DTPSaleDate.Value, "DD/MM/YYYY")
Selection.Find(What:=sDate, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Activate
iCol = ActiveCell.Column '<-- Use this location to populate data in
rows
'populate data ...
Set WkSh = Nothing
WkBk.Close True
XlsApp.Quit
Set XlsApp = Nothing
MsgBox "Done"
==========
There are 2 situation will generate error, and the message is :
91 - object variable or With block variable not set
First one is, if the date value is matched, works fine, but if I don't exit
the program and run for another day, it gives the above error.
The second one is, if the date value is Not matched, it gives the same
error.
I'm thinking it may be related to how I quit the excel app., however I the
codes seems OK to me, could you please tell me what's wrong?
Thanks!!!
I have an excel file with a series of date values as column. Each day I
need to populate some data under an appropriate column.
Now I'm writting a programme to automate this. In order to locate the
location of the column I use the find method :
=========
Set XlsApp = New Excel.Application
XlsApp.Visible = False
Set WkBk = XlsApp.Workbooks.Open("C:\Temp\Daily.xls")
Set WkSh = WkBk.Worksheets(1)
Set Rng = WkSh.UsedRange
Rng.Select
sDate = Format(DTPSaleDate.Value, "DD/MM/YYYY")
Selection.Find(What:=sDate, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Activate
iCol = ActiveCell.Column '<-- Use this location to populate data in
rows
'populate data ...
Set WkSh = Nothing
WkBk.Close True
XlsApp.Quit
Set XlsApp = Nothing
MsgBox "Done"
==========
There are 2 situation will generate error, and the message is :
91 - object variable or With block variable not set
First one is, if the date value is matched, works fine, but if I don't exit
the program and run for another day, it gives the above error.
The second one is, if the date value is Not matched, it gives the same
error.
I'm thinking it may be related to how I quit the excel app., however I the
codes seems OK to me, could you please tell me what's wrong?
Thanks!!!