can't compile, user defined type not defined

[Guest]

This doesn't compile on this line:
Dim appExcel As excel.Application
and I don't know why?

Also, do I need to declare the function with the library kernel32? I don't
know what that is for or if I need it but it seems to want the library.

thanks,

-------------------
Private Sub Form_AfterUpdate()

Dim lngFileHandle As Long
Dim lngLastError As Long
Const STR_DIRECTORY_PATH = "c:\Test"
Const str_Filename = "emp.xls"

Select Case Check_Dir_File
Case 1
'Check if directory exists

If Dir(STR_DIRECTORY_PATH) = "" Then
MkDir STR_DIRECTORY_PATH
End If
Case 2
'Check file exists
If Dir(STR_DIRECTORY_PATH & str_Filename = "") Then
lngFileHandle = CreateXLFile(str_Filename)

End If
End Select
Close longFileHandle
End Sub

'Private Declare Function CreateXLFile Lib "kernel32" Alias _
' "XXXX" (ByVal ) As Long

Private Function CreateXLFile(ByVal strFilename As String)
Dim appExcel As excel.Application ******
Dim wbk As excel.Workbook
Dim wks As excel.Worksheet
Dim i As Integer
Dim EndRow As Long

Dim dbs As DAO.Database
Dim rst As DAO.Recordset

Set appExcel = excel.Application
appExcel.Visible = True
Set wbk = appExcel.Workbooks.Open("emp.xls")

Set wks = appExcel.Worksheets(1)
wks.Activate

EndRow = Range("A65536").End(xlUp).Select

For i = 1 To EndRow

Range("a1").Offset(0, i + 1).Value = Forms![frmEmployees]!Form![ID]
Range("a2").Offset(0, i + 1).Value = Forms![frmEmployees]!Form![FirstName]
Range("a3").Offset(0, i + 1).Value = Forms![frmEmployees]!Form![Salary]


Set dbs = Nothing
End Function

 

[Guest]

If you are using this line:

Dim appExcel As excel.Application

Then you need to set a reference to Microsoft Excel in the References (Tools

References) in the VBA IDE window.

--
Bob Larson
Access World Forums Super Moderator
Utter Access VIP
Tutorials at http://www.btabdevelopment.com
__________________________________
If my post was helpful to you, please rate the post.

This doesn't compile on this line:
Dim appExcel As excel.Application
and I don't know why?

Also, do I need to declare the function with the library kernel32? I don't
know what that is for or if I need it but it seems to want the library.

thanks,

-------------------
Private Sub Form_AfterUpdate()

Dim lngFileHandle As Long
Dim lngLastError As Long
Const STR_DIRECTORY_PATH = "c:\Test"
Const str_Filename = "emp.xls"

Select Case Check_Dir_File
Case 1
'Check if directory exists

If Dir(STR_DIRECTORY_PATH) = "" Then
MkDir STR_DIRECTORY_PATH
End If
Case 2
'Check file exists
If Dir(STR_DIRECTORY_PATH & str_Filename = "") Then
lngFileHandle = CreateXLFile(str_Filename)

End If
End Select
Close longFileHandle
End Sub

'Private Declare Function CreateXLFile Lib "kernel32" Alias _
' "XXXX" (ByVal ) As Long

Private Function CreateXLFile(ByVal strFilename As String)
Dim appExcel As excel.Application ******
Dim wbk As excel.Workbook
Dim wks As excel.Worksheet
Dim i As Integer
Dim EndRow As Long

Dim dbs As DAO.Database
Dim rst As DAO.Recordset

Set appExcel = excel.Application
appExcel.Visible = True
Set wbk = appExcel.Workbooks.Open("emp.xls")

Set wks = appExcel.Worksheets(1)
wks.Activate

EndRow = Range("A65536").End(xlUp).Select

For i = 1 To EndRow

Range("a1").Offset(0, i + 1).Value = Forms![frmEmployees]!Form![ID]
Range("a2").Offset(0, i + 1).Value = Forms![frmEmployees]!Form![FirstName]
Range("a3").Offset(0, i + 1).Value = Forms![frmEmployees]!Form![Salary]


Set dbs = Nothing
End Function

 

[Jason]

Dear Sir/Madam:
I am searching from the web and find this page - I have the same problem and
I was not able to solve it even I set the reference.
I am working on visio 2003 and I need to transfer shape date to an excel
spreadsheet by VBA code. However the code Dim appExcel as Excel.Application
is not working, pop-up a compile error.
I did set the reference of excel in Tool->Reference in visio VBA IDE.
Could you help?
Thank you in advance.
Jason

If you are using this line:

Dim appExcel As excel.Application

Then you need to set a reference to Microsoft Excel in the References (Tools

References) in the VBA IDE window.

--
Bob Larson
Access World Forums Super Moderator
Utter Access VIP
Tutorials at http://www.btabdevelopment.com
__________________________________
If my post was helpful to you, please rate the post.

This doesn't compile on this line:
Dim appExcel As excel.Application
and I don't know why?

Also, do I need to declare the function with the library kernel32? I don't
know what that is for or if I need it but it seems to want the library.

thanks,

-------------------
Private Sub Form_AfterUpdate()

Dim lngFileHandle As Long
Dim lngLastError As Long
Const STR_DIRECTORY_PATH = "c:\Test"
Const str_Filename = "emp.xls"

Select Case Check_Dir_File
Case 1
'Check if directory exists

If Dir(STR_DIRECTORY_PATH) = "" Then
MkDir STR_DIRECTORY_PATH
End If
Case 2
'Check file exists
If Dir(STR_DIRECTORY_PATH & str_Filename = "") Then
lngFileHandle = CreateXLFile(str_Filename)

End If
End Select
Close longFileHandle
End Sub

'Private Declare Function CreateXLFile Lib "kernel32" Alias _
' "XXXX" (ByVal ) As Long

Private Function CreateXLFile(ByVal strFilename As String)
Dim appExcel As excel.Application ******
Dim wbk As excel.Workbook
Dim wks As excel.Worksheet
Dim i As Integer
Dim EndRow As Long

Dim dbs As DAO.Database
Dim rst As DAO.Recordset

Set appExcel = excel.Application
appExcel.Visible = True
Set wbk = appExcel.Workbooks.Open("emp.xls")

Set wks = appExcel.Worksheets(1)
wks.Activate

EndRow = Range("A65536").End(xlUp).Select

For i = 1 To EndRow

Range("a1").Offset(0, i + 1).Value = Forms![frmEmployees]!Form![ID]
Range("a2").Offset(0, i + 1).Value = Forms![frmEmployees]!Form![FirstName]
Range("a3").Offset(0, i + 1).Value = Forms![frmEmployees]!Form![Salary]


Set dbs = Nothing
End Function