• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 873
  • Last Modified:

Convert .xls to .mdb

Hi Experts,
  Anyway we can convert .xls file to .mdbfile ?
2 Solutions
Assuming you have access ... File > Get external Data > Import ... Then choose "Files of type" XLS
Here is one of soultions:
Create the access application object. Open a database (or create new one).
After that you can execute any you want that you can normally do from code in access

Dim oApp As Object
Set oApp = CreateObject("Access.Application")
oApp.Visible= True ' if you want to see it

read up on the following:
OpenDatabase (dbname, options, read-only, connect)

look at:
TransferText(TransferType, SpecificationName, TableName, FileName, HasFieldNames, HTMLTableName, CodePage)
more about this function you find here: http://www.blueclaw-db.com/transfertext-docmd.htm

or better this function (xls to mdb) something like this:
docmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "TMP_TABLE", "c:\my_file.xls", true, "WorksheetName"

Change the True to False if the first row does not contain column headings
example, like so with oApp:
oApp.DoCmd.TransferText( .....enter args)

don't forget when done to:
Set oApp = Nothing

small example:
Private Sub cmdImport_Click()
Dim strFileName As String ' filename we are importing from
'get filename from user to import
If strFileName = "" Then
With ComDlg
.CancelError = True
.Filter = "XLS Files (*.xls)|*.xls" _
& "|All Files (*.*)|*.*"
.DefaultExt = ".ext"
.DialogTitle = "Select File"
.FilterIndex = 1
'.InitDir = "default directory goes here"
.Flags = cdlOFNHideReadOnly
If .FileName <> "" Then strFileName = .FileName
End With
End If
'do import of file
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel97, "ImportTable", strFileName, True, "ImportArea"
Exit Sub

Open in new window


Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now