Create excel object in access issue

[K3vin]

Hi could someone explain the difference between creating instance of excel in access

Dim xlApp As Excel.Application
Set xlApp = CreateObject("Excel.Application")

The above produced run time errors when running my code within access when opening excel files for example this method is not support.

However if changed the code to below then there were no errors and code run as expected.

Dim xlApp As Object
Set xlApp = CreateObject("Excel.Application")

bit confused to why this would happen.

 

[arnelgp]

on your first code, you already declared xlApp as Excel.Application,
then to make new instance:

set xlApp= New Excel.Application

when you explicitly use this method you must
make reference to Microsoft Excel XX.X Object Library.

while creating an object like the later method is
more generic and is not targetted on specific
version of Excel:

Dim xlApp As Object
set xlApp=CreateObject("Excel.Application")

 

[isladogs]

Further to arnel's comments, for future info these 2 methods are referred to as early binding and late binding.

Both methods have their pros & cons:

Early binding - provides intellisense in VBA coding so easier to code BUT requires the VBA Excel reference (version specific)

Late binding - no reference needed but no intellisense so harder to code

 

[K3vin]

Thanks for the explanation.

The code is running however sometimes its not picking up the instance of excel. I have the correct reference however get global fail errors.

Is this because am not fully referencing the file as it will be active when I open it. Bit confused at times why it running sometimes then failing other times maybe its due to excel instance really not sure. Any ideas?

Dim xlApp As Object
Set xlApp = CreateObject("Excel.Application")
Dim shSource As Worksheet
Dim shDest As Worksheet
Dim sh As Worksheet
Dim flg As Boolean
Dim wkSource As Workbook

' File Path to folder
strPath = "String is here my data source"

strFile = Dir(strPath & "*.xlsx")
    Do While Len(strFile) > 0
    strPathFile = strPath & strFile
Set xlApp = GetObject(, "Excel.Application")
Set wkSource = xlApp.WorkBooks.Open(strPathFile)
Set shSource = wkSource.Worksheets("exportinstances")

'Sometimes it fails with runtime error 1004 - 'method worksheet of object'_Global Failed
For Each sh In Worksheets
    If sh.Name = "RawData" Then flg = True: Exit For
Next sh
    If flg = True Then
    Set shDest = wkSource.Worksheets("RawData")
    shDest.Cells.Clear
    Else
    ' Add raw data tab is for loop does not find it
    Sheets.Add(After:=Sheets(Sheets.Count)).Name = "RawData"
    Set shDest = wkSource.Worksheets("RawData")
    End If

 

[arnelgp]

you have this in your code:

For each sh in Worksheets
...
...
...
...
Else

Sheets.Add( ..... ???

should be:

For each sh In wkSource.WorkSheets
	If sh.Name = "RawData" Then flg=True: Exit For
Next

If flg = True Then
	Set shDest = wkSource.Sheets("RawData")
	shDest.Cells.Clear

Else
	wkSource.Sheets.Add(After:=wkSource.Sheets(wkSource.Sheets.Count)).Name = "RawData"
	Set shDest=wkSource.Sheets("RawData")
End If

 

[K3vin]

Thanks this making whole load more sense now. Appreciate you taking the time to help me out.

Set xlApp = GetObject(, "Excel.Application")
Set wkSource = xlApp.WorkBooks.Open(strPathFile)
Set shSource = wkSource.Worksheets("exportinstances")

Just one last question by using set like this

Set shSource = wkSource.Worksheets("exportinstances")

am really making this statement

Workbooks("opened workbook").WorkSheets("exportinstances")

So if I write a with statement, I am fully referencing the workbook.

With shsource
'code here
End with

for example this would fail

wkSource.shsource

Hope this makes sense.