I am trying to write a simple onclick button to open an excel file that already exists on my desktop
what i dont understand is I am using the same Dim as many other examples I have seen. but I am getting a Compile Error "User defined type not defined" and it pulls up the code showing blue highlights Dim excelapp As Excel.Application but Yellow highlight on Private Sub line. Where am i going wrong. is the failure actually further down the code? I ma running Access 365 and Should i be using a different syntax to reference Excel?
Private Sub cmdcopyfieldsonly_Click()
Dim excelapp As Excel.Application
Dim wbTarget As Excel.Workbooks
Dim qdfquerytest As QueryDef
Dim rsquerytest As Recordset
Set qdfquerytest = CurrentDb.QueryDefs("OpenComplaintsQuery")
Set rsquerytest = qdfquerytest.OpenRecordset()
Set excelapp = CreateObject("Excel.Application")
excelapp.Visible = True
Set wbTarget = Excel.Workbooks.Open("C:\Desktop\copytest.xlsx")
wbTarget.worksheets("Sheet1").Range("B8").Value = rsquerytest(2).Value
End Sub
It's a matter of early vs late binding, which are mixed in your code.
Early binding requires a hard reference of the type e.g. Excel, so VBA has information about the type during compilation. You can then declare the variable by its type and also have intellisense while typing.
Dim app As Excel.Application
Set app = New Excel.Application
The drawback is when changing office versions as the code still references the old version - the reference will need to be updated.
Late binding does not require a hard reference since the variable is declared using the base type of Object and gets resolved at runtime. No issues when changing office version but you lose intellisense.
Dim app As Object
Set app = CreateObject("Excel.Application")
In you case, this is absolutely valid when using late binding:
With CreateObject("Excel.Application")
With .Workbooks.Open("C:\Desktop\copytest.xlsx")
.Worksheets("Sheet1").Range("B8").Value = rsquerytest(2).Value
End With
End With