Transferspreadsheet formatting issues

mlcktmguy
mlcktmguy used Ask the Experts™
on
I am creating a procedure to import a spreadsheet into an Access 2013 for processing.  Simple enough I've done it dozens of times.

I set up a table 'tblImport' to import the spreadsheet into with a column F1 - F12 to accept each field on the spreadsheet.  Each column in this table is defined as text 50 with no 'Format' or 'InputMask' specified.

I use this code to import the spread into the tblImport
'
wkImportRange = "!A6:L8000"
'
DoCmd.TransferSpreadsheet acImport, _
                          acSpreadsheetTypeExcel8, _
                          inputTable, _
                          passedSpreadsheetNameAndLoc, _
                          False, _
                          wkImportRange

Open in new window


I attached a file showing the pertinent columns in the spread.  Everything works as it should except for most of the columns that may contain a date or a text description, usually the word 'Hold'.

The first column shown in the attached pic works just as I would expect.  The data in that column is imported into column 'F7' in tblImport.  In column F7 in the datasheet view I see rows with a date and rows with the word 'Hold' in them just as I would expect.

The remaining columns, which are all defined as text 50 just like column F7,  don't import the same way.  Only the dates import, the text 'Hold' never does.  It throws a type conversion error.  When I look at those columns in the imported table I only see dates, nothing is in
the rows that had the word 'Hold' in the spreadsheet cell.

I don't understand why column F7 would import properly and the others don't.  They are all defined exactly the same in the table.

It seems like something in the Transferspreadsheet is causing these columns to be looked at a type 'Date', even though I have them defined as text.

I'm pretty sure I ran into this several years ago when importing a spreadsheet and the solution was to add a fairly short and simple piece of VBA code prior to importing the spreadsheet that changed the format of each cell in the import range to something like 'General'.  The VBA then save the spreadsheet prior to the 'Transferspreadsheet' logic being executed.

Unfortunately I don't have Access to the VBA code I created to do the reformatting.

Does anyone have a solution to the problem?  

Perhaps the pre-import reformatting is the only way to go.  In that case can anyone provide an example of the code needed?
SpreadPic.doc
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2016

Commented:
1. prior to importing the excel file, do a sort in the column F7, such that the records with 'Hold' or any text values will be on top .

2. replace your code with

DoCmd.TransferSpreadsheet acImport, _
                          10, _
                          inputTable, _
                          passedSpreadsheetNameAndLoc, _
                          False, _
                          wkImportRange

Author

Commented:
Thanks for the response it is appreciated.  I see what you're getting at.  If the first cell is text, the rest will import as text.  If the first cell is a date the rest will try to import as a date, regardless of how I have them defined in the receiving table in my application.
I must not have explained this well in my original post but all of the columns show with dates may also have 'Hold' in any of the cells.  So even if I make sure I presort column F7 in all of the spreadsheets coming I will still have an issue with any of the other columns in which a date appears prior to the ‘Hold’ text.
I should also mention the fact that there will 300 of these spreadsheets to process at a time.  So whatever has to be done will have to be done in VBA code   Doing it manually is not feasible.
Hopefully there is a solution.
By the way, I changed the spreadsheet type to 10, even though these are .xls spreadsheets but the result was the same.
Top Expert 2016

Commented:
are all of the 300 spreadsheet have the same format?
will they be imported to the same table?

one solution is to convert the excel file to .CSV


- import one file and create an import specification

To create import specification :
http://www.experts-exchange.com/Database/MS_Access/Q_28129404.html

- the rest of the .csv files can be imported using the command line
 
example : http://www.experts-exchange.com/Database/MS_Access/Q_28602198.html
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
Yes all the files are the same format but converting them all to CSV is not an option.  I understand how CSV be would better and I have developed many sets of import specs.  The frustrating part of importing spreadsheets is you can't do an import spec.  In this situation I have to adapt to the customer requirements.
Top Expert 2016

Commented:
<Yes all the files are the same format but converting them all to CSV is not an option.>

WHY?

Author

Commented:
"In this situation I have to adapt to the customer requirements. "
Top Expert 2016

Commented:
I really don't understand...

anyway, you can still use vba codes

- open the table as recordset
- open the excel file and read each cell and add the records to the recordset.
Top Expert 2016

