acSpreadsheetTypeExcel8 Error Message

[DIVINEDAR0956]

Could someone please help me.

I have an excel file that I want to transfer into access. This has worked in the pass and now all of a sudden I am getting this error message EXTERNAL TABLE IS NOT IN THE EXPECTED FORMAT . I can't seem to figure out if there is a library I need to download or my excel program isn't saving my excel file properly.

I have the following code:


Public Function updatelink()
'EditProjectNumber

Dim dbsDocument As Database
Dim rstDocument As Recordset
Dim sTable As String
Dim strFileName As String

strFileName = FileDialog

'Search for the temporal file name in access. Delete the table when found


' Add your TransferSpreadsheet code here...
sTable = "Project"
DeleteTable (sTable)
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, sTable, strFileName, True, "PROJECT_EXTRACT"
sTable = "TASK"
DeleteTable (sTable)
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, sTable, strFileName, True, "TASK_EXTRACT"
sTable = "HRSENTER"
DeleteTable (sTable)
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, sTable, strFileName, True, "HOURS_EXTRACT"
sTable = "RATE"
DeleteTable (sTable)
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, sTable, strFileName, True, "RATE_EXTRACT"
sTable = "VENDOR"
DeleteTable (sTable)
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, sTable, strFileName, True, "VENDOR_EXTRACT"
End Function

Each of these tables to be imported is calculated in an excel file. I don't know if my problem could be in this script or in my FileDialog script as follows:

Public Function FileDialog()

'Declare a variable as a FileDialog object.
Dim fd As FileDialog

'Create a FileDialog object as a File Picker dialog box.
Set fd = Application.FileDialog(msoFileDialogFilePicker)

'Declare a variable to contain the path
'of each selected item. Even though the path is a String,
'the variable must be a Variant because For Each...Next
'routines only work with Variants and Objects.
Dim vrtSelectedItem As Variant

'Use a With...End With block to reference the FileDialog object.
With fd
'Add a filter that includes GIF and JPEG images and make it the first item in the list.
.Filters.Add "Excel Spreadsheet", "*.xls", 1

'Use the Show method to display the File Picker dialog box and return the user's action.
'The user pressed the action button.
If .Show = -1 Then
vrtSelectedItem = .SelectedItems(1)

'Step through each string in the FileDialogSelectedItems collection.
'For Each vrtSelectedItem In .SelectedItems

'vrtSelectedItem is a String that contains the path of each selected item.
'You can use any file I/O functions that you want to work with this path.
'This example simply displays the path in a message box.
'MsgBox "The path is: " & vrtSelectedItem
FileDialog = vrtSelectedItem
'Next vrtSelectedItem
'The user pressed Cancel.
Else
FileDialog = Null
End If
End With

'Set the object variable to Nothing.
Set fd = Nothing
End Function

I have open the excel file and saved it several different ways change the acSpreadsheetTypeExcel(8)

My excel spreadsheet that is used to import has the following code that saves the file in a specific directory automatically. Could my error possibly be in the following code:

Sub LockOut()
Dim sFileName As String



