0
Dim objExcelApp,a,RCP,ID,objOffice
Dim COUNT,CR,i,HR,MNT,SCND,Dt,Tm
Dim fso,DY,MNTH,YR
Dim a1,a2,a3,a4,a5,a6,a7,a8,a9,a10,a11,a12,a13,a15

HR=Hour(Time())
MNT=Minute(Time())
SCND=Second(Time())

DY = Day(Date())
MNTH = Month(Date())
YR = Year(Date())

Dt = DY & "_" & MNTH & "_" & YR
Tm = HR & "_" & MNT

a = HMIRuntime.Tags("FLOW_ROW").Read

On Error Resume Next
Set objOffice = GetObject(,"Excel.Application")
objExcelApp.DisplayAlerts = False 
objOffice.ActiveWorkbook.Save
objExcelApp.DisplayAlerts = False 
objOffice.Workbooks.Close
objExcelApp.DisplayAlerts = False
objOffice.quit
Set objOffice = Nothing

Set objExcelApp = CreateObject("Excel.Application")
objExcelApp.Visible = False

objExcelApp.Workbooks.Open "D:\REPORT\DAILY_FLOW.xlsx"
objExcelApp.DisplayAlerts = False

objExcelApp.Cells(a,1).Value =Date()
objExcelApp.Cells(a,2).Value =Time()

objExcelApp.Cells(a,6).Value = 0.5*a
objExcelApp.Cells(a,7).Value = 1*a
objExcelApp.Cells(a,8).Value = 2*a
objExcelApp.Cells(a,9).Value = 3*a
objExcelApp.Cells(a,10).Value = 4*a
objExcelApp.Cells(a,11).Value = 5*a
objExcelApp.Cells(a,12).Value = 6*a
objExcelApp.Cells(a,13).Value = 7*a
objExcelApp.Cells(a,14).Value = 8*a
objExcelApp.Cells(a,15).Value = 9*a
objExcelApp.Cells(a,16).Value = 10*a
objExcelApp.Cells(a,17).Value = 11*a

objExcelApp.ActiveWorkbook.Save
objExcelApp.DisplayAlerts = False
objExcelApp.Workbooks.Close
objExcelApp.Quit
Set objExcelApp = Nothing

If a=0 Or a=1 Then
a=4
HMIRuntime.Tags("FLOW_ROW").Write  a
Else
a=a+1
HMIRuntime.Tags("FLOW_ROW").Write  a
End If

I am using this code for entry in excel file. It is checking if any excel is open. If it is then it closes the excel first, without any prompt and then opens the excel, without visibility and make entry and then closes the excel again. All without any display alerts.

Problem I am facing is, sometimes a row is skipped between two entries. I am trying to find in what case it can happen?

Kbv.

Community
  • 1
  • 1
Kushant Vyas
  • 33
  • 6
  • 2
    Remove `On Error Resume Next`. This line just hides error messages but the errors still occur, you just cannot see them. Therefore you are blind when you try to debug your code, so you don't see which errors happen: After removing the line, tell where you get an error and which. Also please be clear in your tags if this is VBA or VBScript. These are 2 completely different languages. – Pᴇʜ May 22 '18 at 12:26
  • You could set your `dt` and `tm` variables using `dt=format(date(),"dd_mm_yy")` and `tm=format(time(),"hh_mm")` - but you don't use them elsewhere in the code. I'd guess `a = HMIRuntime.Tags("FLOW_ROW").Read` isn't returning the correct row. – Darren Bartrup-Cook May 22 '18 at 13:16
  • @Pᴇʜ: Actually I am using a third party software to run above script so there is not any debugging options. The above code is Vbscript, so sorry for the wring tag. – Kushant Vyas May 24 '18 at 08:45
  • @DarrenBartrup-Cook: well, it is just a part of the code, i am using them. Actually variable 'a' is incremented at the end of the script to make entry in subsequent row. But somehow excel isn't opening first time and script is executing twice. So data entry is made only once, but one row is skipped in between. I am not sure and trying to find that reason only. – Kushant Vyas May 24 '18 at 08:52
  • Might be useful: [How do I debug a stand-alone VBScript script?](https://stackoverflow.com/questions/2288043/how-do-i-debug-a-stand-alone-vbscript-script) – Pᴇʜ May 24 '18 at 08:54

0 Answers0