Commented:
- another option is to create link to the excel file
- create an append query using the link excel file to the destination table.
Top Expert 2016

Commented:
and I forgot,
-import the excel file using the command line that you are using now, but instead of directly appending to the table, use a tempTable
- transfer the records from the tempTable to the final table using an append query with  fields formatted accordingly.

Author

Commented:
Thanks for your responses.  I appreciate you taking an interest in my situation.  
Of the suggestions you've given so far the one which seems most probable is "
- open the table as recordset
- open the excel file and read each cell and add the records to the recordset.

I can connect the spreadsheet to a recordset but don't know how to read each cell.

In the meantime I checked thru my old code and found the VBA loop that will alter the characteristics of specific cell in an Excel spreadsheet.

Here is the code that alters the formatting of columns F - P :
Private Sub fixSpreadsheetNumFormat(passedNameAndLoc As String)
 
Dim xlApp As Object, xlWb As Object, xlWs As Object
 
Set xlApp = CreateObject("Excel.Application")
Set xlWb = xlApp.Workbooks.Open(passedNameAndLoc)
 
Set xlWs = xlWb.Worksheets(1)
xlWs.Range("F:P").NumberFormat = "0"
'repeat as needed
 
With xlWb
    .Save
    .Close
End With
 
Set xlWs = Nothing
Set xlWb = Nothing
xlApp.Quit
Set xlApp = Nothing
End Sub

Open in new window


Using a modified version of this loop that alters the format of the cells to 'General' seems to be the most efficient option.  Successfully altering all the cells to 'General' format will import the cells with 'Hold' and the cells with dates but the dates come in as numbers, '3456789' rather than '01/01/15'.  

To use the above loop, I need to know how to revise it so instead of revising the .Numeric property of the cells I need to revise the cell formatting to 'General' .  I don't know how to do that, do you?

I also need to know how to convert the dates imported as numbers '3456789' back into dates.

Any ideas?
Distinguished Expert 2017

Commented:
You don't need automation code to do this.  You can do it by appending to a local table.  Define the table to have text fields.  Then using the TransferSpreadsheet, specify append rather than import as the option.

You will need to delete the contents of this table prior to doing the TransferSpreadsheet.  Also, keep in mind that this temp table will cause bloat so you will need to compact the database more frequently than you have been.

Dates are stored internally as double precision numbers.  The integer part represents the number of days since the origin date (0 value).  In Access, the 0 date is Dec 30, 1899.  In Excel, I think it is Jan 1, 1900 but the concept is the same.

Use the append method and just import the dates as date strings.  After you import to the temp table, you will need to append to the real table where I seriously hope that you have dates defined as DateTime and "hold" is a status flag which is a separate field.

Author

Commented:
PatHartman: I don't see an 'Append' option on the Transferspreadsheet command.  Only acExport, acImport and acLink.
How do I accomplish the append?

Author

Commented:
Rey Obrero: I appreciate all of your suggestions.  I got pulled off this task for a few days.

As you suggested, I created a test that linked the spreadsheet to the DB as an input table.  I read the table line by line and processed each cell individually.
This didn't work any better than the Tranferspreadsheet import.  When I researched I saw that when the link was created the fields that contained dates were defined in the link table as 'Date/Time'.  Since it's a linked table I cannot alter the data definitions and this caused issues when the rows with 'Hold" in those columns were read.

I still haven't been able to successfully import this spreadsheet.  Any other ideas would be welcome.

I got some input from an EE'er and was able to revise the spreadsheet prior to import, chnaging all of the columns to format 'General'.  This was still unsuccessfull with the columns containing 'Hold" not being imported.

Here's the revised code that reformats the spreadsheet prior to import

Public Sub fixSpreadsheetToGeneralFormat(passedNameAndLoc As String)
 
Dim xlApp As Object, xlWb As Object, xlWs As Object
 
Set xlApp = CreateObject("Excel.Application")
Set xlWb = xlApp.Workbooks.Open(passedNameAndLoc)
 
Set xlWs = xlWb.Worksheets(1)
'xlWs.Range("F:P").NumberFormat = "0"
xlWs.Range("A6:M" & xlWs.UsedRange.Rows.Count).NumberFormat = "General"
'repeat as needed
 
