Data from Excel to Access by code (VB6)

Topic

New Reply

I got requirement to transfer full data from columns of Excel sheets to Access tables and this has to be done by VB code (e.g. by press a button in a VB application). I guess someone somewhere have done such job as they are within MS Office and the two (Excel and Access) are so widely used. Can someone advise where I can find similar code or what basic syntax I need to follow if there is no code available.

Thanks,
David

Reply | Quote

Replies

It’s pretty straightforward to import an Excel worksheet into Access. There are several ways to do it, and it depends on how automated you want to make it. It will however requrire some design work in Access to establish how you are going to store the data. For starters, look at the File/Get External Data/Import command in Access – it will give you some idea of the process and the information needed. Another VBA command in Access is TransferSpreadsheet – it will perform all sorts of different import and export functions, and can also be used to link to an Excel spreadsheet if you don’t actually want to store it in Access. Finally, there is OLE Automation which lets you run Excel commands from Access (or Word, PowerPoint, etc.).

My guess is that one of the first two choices should satisfy your requirement. (I am presuming you don’t actually have to do this in VB6 but could use VBA in Access – if you cannot do that, and must write a VB6 app, you have a much more complicated problem.)

Reply | Quote

Thanks Wendell,

Unfortunately, the request is to do this by VB6, as my subject title mentioned. I knew it coulbe done by hand from Access, but I need a code to do the same and the code is to be embeded in VB6, not VBA in Access or Excel. There has been a VB application to communicate w/ Access now, which needs to add such a control function to import data from Excel directly without touching Access by hand operation. This code will combine with validation program to control valid data import from Excel and filter/find out some invalid data for the users.

Is it very complicated? I dont know. Can VB application (independent from Access/Excel) include some VBA codes? However, I guess if it can be done easily by hand, and VB and VBA are within one family, it shouldnt have too much trouble to have relevant code. The problem to me is unaware those back code/syntax related to the hand operation, but someone should know that. Your suggestion of VBA command TransferSpreadSheet is a good example. There should be more commands/in-built functions/properties to support the whole operation in addtion to this command.
Thank you again.
David

Reply | Quote

I’m afraid you will need help from someone else, as we RARELY use VB to do anything that can be done in Access. It always takes 3 or 4 times as long in VB, and for something like this it will probably be 20 to 30 times longer, as the code to do this from Access is very simple. Perhaps one of the other loungers will have had experience in doing things like this from VB. Or you could challenge the request! Not always and easy thing to do unfortunately.

Reply | Quote

If your users have Access, you can use Automation to do it:

• Set a reference to the Microsoft Access x.0 Object Library in Project/References…
• Create an Access object:
  Dim appAccess As Access.Application
  Set appAccess = CreateObject(“Access.Application”)
• Now you can use Access VBA, for instance
  appAccess.OpenCurrentDatabase “pathfile.mdb”
  appAccess.DoCmd.TransferSpreadsheet …
• Don’t forget to quit Access and destroy the Access object when you’re done:
  appAccess.Quit acQuitSaveNone
  Set appAccess = Nothing
  [/list]Using Access generates quite a bit of overhead, but it will save you a large amount of coding.

Reply | Quote

THanks Hans, your advice is critical to me as you gave me the syntax/ref for how to embed the Access VBA in VB.
Since had a bit experience doing VBA for Excel before, I thought it might be a quick way to solve the issue if I could “record” the hand operation in Access as VBA code, then embed into VB. However, Access doesnt have Record New Macro function as Excel does and its Macro creation is also not flexible as Excel. I waited for a couple of days to update my Office2k to have “Convert Macros to VB” feature loaded (Tool|Macro|..), then the job becomes very easy.
I used Macros|New from DB pane to design a Macro to import a sheet from Excel to Access table, then converted to VB/VBA. Next as Hans suggested, embeded this SHORT code in VB6. I click the button, the job done! The following is my test code in VB6 (with Access, Excel 2000).

Private Sub cmdExcel2Acc_Click()

Dim appAccess As Access.Application
Dim strFileName As String, strShtName As String

Set appAccess = CreateObject(“Access.Application”)

On Error GoTo TestInputMacro_Err

strFileName = InputBox(“Please input the Excel file name (WITHOUT extension ‘.xls’) if different from the default file name:”, “Excel File Name”, “CombAccessTung”)

strShtName = InputBox(“Please define sheet name or close the this input box if only one sheet in the file”, “Work Sheet Name”)

appAccess.OpenCurrentDatabase (“C:tempTestDB.mdb”)

‘THIS IS THE MODIFIED CODE FROM ACCESS VBA (“Test” is the Access table to import):
appAccess.DoCmd.TransferSpreadsheet acImport, 8, “Test”, “c:project” & strFileName & “.xls”, True, strShtName & “!”

MsgBox “Your Excel file data have been tranferred to Access tables!”

