Class Code for Excel, Import OLE Object Files

[lindyr]

I am attempting to import OLE object files into an Access
table using the code below. I error out on the Class
Code field. What is the Class code for MS Excel files -
is it Excel.Spreadsheet?

The instructions give an example of " Paint.Picture
for .bmp files.", but don's say what to use for Excel
files.

Here is the detail code:

Private Sub cmdLoadOLE_Click()

Dim MyFolder As String
Dim MyExt As String
Dim MyPath As String
Dim MyFile As String
Dim strCriteria As String

MyFolder = Me!SearchFolder
' Get the search path.
MyPath = MyFolder & "\" & "*." & [SearchExtension]
' Get the first file in the path containing the
file extension.
MyFile = Dir(MyPath, vbNormal)
Do While Len(MyFile) <> 0
[OLEPath] = MyFolder & "\" & MyFile
[OLEFile].Class = [OLEClass]
[OLEFile].OLETypeAllowed = acOLEEmbedded
[OLEFile].SourceDoc = [OLEPath]
[OLEFile].Action = acOLECreateEmbed
' Check for next OLE file in the folder.
MyFile = Dir
' Go to new record on form.
' For Access 95 only, use the following Line of
code:
DoCmd.DoMenuItem acFormBar, acEditMenu, 12, 4,
acMenuVer70

' For Access 97 only, use the following line of
code:
'DoCmd.RunCommand acCmdRecordsGoToNew
Loop

End Sub

 

[TC]

Those names are known as progid's (not class codes). The progid for Excel is
"Excel.Application".

No idea if that will solve your problem, though!

HTH,
TC

 

[lindyr]

Thanks for the info. My expectation is that the SQL I've
detailed in this note will "automatically" import data
from the 100 Excel files that I have in a common folder.
These all have the same number of fields, which are in the
same sequence.

This seems a bit too easy. Can you confirm that this SQL
logic will do this for me. Also, if this is not the case,
do you know of a way to easily/quickly import data from
100 Excel files into one table in Access? This would be
300,000+ records in total. Thanks.

-----Original Message-----
Those names are known as progid's (not class codes). The progid for Excel is
"Excel.Application".

No idea if that will solve your problem, though!

HTH,
TC

I am attempting to import OLE object files into an Access
table using the code below. I error out on the Class
Code field. What is the Class code for MS Excel files -
is it Excel.Spreadsheet?

The instructions give an example of " Paint.Picture
for .bmp files.", but don's say what to use for Excel
files.

Here is the detail code:

Private Sub cmdLoadOLE_Click()

Dim MyFolder As String
Dim MyExt As String
Dim MyPath As String
Dim MyFile As String
Dim strCriteria As String

MyFolder = Me!SearchFolder
' Get the search path.
MyPath = MyFolder & "\" & "*." & [SearchExtension]
' Get the first file in the path containing the
file extension.
MyFile = Dir(MyPath, vbNormal)
Do While Len(MyFile) <> 0
[OLEPath] = MyFolder & "\" & MyFile
[OLEFile].Class = [OLEClass]
[OLEFile].OLETypeAllowed = acOLEEmbedded
[OLEFile].SourceDoc = [OLEPath]
[OLEFile].Action = acOLECreateEmbed
' Check for next OLE file in the folder.
MyFile = Dir
' Go to new record on form.
' For Access 95 only, use the following Line of
code:
DoCmd.DoMenuItem acFormBar, acEditMenu, 12, 4,
acMenuVer70

' For Access 97 only, use the following line of
code:
'DoCmd.RunCommand acCmdRecordsGoToNew
Loop

End Sub

.

 

[Damon Heron]

You might try something like this -assuming you have a good naming
convention for the Excel files. In this case, I used test1.xls thru
20.xls....

Private Sub Command1_Click()
Dim sheetloc As String
Dim x As Integer
For x = 1 To 20
sheetloc = "C:\orders\test"
sheetloc = sheetloc & x & ".xls"
DoCmd.TransferSpreadsheet acImport, SpreadsheetType:=8,
TableName:="tblExcelSheet", FileName:=sheetloc,_ Range:="A1:c8"
Next x
End Sub

Also note that field names on spreadsheet are not present. Field names in
table are F1, F2, F3, etc. See the TransferSpreadsheet method for more.

HTH
Damon

Thanks for the info. My expectation is that the SQL I've
detailed in this note will "automatically" import data
from the 100 Excel files that I have in a common folder.
These all have the same number of fields, which are in the
same sequence.