With xlWb
    .Save
    .Close
End With
 
Set xlWs = Nothing
Set xlWb = Nothing
xlApp.Quit
Set xlApp = Nothing
End Sub

Open in new window


Any other ideas would be appreciated.
Top Expert 2016

Commented:
can you upload a copy of one of the excel file.. remove sensitive info.
Distinguished Expert 2017

Commented:
It is part of the External Data dialog.Excel Append

Author

Commented:
PatHartman: Thanks for showing where the append is located.  However, there are 300 spreadsheets to be imported an processed so whatever I use has to be available in VB code.  

I finally created and attached a scrubbed version of an example spreadsheet to be imported.

Here is the code I am using to import the data cells, which begin in row 6.

'
wkImportRange = "!A6:L8000"
'
DoCmd.TransferSpreadsheet acImport, _
                          10, _
                          inputTable, _
                          passedSpreadsheetNameAndLoc, _
                          False, _
                          wkImportRange

Open in new window


I also attached a word file with snapshots of the table definition of the table I am importing into and the result of the import.

The issues is shown in the resultant table.  The cells in row F8 and F9 that contain the word 'Hold' on the imported spreadsheet are not being imported into the resultant table.  For some reason the 'Hold' cells in row F7 and F10 are being imported.

I need all cells in all rows to be imported and the process must be doable in VB code.  Thaks for your interest in my issue.  Any help will be appreciated.
EETestVers.xls
EETableDefAndContents.doc
Top Expert 2016
Commented:
test this codes,
change xlFile path to the actual path of the file

Sub ImportExcelRecords()
Dim xlFile As String
xlFile = CurrentProject.Path & "\EETestVers.xls"
Dim rs As DAO.Recordset, xlObj As Object, j As Integer
Dim vRow As Integer, colCnt As Integer
colCnt = 12: vRow = 6
Set rs = CurrentDb.OpenRecordset("EE_Import")

Set xlObj = CreateObject("excel.application")
    xlObj.Workbooks.Open xlFile
    With xlObj
        .Visible = True
        .worksheets("active files").Select
            .cells(vRow, 2).Select
            Do
                rs.AddNew
                    rs!F1 = .cells(vRow, 1).Value
                    rs!F2 = .cells(vRow, 2).Value
                    rs!F3 = .cells(vRow, 3).Value
                    rs!F4 = .cells(vRow, 4).Value
                    rs!F5 = .cells(vRow, 5).Value
                    rs!F6 = .cells(vRow, 6).Value
                    rs!F7 = .cells(vRow, 7).Value
                    rs!F8 = .cells(vRow, 8).Value
                    rs!F9 = .cells(vRow, 9).Value
                    rs!F10 = .cells(vRow, 10).Value
                    rs!F11 = .cells(vRow, 11).Value
                    rs!F12 = .cells(vRow, 12).Value
                rs.Update
                .ActiveCell.Offset(1, 0).Activate
                vRow = vRow + 1
                If .ActiveCell.Value = "" Then Exit Do
            Loop
    End With
    xlObj.Quit
    rs.Close
End Sub

Open in new window

Author

Commented:
Wow, that is outstanding, I wish I could give you more than 500 points.

Just one requested revision to the logic:  This statement:

        .worksheets("active files").Select

pulls the data from the 'Active files' worksheet.  Theoretically all of the spreadsheets being imported will have the first tab with the name 'Active files'.  However, knowing that users can sometimes mis-spell or arbitrarily decide they don't like the name 'Active files':

Is there a way the above statement can be revised to always select the first tab on the worksheet. rather than 'Active Files' specifically.  Most of the spreadsheets will come in with data on other tabs ( I removed it in the scrub) but the data I'm interested will always be on the first tab.

Thanks much
Top Expert 2016

Commented:
if it is always the first tab
replace
 .worksheets("active files").Select

wirh

 .worksheets(1).Select

Author

Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for mlcktmguy's comment #a40954912

for the following reason:

Perfect.  Thank you so much for staying with my issue until it was resolved.  You are definitely an all-star.
Top Expert 2016

Commented:
@mlcktmguy


are you sure you want to accept your comment as the solution???



.

Author

Commented:
Absolutely not, how do I undo my mistake?

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial