0

I am trying to make program to see the excel workbook that is already open, but it doesn't. Controlling with xlApp.Visible = True line creates a new excel document instead making the open one visible. Any suggestions please?

Edit: I added the rest of the code here. Using catia, I am trying to reach the open excel worksheet and make modifications on it. In this case I am trying to select A1:E5 cells one by one and clear their contents

Sub CATMain()

Dim xlApp As Excel.Application 
'On Error Resume Next
Set xlApp = VBA.GetObject("", "Excel.Application")

Dim exlBook As Workbook
Set exlBook = xlApp.ActiveWorkbook

Dim exlSheet As Worksheet
Set exlSheet = xlApp.ActiveSheet

xlApp.Visible = True


Dim cell1 As Integer
Dim cell2 As Integer
Dim cell3 As Integer
Dim cell4 As Integer

Dim myRange As Range

cell1 = 1 'InputBox("Tablo Başlangıç Satırını Girin: ")
cell2 = 1 'InputBox("Tablo Başlangıç Sütununu Girin: ")
cell3 = 5 'InputBox("Tablo Bitiş Satırını Girin: ")
cell4 = 5 'InputBox("Tablo Bitiş Sütununu Girin: ")

Set myRange = exlSheet.Range(Cells(cell1, cell2), Cells(cell3, cell4))
myRange.ClearContents

End Sub
Martijn Pieters
  • 1,048,767
  • 296
  • 4,058
  • 3,343
ccan
  • 13
  • 4
  • Why would you need to make the current excel visible - surely it's visible already? You would normally only use the visible line if you were creating a new instance of the application. What are you actually trying to do? – Harassed Dad Jul 23 '18 at 12:16
  • Actually I am using catia vba editor to execute the code and trying to make catia see the open excel workbook. I am trying to make a few operations between excel and catia. But catia doesn't get the open excel workbook. Instead it creates a new blank excel. – ccan Jul 23 '18 at 12:21
  • 1
    `Set exlSheet = Excel.ActiveSheet` should be `exlBook.ActiveSheet` or just `xlApp.ActiveSheet` – GisMofx Jul 23 '18 at 12:27
  • Thanks GisMofx, that helped me with the error. I edited the code as `Set exlSheet = exlBook.ActiveSheet` Now it gives me Run-Time error 91 Object variable or With block variable not set . Any suggestions on that? – ccan Jul 23 '18 at 12:38
  • @ccan: that means **xlBook** doesn't refer (in memory) to anything, or `exlSheet.Range(C` ... doesn't resolve to actual range - what happens when you debug those lines and check in the Immediate Window? – Our Man in Bananas Jul 23 '18 at 13:11
  • @Our Man in Bananas: Previoulsy I thought the problem was solved but I was wrong. As you said I realised and removed the 2 lines about workbook and nothing different. It still creates a new blank excel instead reading the active one. – ccan Jul 23 '18 at 13:20
  • 1
    @ccan: have you tried [F8 Debugging](https://www.excel-easy.com/vba/examples/debugging.html)? Also, see [Ozgrid: Debugging Excel VBA](http://www.ozgrid.com/VBA/debug.htm) – Our Man in Bananas Jul 23 '18 at 13:22

0 Answers0