Excel VBA : Need help

[Tanushri_Kothari]

Hi, need help on excel vba. I have attached an Excel workbook along with Access Database. Password to open the database is 1234. I have placed a button on the excel sheet1. If I click on that button, it should export the data from the access database onto a new excel blank workbook. Please help at your earliest.

 

[Nebu]

This is a duplicate thread , refer http://chandoo.org/forum/threads/excel-vba-import-data-from-access-database.21533/#post-130031

Thanks

 

[Tanushri_Kothari]

This is a duplicate thread , refer http://chandoo.org/forum/threads/excel-vba-import-data-from-access-database.21533/#post-130031

Thanks

Thanks for reply. Surprised to see duplicate thread. I've just tried the code, it runs without error but does not export any data. The tool would be used by 350 people, isn't it possible to export the data without establishing manual connection? My problem stands open

 

[Nebu]

Hi:

It can be done without establishing manual connection, but establishing a manual connection is a one minute job. Even if you have multiple users, if the file is shared over the network the link will still work.It is much better than recording the items one-by-one as record set from the data base.

Thanks

 

[Tanushri_Kothari]

Hi:

It can be done without establishing manual connection, but establishing a manual connection is a one minute job. Even if you have multiple users, if the file is shared over the network the link will still work.It is much better than recording the items one-by-one as record set from the data base.

Thanks

Thanks Sir, can you plz tell me record set method?

 

[Nebu]

Hi:

That will be an entire set of new coding. I need time to code it.

Thanks

 

[Tanushri_Kothari]

Sure,

Hi:

That will be an entire set of new coding. I need time to code it.

Thanks

Sure and thanks so much. I am waiting to see your reply.

 

[Nebu]

Hi:

Please find the attached.

Sub accessimport()

Dim db As Database
Dim ws As Workspace
Dim acsql As String
Dim recSet As DAO.Recordset

Set ws = DBEngine.Workspaces(0)
Set db = ws.OpenDatabase _
("your file path here\myDatabase.mdb", _
    False, False, "MS Access;PWD=1234")
Set recSet = db.OpenRecordset("Raw_Data")
Set rng = Sheet2.Range("A1")
lFieldCount = recSet.Fields.Count

For i = 0 To lFieldCount - 1
rng.Offset(0, i).Value = recSet.Fields(i).Name
Next i
rng.Offset(1, 0).CopyFromRecordset recSet

recSet.Close
Set ws = Nothing
Set db = Nothing

End Sub

you will have to put your file path in the highlighted portion before running the macro.

Thanks

 

[Tanushri_Kothari]

Hi:

Please find the attached.

Sub accessimport()

Dim db As Database
Dim ws As Workspace
Dim acsql As String
Dim recSet As DAO.Recordset

Set ws = DBEngine.Workspaces(0)
Set db = ws.OpenDatabase _
("your file path here\myDatabase.mdb", _
    False, False, "MS Access;PWD=1234")
Set recSet = db.OpenRecordset("Raw_Data")
Set rng = Sheet2.Range("A1")
lFieldCount = recSet.Fields.Count

For i = 0 To lFieldCount - 1
rng.Offset(0, i).Value = recSet.Fields(i).Name
Next i
rng.Offset(1, 0).CopyFromRecordset recSet

recSet.Close
Set ws = Nothing
Set db = Nothing

End Sub

you will have to put your file path in the highlighted portion before running the macro.

Thanks

Hi, sorry for late reply
thank you so much for the great help, it worked perfectly