Export data from Access to Excel

[irenicus]

I am exporting data from Access to Excel with the following code:

  Dim xlApp As Excel.Application
  Dim xlBook As Excel.Workbook   'Excel werkboek object

  If Not IsExcelRunning (FUNCTION Checking if excel is open) Then
    Set xlApp = CreateObject("Excel.Application")
  Else
    Set xlApp = GetObject(, "Excel.Application")
  End If

  xlApp.DisplayAlerts = False
  Set xlBook = xlApp.Workbooks.Add()
  xlBook.Sheets.Add

  <DATA EXPORT LINES>
  
  xlApp.DisplayAlerts = True
  xlApp.Visible = True
  Set xlBook = Nothing
  Set xlApp = Nothing

Now when i execute this code it works fine till i execute it a second time after closing Excel. The first time Excel is show with data, the second time Excel opens also, but no data is shown. When i close Access and reopen it it works again one single time.

I already discovered that this happens ONLY when i use certain functions like Range. When i dont use them i dont have this problem.

By the way i am using Excel 2000 with VBA 6.0

Anyone know how to solve this? Did i forgot something?

[irenicus]

Apparently when adding xlapp.activesheet before the to be executed functions is necessary. If u dont the problem as i described can happen. Only question which remains is why?

[Ecniv]

If you are using activesheet or selection then I guess you ar enot coding to the correct objects... this can be tested by opening excel and trying to update a different excel sheet whilst your program runs...

If you create a variable to hold worksheets then as you add you set that (see following) then you have a pointer to that sheet and you shouldn't need activesheet method.

dim sht as worksheet
 
    set sht = wrkBk.sheets.add

Assumes that wrkBk is the work book set in a similar way after you have the excel object.

Possibly the excel app you opened is not defaultly pointing to a sheet (being invisible) and therefore it needed to set before you ran anything?


Vince

[irenicus]

It did work so thanks for that

But it is still strange that some procedures/functions (cell(y,x) = <value>) do work and some others (range(cell 1:cell2)) cause the problem i described. So why did some procedures/functions work and some not?

[Ecniv]

Originally posted by irenicus
It did work so thanks for that

But it is still strange that some procedures/functions (cell(y,x) = <value>) do work and some others (range(cell 1:cell2)) cause the problem i described. So why did some procedures/functions work and some not?

Apart from Excel being a bit crap like all ms office apps (incl. Access) we still put up with their eccentricities

range(cell 1:cell2)
range should accept this, but only if cell1:cell2 is in a text string.
sometimes plays up, though I've noticed. Cell(x,y) is the easier and more programmatically useful function, as it doesn't depend on whether excel is in a1 or r1c1 mode.

Sometimes range("cell1:cell2") needs a .select after it... weird but there you go.

As long as its working (knock on wood) leave it be ... hehehe


Vince

[irenicus]

Originally posted by Ecniv


Sometimes range("cell1:cell2") needs a .select after it... weird but there you go.

As long as its working (knock on wood) leave it be ... hehehe

Vince

Ok thanks, and will do