Transferspreadsheet formatting issues

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
LVL 1
mlcktmguyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)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
0
mlcktmguyAuthor 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.
0
Rey Obrero (Capricorn1)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
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

mlcktmguyAuthor 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.
0
Rey Obrero (Capricorn1)Commented:
<Yes all the files are the same format but converting them all to CSV is not an option.>

WHY?
0
mlcktmguyAuthor Commented:
"In this situation I have to adapt to the customer requirements. "
0
Rey Obrero (Capricorn1)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.
0
Rey Obrero (Capricorn1)Commented:
- another option is to create link to the excel file
- create an append query using the link excel file to the destination table.
0
Rey Obrero (Capricorn1)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.
0
mlcktmguyAuthor 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?
0
PatHartmanCommented:
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.
0
mlcktmguyAuthor Commented:
PatHartman: I don't see an 'Append' option on the Transferspreadsheet command.  Only acExport, acImport and acLink.
How do I accomplish the append?
0
mlcktmguyAuthor 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.
0
Rey Obrero (Capricorn1)Commented:
can you upload a copy of one of the excel file.. remove sensitive info.
0
PatHartmanCommented:
It is part of the External Data dialog.Excel Append
0
mlcktmguyAuthor 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
0
Rey Obrero (Capricorn1)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

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mlcktmguyAuthor 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
0
Rey Obrero (Capricorn1)Commented:
if it is always the first tab
replace
 .worksheets("active files").Select

wirh

 .worksheets(1).Select
0
mlcktmguyAuthor 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.
0
Rey Obrero (Capricorn1)Commented:
@mlcktmguy


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



.
0
mlcktmguyAuthor Commented:
Absolutely not, how do I undo my mistake?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.