Import Excel Files

[abenitez77]

I am looping thru and linking excel files. Sometimes there is data with the top row having title names. The problem is that when the column names are like "date" and "quatity" then when it links in what shows in the cell is "#Num!". How can I get around this so I see "date" and the other field names.? I don't want to set to true for the headers arguement because I want the columns to have the F1,F2,F3 etc...

Set wkb = objXL.Workbooks.Open(strFileName)
   ShtCount = wkb.Sheets.count
   i = 1
   x = 1

   For Each wks In wkb.Worksheets
        ' Link each sheet of the entire Spreadsheet file -----------------------
        'DoCmd.TransferSpreadsheet acLink, , _
        '      shortFilename & "_~" & wks.Name, strFileName, False, wks.Name & "$"

        'Progress Bar :::::::::::::::::::::::::::::::::::::
        RetVal = SysCmd(acSysCmdInitMeter, "Linking FILE: " & PathFilename & " - SHEET: " & Trim(str(x)) & " of " & Trim(str(ShtCount)), ShtCount)
        'Update the progress meter.
        RetVal = SysCmd(acSysCmdUpdateMeter, i)

        'TabName = wks.Name
        DoCmd.TransferSpreadsheet acLink, , _
              "TmpLinkXLS", strFileName, False, wks.Name & "$"

        ' Get the list of field names
        'Set tdf = db.TableDefs("[" & shortFilename & "_~" & wks.Name & "]")
        Set tdf = db.TableDefs("TmpLinkXLS")

        ' ---- Pause 1 second ------
        Const cTIME = 1000 'in MilliSeconds
        Call sSleep(cTIME)

        myfields = ""
        For Each fld In tdf.Fields
            myfields = myfields & "[" & fld.Name & "],"
        Next
        'myfields = Mid(myfields, 1, Len(myfields) - 1)
        i = i + 1
        RetVal = SysCmd(acSysCmdInitMeter, "Inserting FILE: " & PathFilename & " - SHEET: " & Trim(str(x)) & " of " & Trim(str(ShtCount)), ShtCount)
        'Update the progress meter.
        RetVal = SysCmd(acSysCmdUpdateMeter, i)

        ' Insert the data from the sheet into the local XLSData Table --------------------------
        'strsql = "Insert Into XLSData(" & myfields & "Fullimagepath,TabName" & ") Select " & myfields & Chr(34) & strFileName & Chr(34) & " as Fullimagepath, " & wks.Name & " as TabName From [" & shortFilename & "_~" & wks.Name & "] "
        strsql = "Insert Into XLSData(" & myfields & "Fullimagepath,TabName" & ") " & _
                    " Select " & myfields & Chr(34) & strFileName & Chr(34) & " as Fullimagepath, " & Chr(34) & wks.Name & Chr(34) & " as TabName " & _
                    " From TmpLinkXLS As x1 " & _
                    " Where Len(Trim(x1.F1)) > 0 OR Len(Trim(x1.F2)) > 0 OR Len(Trim(x1.F3)) > 0 OR Len(Trim(x1.F4)) > 0 OR Len(Trim(x1.F5)) > 0 OR Len(Trim(x1.F6)) > 0 OR Len(Trim(x1.F7)) > 0 OR Len(Trim(x1.F8)) > 0 OR Len(Trim(x1.F8)) > 0 "
        CurrentDb.Execute strsql, dbFailOnError

        ' ---- Pause 1 second ------
        'Const cTIME = 1000 'in MilliSeconds
        Call sSleep(cTIME)

        i = i + 1
        RetVal = SysCmd(acSysCmdInitMeter, "Dropping TmpLinkXLS", ShtCount)
        'Update the progress meter.
        RetVal = SysCmd(acSysCmdUpdateMeter, i)

        If TableExists("TmpLinkXLS") Then
            CurrentDb.Execute "Drop Table TmpLinkXLS", dbFailOnError
        End If

        Set tdf = Nothing

        RetVal = SysCmd(acSysCmdInitMeter, " ", 0)
        RetVal = SysCmd(acSysCmdRemoveMeter)

        i = 1
        x = x + 1

        'DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
        '      wks.Name, strFileName, True, wks.Name & "$"
   Next

 