'Create a string of the file that you are saving
sFileName = "n:\CostAnalysis\Projects\" & Worksheets("PROJECT_INFORMATION&quot.Range("B2&quot.Value

'Send a message dialog to screen
MsgBox "Saving: " & sFileName

ShowSheets

'Save a write protected copy out to the j:' ActiveWorkbook.SaveCopyAs Filename:= _
' sFileName, WriteResPassword:="deleon", CreateBackup:=False

ActiveWorkbook.SaveAs Filename:=sFileName, FileFormat:= _
xlNormal, Password:="", WriteResPassword:="deleon", ReadOnlyRecommended:= _
False, CreateBackup:=False

HideSheets

End Sub

If anyone has any ideas why this is happening please tell me.

Thank you for your help and your patience.




Darlene Sippio
dsippio@comtechsystems.com

 

[VBslammer]

Check this KB to see if you have this problem:

http://support.microsoft.com/default.aspx?scid=kb;en-us;135374

VBSlammer

Unemployed in Houston, Texas

 

[SBendBuckeye]

The problem could be your enumerated constants. I found that many of them have changed when going from Access97 to Access2000. I would focus initially on the DoCmd.TransferSpreadsheet line. You might try manually rekeying it and leave any defaults blank as opposed to filling in every default value. Kick your code into single step mode and see which line of code it breaks on.

Hope this helps and good luck!

 

[DIVINEDAR0956]

Question for you.

I understand doing a macro to import the tables but the catch to writing the macro is that you have to select the file from a particular folder so you can't write a specific file name.

Is there a way for the file name to be a procedure to open the file dialog box to select a file to import from.

This is the first reason why I didn't use a macro because I was unable to figure out how to get the file name to open the dialog box.

If someone can help me figure that one then I got it made.

Thank you.

Appreciate you guys so very much.

Darlene Sippio
dsippio@comtechsystems.com

 

[DIVINEDAR0956]

My code breaks on this line "DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, sTable, strFileName, True, "PROJECT_EXTRACT"". I have tried several ways to bring in the excel spreadsheet to access by saving the excel spreadsheets in different versions of excel but none seems to work.

Also what does the number at the end of acSpreadsheetTypeExcel8. Because regardless of what I save the spreadsheet as none of the numbers seem to work to pull in the spreadsheet.

Could there be a library that I'm missing in either excel or access that will help me accomplish this task.

Thank you.

Darlene Sippio
dsippio@comtechsystems.com

 

[SBendBuckeye]

Darlene,

Have you tried the following to let it take the default?

DoCmd.TransferSpreadsheet acImport, , sTable, strFileName, True, "PROJECT_EXTRACT"

If you paste the string acSpreadsheetTypeExcel8 into the search part of the object browser you will be able to see the different enumerations.

Also, does the range name Project_Extract exist in your import file? Hope this helps you.

Good Luck!

 

[DIVINEDAR0956]

No that didn't work by letting it default.

For the world of me I can't understand why it's not working.

I have tried it every way.

I have even checked my range names for all documents and they work fine.

In the excel document itself once it does a final calculation it automatically saves it in a directory on a server. This may be my problem. Should the following code have a different meaning of sort to save it to the server and to open in access:

Sub LockOut()
Dim sFileName As String

'Create a string of the file that you are saving
sFileName = "n:\CostAnalysis\Projects\" & Worksheets THIS "N" DRIVE IS ON A SERVER ("PROJECT_INFORMATION&quot.Range("B2&quot.Value

'Send a message dialog to screen
MsgBox "Saving: " & sFileName

ShowSheets

'Save a write protected copy out to the j:' ActiveWorkbook.SaveCopyAs Filename:= _
' sFileName, WriteResPassword:="deleon", CreateBackup:=False

ActiveWorkbook.SaveAs FileName:=sFileName, FileFormat:= _
xlNormal, Password:="", WriteResPassword:="deleon", ReadOnlyRecommended:= _
False, CreateBackup:=False

HideSheets

End Sub

May be thru the FILEFORMAT .

Thank you for your help.

Darlene Sippio
dsippio@comtechsystems.com

 

[only412c]

Darlene,

I am just a rookie in MS Access but a co-worker told me the acSpreadsheetTypeExcel8 is for Excel97 and the acSpreadsheetTypeExcel9 is for Excel2000. Did you try the latter? I am writing currently writing code to accept files from Excel and would sure like to know the solution to your issue. If the code was working fine, it might be in the environment or platform, I believe.

Juan

 

[DIVINEDAR0956]

Juan,

I am using Access 2002 with Excel 2000.

If I can figure out why you will be the first to know.

I did try 9 and it didn't work either. I wondering is it the fileformat xlNormal could be the reason.

Let me know if you find out anything.

Let me know what else you are trying to do maybe we can help each other. My email address is dsippio@comtechsystems.com. My mom always said two heads are better than one. That's why I love this sight so much.

Darlene Sippio
dsippio@comtechsystems.com

 

[SBendBuckeye]

Below is from help. Try dimensioning sTable and strFileName as Variants. Hope this helps and Good LucK!

TableName Optional Variant. A string expression that's the name of the Microsoft Access table you want to import spreadsheet data into, export spreadsheet data from, or link spreadsheet data to, or the Microsoft Access select query whose results you want to export to a spreadsheet.

FileName Optional Variant. A string expression that's the file name and path of the spreadsheet you want to import from, export to, or link to.

HasFieldNames Optional Variant. Use True (–1) to use the first row of the spreadsheet as field names when importing or linking. Use False (0) to treat the first row of the spreadsheet as normal data. If you leave this argument blank, the default (False) is assumed. When you export Microsoft Access table or select query data to a spreadsheet, the field names are inserted into the first row of the spreadsheet no matter what you enter for this argument.

Range Optional Variant. A string expression that's a valid range of cells or the name of a range in the spreadsheet. This argument applies only to importing. Leave this argument blank to import the entire spreadsheet. When you export to a spreadsheet, you must leave this argument blank. If you enter a range, the export will fail.

 

[sxschech]

We have had problems in the past importing/exporting excel files and here are two things to try that "fixed" them. Although it seems totally unrelated to the issue since it changes a setting in the text section rather than excel, but this worked. Go to the registry:
HK_Local_Machine,SOFTWARE,Microsoft,Jet,4.0,Engines,Text Once there check the following entry under format (in my registry it is the seventh item in the list):

Format REG_SZ CSVDelimited

If yours says something else, try changing it to CSVDelimited.

Another thing to try if that doesn't work is to do an Office Repair. Go to Start, Settings, Control Panel, Add Remove Programs, Microsoft Office 2000 SR-1 Professional, Change, Repair Office.

 

[SteveEAA]

You have to put a dollar sign($) after the worksheet name. See

http://msdn.microsoft.com/library/d...-us/office97/html/workingwithspreadsheets.asp

 

[SBendBuckeye]

SteveEAA,

Your link refers to opening Excel from within Access, not to importing or exporting.

Thanks

 

[DIVINEDAR0956]

SteveEAA

You lost me. I have a script importing the table into access. That's what seem to be my problem is the following:

Public Function updatelink()
'EditProjectNumber

Dim dbsDocument As Database
Dim rstDocument As Recordset
Dim sTable As String
Dim strFileName As String

strFileName = FileDialog

'Search for the temporal file name in access. Delete the table when found


' Add your TransferSpreadsheet code here...
sTable = "Project"
DeleteTable (sTable)
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, sTable, strFileName, True, "PROJECT_EXTRACT"
sTable = "TASK"
DeleteTable (sTable)
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, sTable, strFileName, True, "TASK_EXTRACT"
sTable = "HRSENTER"
DeleteTable (sTable)
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, sTable, strFileName, True, "HOURS_EXTRACT"
sTable = "RATE"
DeleteTable (sTable)
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, sTable, strFileName, True, "RATE_EXTRACT"
sTable = "VENDOR"
DeleteTable (sTable)
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, sTable, strFileName, True, "VENDOR_EXTRACT"
End Function

I am having trouble with acSpreadsheetTypeExcel8

acSpreadsheetTypeExcel8 does import the spreadsheet to access? right.

Darlene Sippio
dsippio@comtechsystems.com

 

[VBslammer]

I played with this for a bit and the only way I was able to mess up the import was to save my Excel file as a .WKS file and then try to import it. The error message was:

  Run-time error '3274':

  External table is not in the expected format.

Your Excel file must have something wrong with it if it's supposed to be in Excel 2000 format.

Constant values(note - some are the same value):

       0 acSpreadsheetTypeExcel3 
       6 acSpreadsheetTypeExcel4

       5 acSpreadsheetTypeExcel5
       5 acSpreadsheetTypeExcel7

       8 acSpreadsheetTypeExcel8 (default)
       8 acSpreadsheetTypeExcel9

       2 acSpreadsheetTypeLotusWK1
       3 acSpreadsheetTypeLotusWK3
       7 acSpreadsheetTypeLotusWK4
       4 acSpreadsheetTypeLotusWJ2 — Japanese version only

Also note this from the documentation:

Microsoft Access also no longer supports importing, exporting, or linking data from Lotus .WKS or Microsoft Excel version 2.0 spreadsheets by using the TransferSpreadsheet method.


VBSlammer

Unemployed in Houston, Texas

 

[DIVINEDAR0956]

Okay guys, I figured out what my problem was. In the sheet that you are transferring into access from excel, the very first cell in excel has to have a value or text in it. If it doesn't it will not transfer the spreadsheet and that is why I was getting the error. Thank you for your help.

Darlene Sippio
dsippio@comtechsystems.com