Solved

Convert .xls to .mdb

Posted on 2009-07-01
3
735 Views
Last Modified: 2012-05-07
Hi Experts,
  Anyway we can convert .xls file to .mdbfile ?
0
Comment
Question by:mkdev2009
3 Comments
 
LVL 23

Accepted Solution

by:
Eirman earned 250 total points
Comment Utility
Assuming you have access ... File > Get external Data > Import ... Then choose "Files of type" XLS
0
 
LVL 3

Assisted Solution

by:Michal-Drozd
Michal-Drozd earned 250 total points
Comment Utility
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:
oApp.quit
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

.ShowOpen

If .FileName <> "" Then strFileName = .FileName

End With

End If
 

'do import of file

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel97, "ImportTable", strFileName, True, "ImportArea"

Exit_cmdImport_Click:

Exit Sub

Open in new window

0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

In my previous article (http://www.experts-exchange.com/Programming/Languages/.NET/.NET_Framework_3.x/A_4362-Serialization-in-NET-1.html) we saw the basics of serialization and how types/objects can be serialized to Binary format. In this blog we wi…
Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
This video discusses moving either the default database or any database to a new volume.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now