adoTestE2A.Refresh ‘the ado and data grid are linked with the DB to show the imported data
dgdTestE2A.Refresh

appAccess.Quit acQuitSaveNone

Set appAccess = Nothing

TestInputMacro_Exit:
Exit Sub

TestInputMacro_Err:
MsgBox Error$

‘to ensure close the application obj
appAccess.Quit acQuitSaveNone
Set appAccess = Nothing

Resume TestInputMacro_Exit

End Sub

The other problems are: 1. Access can import into only one table by this way. I havent got good idea how to import Excel data into multiple and relational tables in one click. 2. I failed to use Range property for importing a part of sheet data. i.e. currently I can import whole sheet data by typing, e.g. “Sheet3” for the Range field, but tried several ways to import say “A2:C20” of the Sheet3 without success. Can someone tell me what the right syntax is to fill the Range field for this?

david

Reply | Quote

For the import of relational data into different tables, it depends of the structure of the data.
If you have the relational structure in the excel file, you can import different part of the data one by one by adding as many Docmd.TransferSpreadsheet lines as needed in your function.
Another method is to import the spreadsheet into a temp table and with code or queries, split the data into different tables.

For the range, add the range to the end of the docmd line :
appAccess.DoCmd.TransferSpreadsheet acImport, 8, “Test”, “c:project” & strFileName & “.xls”, True, strShtName & “!A2:C20”
If you want an input box:
Dim strRange as string
strRange = InputBox(“Please define Range”, “Work Sheet Range”)
appAccess.DoCmd.TransferSpreadsheet acImport, 8, “Test”, “c:project” & strFileName & “.xls”, True, strShtName & “!” & Range
or you could ask the user to input the range in the sheet name and remove the & “!” at the end of the docmd line .
strShtName would have a format like Sheet1!A2:C20

Reply | Quote

Thanks Francois,

I m not so sure abt what is the relational structure in Excel. Nevertheless I cannot control how to make the Excel files but can know their format. I had similar rough ideas as you suggested (multi Docmd’s or temp table). This was why I wanted import by range to transfer diff cols to to diff tables. To create a table on the fly should be a solution if multi DoCmd’s cannot go. Obviously this requires more coding and cost on performance.

The way to define the range you said I tried before but failed simply from Macro in Access. I did, for example, exactly “Sheet3!A2:B20” but received errro msg “Field ‘(cell A2 content here)’ doesnt exist in destination table ‘Test’ when set “Has Field Names” property as “yes”, or “Field ‘F1’ doesnt exist in destination table ‘Test’ when set the same property as “no” (I dont know what ‘F1’ means in the msg).
Do I need to do other changes to achieve the range import (now all other settings same as in my DoCmd line code)?

David

Reply | Quote

When you import data into an existing table, the field names of the Excel range must match the field names of the Access table exactly.
When you import with the HasFieldNames parameter set to False, Access automatically assigns field names F1, F2, etc.

So, you can do one of the following:

• Give your Access table field names F1, F2, etc. and import Excel data with HasFieldNames:=False, or
• Make sure that the Excel range has field names in the first row, that the Access table has exactly the same field names, and import with HasFieldNames:=True, or
• Import the Excel range into a new table and use an append query to insert the imported records into the existing table. In an append query, field names in source and destination don’t have to match.
  [/list]HTH, Hans

Reply | Quote

Thanks Hans. I m interested in only the second option. I dont think many people like the first one – name the DB table field names only as F1, F2, … , not so practical. The last one is a good option but it doesnt make use current Access facility. I however didnt get through the second option after checked the name matching repeatedly and very carefully.

It was a bit frustration when everything done seemed correct but still failed. I didnt touch it for several days until a friend told me the trick. It is not really necessary to have the field names in Excel at the first row although it is common sense. The key point is that you must define the 1st cell for the range input as the same row as the row of field names is at Excel when doing Macro or VB. All my field names in Excel are in the first row and obviously all the data contents are at least from 2nd row downward. Previously I always tried to input range like “A2:B20” or “B5:C50”, etc, like what normally people use Excel ranges. Tthe correct solution is that you have to input “A1:B20” or “B1:C50” to include the field name row in your range input. I later changed the field names in Excel to, e.g. 5th row, then my range input in Macro/VB changed to “A5:B20” or “B5:C50” and successed too.

So my lesson is when “HasFieldName” property is “yes”, Access input range must always start from where the field name row is, NOT from the top row of the data range you want to import. If you want to import only range B5:C50, move the title row to the 4th row, then write the range as “B4:C50”. This will get what you want by bypassing the Access tricky rule!

Maybe I didnt correctly understand what Hans advice was at first, but hope my lesson will save time of someone else when doing this.

David

Reply | Quote

I didn’t realize from your earlier posts that you were turning the HasFieldNames switch on but selecting only the data range. The thread would probably have been a lot shorter if any of us had picked up on that.

Reply | Quote