importing xls in access

  • Thread starter Thread starter karolientje_
  • Start date Start date
K

karolientje_

Guest
Hello,
I'm trying to import xls files in access, but it always gives a failure notice: .xls contains no object. What does this mean? Please help me - urgent!
Thanks.
please e-mail me (karolien.hellemans@leuven.be)
 
two ways

karolientje,

You haven't said how you tried to import. There are two ways to do it, either by code or direct importing.

1.) By code: Using a button in a form for example, you can use the following code:

Private Sub Command0_Click()
DoCmd.TransferSpreadsheet acImport, _
acSpreadsheetTypeExcel9, "Sheet1", "C:\Temp\Table1.xls", True, "A1:C11"
End Sub


2.) Direct import: Select File: Get external data: Import:
Then make sure that 'file type' is on 'Microsoft Excel' and select your file.

Hope this helps
 
Please Elaborate

If I use the "Coded" method to import, could I add a input box on the form where I can specify the file name, how would this reflect in the code mentioned above? Also if I had a button that would allow me to search for the file in a dir structure and then this would populate the input box with the file location and name.

Could someone help with this?
 
Last edited:
I would also find this very useful if anyone one could come up with a solution.

Thanks
 
Why don't you just go to Tables, select New, then Import Tables? It allows you to browse to your datasource and import easily.

I do that all the time and never have trouble. Just make sure that your Excel sheet tabs are one word with no odd characters and NO SPACES. Same goes for the column titles. Works every time.
 
.xls contains no object.
Click to expand...

Does Excel open this file correctly itself?

Normally, I would expect this message if someone had renamed a file to a type of .XLS (from something else) without having gone through Excel to do it. This is a reference to what appears to be an empty file, but it could also be a file that is just not publishing its component objects (under the Component Object Model). Also, is this the same major version of Access and Excel? I.e. both from Office 97 or both from Office XP or whatever?
 
The only ant.

vangogh228 said:
Why don't you just go to Tables, select New, then Import Tables? It allows you to browse to your datasource and import easily.

I do that all the time and never have trouble. Just make sure that your Excel sheet tabs are one word with no odd characters and NO SPACES. Same goes for the column titles. Works every time.
Click to expand...

If I was the only person that would work with the dB then I would concider just importing from the file menu, but I am building this for people who have very little dB skill(Less than I do) and I would like to make this as easy as possible. This means creating a form that they can use to just point to the file to be imported and the code will import, append the table automatically.
 
could you use what i have done?

Are the spreadsheets standardized?

I wrote an access database for collection forecasts from 160 districts around North America where each district manager filled in an excel spreadsheet, because that's what they could deal with on a stand alone basis, and then sent an excel one tab extract of all the numbers to be imported or updated as necessary. . . this is still being done on a monthly basis, and the import or forecast update is only a click of a button for all of them at once.

the key was that the excel spreadsheet was standardized, and had a named range that access attached to as a table. also, each district's excel had VB code that created the extract with standardized names so that the import could go smoothly, or if it wasn't there, then we could go look for it. . .

would this code help you out?

sportsguy
 
Looking for similar

Hi Sportsguy -- I am trying to do something similar, and what you've done might help me immensely. What I was hoping to do was put a button in Excel that would drive the data right into a table, but I am not comfortable enough with Excel to do this. If it's not too much to ask, what you've done might be really helpful for me.
 
OK, The spreadsheet names are in a table,

All spreadsheets have a named Range, "DATA", the spreadsheets have standardized names from a table, with all the rows that need to be imported.

Other possibilities could be to read the files in the desired directory,
put the file names in the control table, and then go through the routine.

Its easier to get Excel into Access from Access than from Excel.

good luck,
sportsguy


Code: 
Private Sub MassImport_DblClick(Cancel As Integer)

'import query code
importSQL = "INSERT INTO FORECAST ( YYYYMM, [Year], District, ProductLine, Type, Account, OCT, NOV, [DEC], QTR1, JAN, FEB, MAR, QTR2, APR, MAY, JUN, QTR3, JUL, AUG, SEP, QTR4, FYTOTAL, ImportTime ) " _
    & "SELECT DATA.YYYYMM, DATA.Year, DATA.District, DATA.ProductLine, DATA.Type, DATA.Account, DATA.OCT, DATA.NOV, DATA.DEC, DATA.QTR1, DATA.JAN, DATA.FEB, DATA.MAR, DATA.QTR2, DATA.APR, DATA.MAY, DATA.JUN, DATA.QTR3, DATA.JUL, DATA.AUG, DATA.SEP, DATA.QTR4, DATA.FYTOTAL, DATA.LastSave " _
    & "FROM DATA;"

' if combo box for path Null, go back and select path in 
If IsNull(Me!ComboPaths) Then
    MsgBox "Please Select the folder path in the Drop Down Box", vbCritical, "ComboBox Error"
    DoCmd.GoToControl "ComboPaths"
    Exit Sub
Else
End If


