I tried to write VBA script in outlook to increase row of cells in excel. But I cannot set default value for this variable (I use public variable and row of cell should be started from "2").
How can I use script VBA in outlook to set second row of cell ( cell(2,1) =1) in excel at the first time, and increase row of cell for the next time
Here is my code in outlook. and when I run it, It said that "Invalid outside procedure"
Public count As Integer: count = 2
Sub test()
Dim xlApp As Object
Dim xlWB As Object
Dim xlSheet As Object
On Error Resume Next
Set xlApp = GetObject(, "Excel.Application")
If Err <> 0 Then
Set xlApp = CreateObject("Excel.Application")
End If
On Error GoTo 0
'Check file open or not, and open it if it isn't opened
If (IsWorkBookOpen("D:\Book1.xlsx") = True) Then
Set xlWB = xlApp.Workbooks("Book1.xlsx")
Else
Set xlWB = xlApp.Workbooks.Open("D:\Book1.xlsx")
End If
Set xlSheet = xlWB.Sheets("Sheet1")
'Process excel file
xlSheet.Cells(count, 1) = 1
count = count + 1
End Sub
Function IsWorkBookOpen(FileName As String) 'function to check excel file is open or not
Dim ff As Long, ErrNo As Long
On Error Resume Next
ff = FreeFile()
Open FileName For Input Lock Read As #ff
Close ff
ErrNo = Err
On Error GoTo 0
Select Case ErrNo
Case 0: IsWorkBookOpen = False
Case 70: IsWorkBookOpen = True
Case Else: Error ErrNo
End Select
End Function
No comments:
Post a Comment