This seems a bit too easy. Can you confirm that this SQL
logic will do this for me. Also, if this is not the case,
do you know of a way to easily/quickly import data from
100 Excel files into one table in Access? This would be
300,000+ records in total. Thanks.

-----Original Message-----
Those names are known as progid's (not class codes). The progid for Excel is
"Excel.Application".

No idea if that will solve your problem, though!

HTH,
TC

I am attempting to import OLE object files into an Access
table using the code below. I error out on the Class
Code field. What is the Class code for MS Excel files -
is it Excel.Spreadsheet?

The instructions give an example of " Paint.Picture
for .bmp files.", but don's say what to use for Excel
files.

Here is the detail code:

Private Sub cmdLoadOLE_Click()

Dim MyFolder As String
Dim MyExt As String
Dim MyPath As String
Dim MyFile As String
Dim strCriteria As String

MyFolder = Me!SearchFolder
' Get the search path.
MyPath = MyFolder & "\" & "*." & [SearchExtension]
' Get the first file in the path containing the
file extension.
MyFile = Dir(MyPath, vbNormal)
Do While Len(MyFile) <> 0
[OLEPath] = MyFolder & "\" & MyFile
[OLEFile].Class = [OLEClass]
[OLEFile].OLETypeAllowed = acOLEEmbedded
[OLEFile].SourceDoc = [OLEPath]
[OLEFile].Action = acOLECreateEmbed
' Check for next OLE file in the folder.
MyFile = Dir
' Go to new record on form.
' For Access 95 only, use the following Line of
code:
DoCmd.DoMenuItem acFormBar, acEditMenu, 12, 4,
acMenuVer70

' For Access 97 only, use the following line of
code:
'DoCmd.RunCommand acCmdRecordsGoToNew
Loop

End Sub

.

 

[lindyr]

Thanks Damon.

I think I understand now how this works. Basically you are
using SQL in place of a macro that has 20
different "Transfer Spreadsheet" Actions. Thanks for the
information.

-----Original Message-----
You might try something like this -assuming you have a good naming
convention for the Excel files. In this case, I used test1.xls thru
20.xls....

Private Sub Command1_Click()
Dim sheetloc As String
Dim x As Integer
For x = 1 To 20
sheetloc = "C:\orders\test"
sheetloc = sheetloc & x & ".xls"
DoCmd.TransferSpreadsheet acImport, SpreadsheetType:=8,
TableName:="tblExcelSheet", FileName:=sheetloc,_ Range:="A1:c8"
Next x
End Sub

Also note that field names on spreadsheet are not present. Field names in
table are F1, F2, F3, etc. See the TransferSpreadsheet method for more.

HTH
Damon

Thanks for the info. My expectation is that the SQL I've
detailed in this note will "automatically" import data
from the 100 Excel files that I have in a common folder.
These all have the same number of fields, which are in the
same sequence.

This seems a bit too easy. Can you confirm that this SQL
logic will do this for me. Also, if this is not the case,
do you know of a way to easily/quickly import data from
100 Excel files into one table in Access? This would be
300,000+ records in total. Thanks.

-----Original Message-----
Those names are known as progid's (not class codes).

The
progid for Excel is

"Excel.Application".

No idea if that will solve your problem, though!

HTH,
TC


I am attempting to import OLE object files into an Access
table using the code below. I error out on the Class
Code field. What is the Class code for MS Excel files -
is it Excel.Spreadsheet?

The instructions give an example of " Paint.Picture
for .bmp files.", but don's say what to use for Excel
files.

Here is the detail code:

Private Sub cmdLoadOLE_Click()

Dim MyFolder As String
Dim MyExt As String
Dim MyPath As String
Dim MyFile As String
Dim strCriteria As String

MyFolder = Me!SearchFolder
' Get the search path.
MyPath = MyFolder & "\" & "*." & [SearchExtension]
' Get the first file in the path containing the
file extension.
MyFile = Dir(MyPath, vbNormal)
Do While Len(MyFile) <> 0
[OLEPath] = MyFolder & "\" & MyFile
[OLEFile].Class = [OLEClass]
[OLEFile].OLETypeAllowed = acOLEEmbedded
[OLEFile].SourceDoc = [OLEPath]
[OLEFile].Action = acOLECreateEmbed
' Check for next OLE file in the folder.
MyFile = Dir
' Go to new record on form.
' For Access 95 only, use the following

Line
of

code:
DoCmd.DoMenuItem acFormBar, acEditMenu, 12, 4,
acMenuVer70

' For Access 97 only, use the following

line
of

code:
'DoCmd.RunCommand acCmdRecordsGoToNew
Loop

End Sub




.

.