On Error Resume Next
DoCmd.DeleteObject acTable, "DATA"

Dim cnnLocal As New ADODB.Connection
Dim rstCurr As New ADODB.Recordset
Set cnnLocal = CurrentProject.Connection

'Get file name from Control Table
rstCurr.Open "SELECT CONTROL.ImportFileName FROM Control WHERE (((CONTROL.Visible)=Yes) AND ((CONTROL.Imported)=No)); ", cnnLocal, adOpenStatic, adLockPessimistic

With rstCurr
    Do Until .EOF
    For Each fldCurr In .Fields
        
        'Concatenate Path from combobox with filename from Control table        
        anyFileName = fldCurr.Value
        anyPath = Forms!MassImport.[ComboPaths] & "\" & anyFileName & ".xls"
        
Debug.Print anyPath
    On Error GoTo SecondTry
    DoCmd.SetWarnings False
            
    If Dir(anyPath) <> "" Then
            DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel9, "DATA", anyPath, True, "ACCESSPASTE"
            DoCmd.RunSQL importSQL
            DoCmd.DeleteObject acTable, "DATA"
            GoTo Done
            
        Else
        End If
    
    Next

    .MoveNext

    Loop

End With

rstCurr.Close
Set cnnLocal = Nothing
Set rstCurr = Nothing

MsgBox "Available Files Imported Successfully", vbOKOnly, 

importedSQL = "UPDATE FORECAST INNER JOIN (CONTROL INNER JOIN DISTRICTS ON CONTROL.HyperionDistrict = DISTRICTS.HyperionDistrict) ON FORECAST.District = DISTRICTS.District " _
    & "SET CONTROL.Imported = Yes WHERE (((FORECAST.Locked)=No)); "

DoCmd.SetWarnings False
DoCmd.RunSQL importedSQL
DoCmd.SetWarnings True

Exit_MassImport_DblClick:
    DoCmd.SetWarnings True
    Exit Sub

Err_MassImport_DblClick:
    MsgBox Err.Description
    GoTo Done

End Sub
 
Last edited:
There is a very simple solution for importing sheets with a click on a button...

Go to macro's en build an import macro. In the design view of the macro you can select an option to import whatever you want. Specify the desired format and other stuff and your import macro is ready...

Then just go to form design and create a button. After that you just put in the buttons properties the name of the macro as desired action...

No code knowledge necessary to solve this problem you know...
 
Thanks Sportsguy

I appreciate that, thanks for sharing. This isn't really what I was looking for, unfortunately. What I have is a group of users that are collecting and manipulating financial data in Excel spreadsheets. Once the collection/manipulation is complete, I want to capture the final numbers (and some other data) and move it into a database, which then runs an asp view only app.

Rather than dealing individually with the files, I want to be able to have the user click a button in Excel that then exports the data into Access .... and I don't get the sense that this is what you're doing here -- you're still initiating the process from Access, right? I will do it that way if I have to, but if I can push the data from Excel and not have the data collection people use the database, I would be a happy person!

Thanks in advance - for any comments.
 
Last edited:
Then you need to retitle the post,

uploading Excel into Access and
then put that into the Excel area. . . .

and yea, i would like to use that also. . .
I had spreadsheets that did this back in 1995
that were written by a develper, but they were lost
on a old Bernoulli drive.

sportsguy
 
Sportsguy, you're the best

Thanks again -- I am going through the link you provided -- and I may post this to the Excel thread anyway, but will try the link first.

I think this could be pretty useful - I can think of myriad examples where data collection can be done in Excel and ported to Access....

Thanks again - I'll give you my feedback on how this works as soon as I've had a chance to process.
 
Importing dynamic range from Excel

Hi,

Seems like there are some knowledgable importers on this thread so I'll try my question here.

I have created a dynamic named range in Excel using the Offset command however when I attempt to import to an Access table (either using code or the wizard), it cannot find the range, therefore the import fails.

This is the formula referred to in the Insert>Name>Define:
=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),COUNTA(Sheet1!$1:$1))

Any ideas on how to import this range into Access?

Let me know if you need files demonstrating this.

Thanks

Jafa
 
dynamic only works when excel is open
you have to save the name so that excel stores a name
in the rangename field.

sort of makes sense, no?

sportsguy
 
You must log in or register to reply here.

Similar threads

oxicottin
Solved Import from excel and delete unwanted data
Replies
14
Views
633
oxicottin
oxicottin
A
Missing data when exporting from excel to Access
Replies
11
Views
389
Isaac
Isaac
G
  • Locked
Creating all possible Descriptions & Codes
2
Replies
22
Views
1,202
The_Doc_Man
The_Doc_Man
brucemc777
Solved Can one import a named table from Excel into either a new table, or an empty table created for the purpose, in Access?
Replies
1
Views
412
azhar2006
A
M
DoCmd.TransferSpreadsheet no longer trims trailing spaces from field names after conversion to Office 365
Replies
12
Views
933
Gasman
Gasman

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom