Import and Modify a .XLSB

User avatar
GARYPSWANSON
Lounger
Posts: 37
Joined: 07 May 2014, 14:16

Import and Modify a .XLSB

Post by GARYPSWANSON »

Using Access 2010, Excel 2010, Windows 7 Enterprise with Service Pack 1

Have been requested to import one of many tabs of data from an .XLSB formatted excel file, modify the data, and then send the modified results back into a new .XLSB file. Issues are that there are many cells in text format with greater then 254 characters. Do not expect the .xlsb tab being loaded to exceed 65536 rows. Original .xlsb has many tabs with pivot tables. (For info only - don't need them).

I have done this in the past using Automation on .XLS files, howver, can't seem to get the .xlsb file to load.

Is it possible to use Automation to import data from an .XLSB file? If not, is it possible to use automation to import a .XLSX file? I can open the .XLSB file via code as I can see the tab names, however, and it seems the only way to import the data would be to do it cell by cell as the transferdatabase command does not appear to work. Any ideas?

Thanks,

Gary
Regards,

Gary

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Import and Modify a .XLSB

Post by HansV »

Hi Gary, long time no see.

To import or export a .xlsb workbook, specify acSpreadsheetTypeExcel12 as SpreadsheetType; to import or export a .xlsx workbook, use acSpreadsheetTypeExcel12Xml.

E.g.:

DoCmd.TransferSpreadsheet TransferType:=acImport, SpreadsheetType:=acSpreadsheetTypeExcel12Xml, TableName:="MyTable", FileName:="C:\Excel\MyWorkbook.xlsx", HasFieldNames:=True

or

DoCmd.TransferSpreadsheet TransferType:=acExport, SpreadsheetType:=acSpreadsheetTypeExcel12, TableName:="MyTable", FileName:="C:\Excel\MyWorkbook.xlsb", HasFieldNames:=True
Best wishes,
Hans

User avatar
GARYPSWANSON
Lounger
Posts: 37
Joined: 07 May 2014, 14:16

Re: Import and Modify a .XLSB

Post by GARYPSWANSON »

Thanks Hans. Yes, it has been a long time! Hope you are well.

Let me try this again. I did attempt this as you indicate, however, received an error. Let me try it again to see if it works. I will post the error should it continue.

Gary
Regards,

Gary

User avatar
GARYPSWANSON
Lounger
Posts: 37
Joined: 07 May 2014, 14:16

Re: Import and Modify a .XLSB

Post by GARYPSWANSON »

Hans,

Used the transferdatabase command as specified (tried both ways even though data is from an .XLSB) and receive a Run Time 3274 Error, External Table is not in Specified Format. Ran Updates, re-booted, and tried again yet still receive the same error.

The .XLSB file does open via code, I then select the tab of data I need, but can't get the data to transfer using the transfer database command. Any suggestions on a work around to transfer the data?

Thanks,

Gary
Regards,

Gary

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Import and Modify a .XLSB

Post by HansV »

Weird - I tested the code in both Office 2010 and Office 2013; it works without errors for me. Is the sheet you want to import the first sheet in the workbook? By default, Access tries to import the first sheet. If you want another sheet, you have to specify the Range argument:

DoCmd.TransferSpreadsheet TransferType:=acImport, ..., Range:="SheetName$"

You might use ADO to open a recordset on the worksheet, then loop through the records and append them to an (empty) Access table.

Or use Automation to open the .xlsb workbook; as you suggest, you'd then have to transfer the data cell by cell.
Best wishes,
Hans

User avatar
GARYPSWANSON
Lounger
Posts: 37
Joined: 07 May 2014, 14:16

Re: Import and Modify a .XLSB

Post by GARYPSWANSON »

The sheet is the first sheet, however, to be safe, I specificed the sheet as well in code. I am not sure what the issue is. I will mess with the code a little and then go the brute force method to transfer the data cell by cell although I would prefer not to do it this way. Thanks for the help.

Gary
Regards,

Gary

User avatar
GARYPSWANSON
Lounger
Posts: 37
Joined: 07 May 2014, 14:16

Re: Import and Modify a .XLSB

Post by GARYPSWANSON »

I am thinking it may have to do with data type inconsistencies. I saved the xlsb file as a new xlsb and corrected any "errors" that were #Error in excel or text in what was supposed to be data fields. I also reduced the size of some very large text fields and the xlsb loaded.
Regards,

Gary

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Import and Modify a .XLSB

Post by HansV »

Sounds like a plausible explanation.
Best wishes,
Hans

User avatar
GARYPSWANSON
Lounger
Posts: 37
Joined: 07 May 2014, 14:16

Re: Import and Modify a .XLSB

Post by GARYPSWANSON »

Hans,

Something to think about. The XLSB data file I was asked to input into Access 2010, besides the data input tab with a lot of VLookups, also has many tabs that are pivot tables and tabs of data used to link to the Vlookups. In addition, the XLSB file has links to other excel files on the network which I am unable to access.

To get the file to load, even with the data inconsistencies, I had to create a new XLSB file (Clean without pivot tables or links to other files) and then copied the data input tab data by selecting all and then went into the new XLSB file and did a Paste Special, Value. This put all of the data, even text fields with greater then 254 characters (which they want) into the new XLSB file. The new XLSB file does load into access without issue and the large text fields load properly into access as long as the data type is Memo.

I am not sure what the issue is, but the import definitly does not like the links to other files even though they are disconnected. I am not sure if the Pivot Tables or VLookups caused any issues either.

Thanks for your quick repsponse. I see you still have 6 fingers!

Gary
Regards,

Gary

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Import and Modify a .XLSB

Post by HansV »

I don't think the pivot tables caused the problem since they were in other sheets. The links to other Excel files do sound like probable suspects - they may prevent Access from accessing the workbook successfully.
Best wishes,
Hans