[CJ_London]

perhaps use a query rather than transferspreadsheet - modify this as a query - start with something like this

[COLOR=#1f497d][COLOR=black]SELECT *[/COLOR][/COLOR]
[COLOR=#1f497d][COLOR=black]FROM [[COLOR=red]mysheetname[/COLOR]$[COLOR=red]A:T[/COLOR]] IN ‘[COLOR=red]C:\myfolder\myworkbook.xlsx[/COLOR]’[[COLOR=darkorange]Excel 12.0 Xml; HDR=YES; IMEX=1; ACCDB=YES;[/COLOR]][/COLOR][/COLOR]

you'll need to change the bits in red to whatever the value of wks.Name & "$" is - remove the columns if not required and change the file location, but I recommend using them in case the spreadsheet bloats with lots of empty columns. The connection string in orange depends on your excel version - it will be different for .xls.

then go into sql and add the additional code below - it is not always necessary but I have found it prevents some errors

[COLOR=#1f497d]
[COLOR=#1f497d][COLOR=red]SELECT * FROM[/COLOR][/COLOR]
[COLOR=#1f497d][COLOR=black][COLOR=red]([/COLOR]SELECT *[/COLOR][/COLOR]
[COLOR=#1f497d][COLOR=black]FROM [mysheetname$A:T] IN ‘C:\myfolder\myworkbook.xlsx’[Excel 12.0 Xml; HDR=YES; IMEX=1; ACCDB=YES;][/COLOR][COLOR=red]) xl[/COLOR][/COLOR]
[/COLOR]

now you can return to the querygrid and change to an append/update/whatever query.

So now you have the full sql, you can transfer back to VBA and substitute table, field and file names as you have for your insert query

Benefits are you are not creating temporary objects like linked tables and the query should still work even if the excel file is open. And of course if you have any data manipulation to do it can be done in the same step.

missed a bit about what you wanted - basically to skip the first row so try

FROM [mysheetname$A2:T2]

and in the connections change HDR from true to false.

 

[abenitez77]

Sorry for late response. I got busy on another project. I tried this below and it is leaving out the first row which has the field names. I want the field names to be part of the data. They are being cut off and I don't see it.

strsql = "SELECT * Into TmpLinkXLS FROM " & _
"(SELECT * " & _
"FROM [" & wks.Name & "$A:Z] IN '" & strFileName & "'[Excel 12.0 Xml; HDR=NO; IMEX=1; ACCDB=YES;]) xl "

DoCmd.RunSQL strsql

 

[abenitez77]

nevermind...i reran it and it's fine. Only thing now is that it prompts me to create the table..or insert into the table. How can i remove the prompts?

 

[CJ_London]

perhaps use currentdb.execute rather than docmd.runsql

 

[abenitez77]

Found it...setwarnings false. Code is running, but the speed is not anything better with the query instead of linking the excel file.

 

[CJ_London]

but the speed is not anything better with the query instead of linking the excel file

I wasn't suggesting it would be faster, I was addressing the issue of you wanting to bring the headers through, and the #num problem

 

[abenitez77]

Sorry, I did not want to come across that way. I do appreciate your help. thanks, i did learn a different way of doing the same thing.

 

[CJ_London]

I wasn't offended! One of the benefits of using the sql method is you can bring in multiple ranges in the same 'table' e.g. cols A,B, E,F, X,Y, Z which cannot be done by transferspreadsheet or linking

 

[abenitez77]

Now I'm having formatting issues. The date fields are coming up as integers and a column that has long numeric values are coming up as scientific values. How can i get them to insert into ms access table with the values formatted correctly?

 

[CJ_London]

importing from excel is a PITA. I've found the only surefire way is to ensure everything is text.

You can try changing the IMEX value to 2 in the connection string - but will probably find it creates problems elsewhere.

so in excel, ensure any large number has a single quote in front of it and dates are formatted as text using the text function