CODEWITHSUNDEEP
excelvba
arduinooo
arduinooo

Reputation: 1

Excel VBA to see if a workbook is open and if is write data

I'm trying to see if an excel workbook is open (Logtemplate.xlsm)using HP Reflections VBA.And if the workbook is open on my desktop,then sendthe
Text from Reflections to the next available row on that workbook. I can get the excel workbook to open, but it will only update the Text one time, and the next time I try to run the vba it reopens the same excel file and doesn't update the Text. I'm very new to VBA and any help would be greatly appreciated. Thank you in advance. 

Sub Test()
On Error GoTo 0

With Session

Wait 1

Dim Text As String
Dim TopRow, LastRow, LastColumn As Integer
Dim excelApp As Object
Dim wb As Workbook
Dim eRow As Long

TopRow = .ScreenTopRow
LastRow = TopRow + .DisplayRows
LastColumn = .DisplayColumns

Text = .GetText(.CursorRow - 1, 0, .CursorRow, 125)


On Error GoTo 0
Set wb = Nothing
On Error Resume Next
Set wb = Workbooks("Logtemplate.xlsm")

On Error GoTo 0

If wb Is Nothing Then
Set excelApp = CreateObject("Excel.Application")
excelApp.Visible = True
Set excelApp = Workbooks.Open("C:\Logtemplate.xlsm")
excelApp.Worksheets("Sheet1").Select
excelApp.Worksheets("Sheet1").Range("A2").Select
eRow = excelApp.Worksheets("Sheet1").Cells(Rows.Count, 1).End (xlUp).Offset  (1, 0).Row

Cells(eRow + 1, 1).Select
Cells(eRow, 1).Value = Text


On Error Resume Next

If Not wb("Logtemplate.xlsm") Is Nothing Then
Set excelApp = Worksheets("Sheet1").Select
End If
On Error Resume Next
excelApp.Worksheets("Sheet1").Select
excelApp.Worksheets("Sheet1").Range("A2").Select
eRow = excelApp.Worksheets("Sheet1").Cells(Rows.Count, 1).End (xlUp).Offset (1, 0).Row
Cells(eRow + 1, 1).Select
Cells(eRow, 1).Value = Text


End Sub

Upvotes: 0

Views: 117

Answers (1)

Zac
Zac

Reputation: 1944

If you are working with Excel VBA you don't need to create an excel application object. It's already available to you. code below will give you al the open workbooks:

Dim oWB As Workbook

For Each oWB In Workbooks

    MsgBox oWB.Name

Next

Set oWB = Nothing

I've put a MsgBox just to give you an idea on how to get workbook name. you can use that to check if you expected workbook is already open. Give this a go and see if your updates work. If not post back and I'll try to help

Upvotes: 1

Related Questions