Board index » Visual Studio » using VB to copy data from excel to access

Visual Studio106
I would like to import an excel spreadsheet into my access database. I can

do this from Access but now I need to do it from a vb6 application. Does

anyone know how I can do this, or know of any links I can visit that gives

example code?



Thank you

Ian


-

"Ian Davies" <iandan.dav@virgin.net>wrote in message



You could use ADO to read the Excel spreadsheet and then use another ADO

connection to write to your Access database. There's no code at the

following link but there is info on the necessary connection string:

http://www.carlprothman.net/Default.aspx?tabid" rel="nofollow" target="_blank">www.carlprothman.net/Default.aspx=87#OLEDBProviderForMicrosoftJetExcel





-

"Jeff Johnson [MVP: VB]" <i.get@enough.spam>wrote in



Add a project reference to the Microsoft Access Object Library.



Instantiate an Access Application object:



Dim accApp As Access.Application

Set accApp = New Access.Application



Then look at using:

accApp.DoCmd.TransferSpreadsheet ...

-

But this would mean every user of the application would need a licensed

version of Access.



This can be accomplished using just ADO.



Look into the SELECT INTO statement (SQL Guide at the bottom of the page):

www.veign.com/vrc_app_main.asp">www.veign.com/vrc_app_main.asp



Syntax:

SELECT COLUMN_NAME(S) INTO NEW_TABLE_NAME

[ IN EXTERNAL_DATABASE ]



FROM SOURCE_TABLE_NAME



[ WHERE CONDITION(S) ]





You will need to use the IN statement to designate your external Excel

spreadsheet.



--

Chris Hanscom - Microsoft MVP (VB)

Veign's Resource Center

www.veign.com/vrc_main.asp">www.veign.com/vrc_main.asp

--





"Tim Baur" <trbo20DISREG@ARDyahoo.com>wrote in message



-

Thanks for the link



I did a search for SELECT INTO on the Veign website but came up with no

results

What key words did you use?



Ian



"Veign" <NOSPAMinveign@veign.com>wrote in message



-

Ignore last request



I found it in the 'SQL guide at the bottom of the page' ;)

I really should read these messages thoroughly



Thanks



"Ian Davies" <iandan.dav@virgin.net>wrote in message



-

Dont think this will worK. I want to append to existing table.

This I can do

And I can open a connection to a spreadsheet using ADO

But am unsure about the inbetween bit, i.e how to get the data from the open

connection to the spreadsheet into an Append statement to execute and update

an already existing table

Ian





"Veign" <NOSPAMinveign@veign.com>wrote in message



-

Ian,



If you want to append to an existing table use the "INSERT" SQL statement.

You simply specify which fields (if not all) you want to add and what

values. You can loop tho' the Excel data creating the INSERT for each line.



HTH

Martin





"Ian Davies" <iandan.dav@virgin.net>wrote in message



-

On Mon, 25 Jul 2005 18:18:48 GMT, "Ian Davies" <iandan.dav@virgin.net>wrote:



¤ I would like to import an excel spreadsheet into my access database. I can

¤ do this from Access but now I need to do it from a vb6 application. Does

¤ anyone know how I can do this, or know of any links I can visit that gives

¤ example code?



See if the following works for you:



Sub ImportExcelToAccess()



Dim cnn As New ADODB.Connection

Dim strSQL As String



cnn.Open _

"Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=E:\My Documents\db1.mdb;" & _

"Jet OLEDB:Engine Type=4"



'Assumes Access table does not already exist

'strSQL = "SELECT * INTO [Data] FROM [Excel 8.0;DATABASE=E:\My

Documents\Test.xls;HDR=No;IMEX=1].[Sheet1$]"

'Assumes Access table exists

strSQL = "INSERT INTO [Data] (Col1, Col2, Col3, Col4) SELECT F1, F2, F3, F4 from [Excel

8.0;DATABASE=E:\My Documents\Book10.xls;HDR=No;IMEX=1].[Sheet1$];"



cnn.Execute strSQL

cnn.Close

Set cnn = Nothing



End Sub





Paul

~~~~

Microsoft MVP (Visual Basic)

-

Hi Paul

Thats the kingd of thing Im looking for

Tried it out and made a few alterations (I think you need 'VALUE' in there)



My statement is as follows



strSQL = "INSERT INTO tPupils ([PupilNo], [AccessCode], [FirstName],

[Surname], [Sex], [StartYear], [TeacherId], "

strSQL = strSQL & "[PSalutation], [PFirstName], [PSurname], [PAddress1],

[PAddress2], [PCounty], [PPostcode], [PEmail], [StatusID]) "

strSQL = strSQL & "VALUES (SELECT PupilNo, AccessCode, FirstName, Surname,

Sex, StartYear, TeacherId, "

strSQL = strSQL & "PSalutation, PFirstName, PSurname, PAddress1, PAddress2,

PCounty, PPostcode, PEmail, StatusID "

strSQL = strSQL & "FROM [Excel 8.0;DATABASE='" & ImportFile &

"';HDR=No;IMEX=1].[Sheet1$]);"



(ImportFile is a string for the excel file path)



altered from your



strSQL = "INSERT INTO [Data] (Col1, Col2, Col3, Col4) SELECT F1, F2, F3, F4

from [Excel

8.0;DATABASE=E:\My Documents\Book10.xls;HDR=No;IMEX=1].[Sheet1$];"



But getting an error message 'Syntax error. in query expression 'SELECT

PupilNo' - 2147217900

I cant see what is wrong with the syntax. Can you see something Im

overlooking?



Ian



"Paul Clement" <UseAdddressAtEndofMessage@swspectrum.com>wrote in message



-

On Tue, 26 Jul 2005 14:57:41 GMT, "Ian Davies" <iandan.dav@virgin.net>wrote:



¤ Hi Paul

¤ Thats the kingd of thing Im looking for

¤ Tried it out and made a few alterations (I think you need 'VALUE' in there)

¤

¤ My statement is as follows

¤

¤ strSQL = "INSERT INTO tPupils ([PupilNo], [AccessCode], [FirstName],

¤ [Surname], [Sex], [StartYear], [TeacherId], "

¤ strSQL = strSQL & "[PSalutation], [PFirstName], [PSurname], [PAddress1],

¤ [PAddress2], [PCounty], [PPostcode], [PEmail], [StatusID]) "

¤ strSQL = strSQL & "VALUES (SELECT PupilNo, AccessCode, FirstName, Surname,

¤ Sex, StartYear, TeacherId, "

¤ strSQL = strSQL & "PSalutation, PFirstName, PSurname, PAddress1, PAddress2,

¤ PCounty, PPostcode, PEmail, StatusID "

¤ strSQL = strSQL & "FROM [Excel 8.0;DATABASE='" & ImportFile &

¤ "';HDR=No;IMEX=1].[Sheet1$]);"

¤

¤ (ImportFile is a string for the excel file path)

¤

¤ altered from your

¤

¤ strSQL = "INSERT INTO [Data] (Col1, Col2, Col3, Col4) SELECT F1, F2, F3, F4

¤ from [Excel

¤ 8.0;DATABASE=E:\My Documents\Book10.xls;HDR=No;IMEX=1].[Sheet1$];"

¤

¤ But getting an error message 'Syntax error. in query expression 'SELECT

¤ PupilNo' - 2147217900

¤ I cant see what is wrong with the syntax. Can you see something Im

¤ overlooking?

¤



The Values keyword shouldn't be used in this instance. Did you get an error using the syntax I

provided?



In addition, if the first row has column headers then you should change the value of HDR to Yes.

Your SELECT statement uses column names (as opposed to the default F1, F2, etc.) so I am assuming

that you have column headers in your Worksheet.





Paul

~~~~

Microsoft MVP (Visual Basic)

-

Hello



<The Values keyword shouldn't be used in this instance. Did you get an error

using the syntax I

<provided?



I did get an error message with you syntax. but I found later that was due

to the variable that provided the path to the excel file. I shouldnt have

been in quotes.



<In addition, if the first row has column headers then you should change the

value of HDR to Yes.

<Your SELECT statement uses column names (as opposed to the default F1, F2,

etc.) so I am assuming

<that you have column headers in your Worksheet.



Yes it does have headers so have changed the value to YES. Out of interest

what does IMEX=1 mean?

Ive taken the VALUE keyword back out and am close to getting it to work.

However, I now get the error 'No value given for one or more required

parameters'



Ine checked the table and made sure the fields 'Required' property is set to

NO. Ive also taken out the PupilNo field from the code and table as it is an

autonumber, but the message still appears

My statement now looks like this



strSQL = "INSERT INTO tPupils ([AccessCode], [FirstName], [Surname], [Sex],

[StartYear], [TeacherId], "

strSQL = strSQL & "[PSalutation], [PFirstName], [PSurname], [PAddress1],

[PAddress2], [PCounty], [PPostcode], [PEmail], [StatusID]) "

strSQL = strSQL & "SELECT AccessCode, FirstName, Surname, Sex, StartYear,

TeacherId, "

strSQL = strSQL & "PSalutation, PFirstName, PSurname, PAddress1, PAddress2,

PCounty, PPostcode, PEmail, StatusID "

strSQL = strSQL & "FROM [Excel 8.0;DATABASE=" & ImportFile &

";HDR=Yes;IMEX=1].[tPupils$];"



Any clues?



Ian



"Paul Clement" <UseAdddressAtEndofMessage@swspectrum.com>wrote in message



-

Hello Paul



<The Values keyword shouldn't be used in this instance. Did you get an error

using the syntax I

<provided?



I put the VALUE keyword in because I was getting an error. However, I

realized that was due to the variable used for the excel file pathway. It

shouldnt have had single quotes as it isnt a string.



<In addition, if the first row has column headers then you should change the

value of HDR to Yes.

<Your SELECT statement uses column names (as opposed to the default F1, F2,

etc.) so I am assuming

<that you have column headers in your Worksheet.



It does have column header so have changed HDR to yes. Out of curiosity what

is IMEX?

Ive taken out the VALUE again and it is tantalizingly close to working

now getting the error 'No value given for one or more required parameters'

Ive checked the table and changed the field's 'required' property to NO.

Ive also removed PupilNo fron the statement and from the spreadsheet as it

is an autonumber in the access table.

But am still getting the message

Statement now look as follows



strSQL = "INSERT INTO tPupils ([AccessCode], [FirstName], [Surname], [Sex],

[StartYear], [TeacherId], "

strSQL = strSQL & "[PSalutation], [PFirstName], [PSurname], [PAddress1],

[PAddress2], [PCounty], [PPostcode], [PEmail], [StatusID]) "

strSQL = strSQL & "SELECT AccessCode, FirstName, Surname, Sex, StartYear,

TeacherId, "

strSQL = strSQL & "PSalutation, PFirstName, PSurname, PAddress1, PAddress2,

PCounty, PPostcode, PEmail, StatusID "

strSQL = strSQL & "FROM [Excel 8.0;DATABASE=" & ImportFile &

";HDR=Yes;IMEX=1].[tPupils$];"





Ian





"Paul Clement" <UseAdddressAtEndofMessage@swspectrum.com>wrote in message



-

On Tue, 26 Jul 2005 20:07:07 GMT, "Ian Davies" <iandan.dav@virgin.net>wrote:



¤ Hello

¤

¤ <The Values keyword shouldn't be used in this instance. Did you get an error

¤ using the syntax I

¤ <provided?

¤

¤ I did get an error message with you syntax. but I found later that was due

¤ to the variable that provided the path to the excel file. I shouldnt have

¤ been in quotes.

¤

¤ <In addition, if the first row has column headers then you should change the

¤ value of HDR to Yes.

¤ <Your SELECT statement uses column names (as opposed to the default F1, F2,

¤ etc.) so I am assuming

¤ <that you have column headers in your Worksheet.

¤

¤ Yes it does have headers so have changed the value to YES. Out of interest

¤ what does IMEX=1 mean?



The IMEX parameter handles mixed mode columns. If you don't use it for columns which have both alpha

and numeric values you could end up with null values on import.



¤ Ive taken the VALUE keyword back out and am close to getting it to work.

¤ However, I now get the error 'No value given for one or more required

¤ parameters'

¤

¤ Ine checked the table and made sure the fields 'Required' property is set to

¤ NO. Ive also taken out the PupilNo field from the code and table as it is an

¤ autonumber, but the message still appears

¤ My statement now looks like this

¤

¤ strSQL = "INSERT INTO tPupils ([AccessCode], [FirstName], [Surname], [Sex],

¤ [StartYear], [TeacherId], "

¤ strSQL = strSQL & "[PSalutation], [PFirstName], [PSurname], [PAddress1],

¤ [PAddress2], [PCounty], [PPostcode], [PEmail], [StatusID]) "

¤ strSQL = strSQL & "SELECT AccessCode, FirstName, Surname, Sex, StartYear,

¤ TeacherId, "

¤ strSQL = strSQL & "PSalutation, PFirstName, PSurname, PAddress1, PAddress2,

¤ PCounty, PPostcode, PEmail, StatusID "

¤ strSQL = strSQL & "FROM [Excel 8.0;DATABASE=" & ImportFile &

¤ ";HDR=Yes;IMEX=1].[tPupils$];"

¤

¤ Any clues?



The error "no value given for one or more parameters" indicates that a column name or table name is

misspelled (or not present) in one of the data sources. I can't see either of the data sources so I

can't tell you what it is.



Otherwise the syntax looks to be correct.





Paul

~~~~

Microsoft MVP (Visual Basic)

-

Found the problem :)



Excel Heading was 'E-mail' while SQL statement was 'Email'. Duhh!

I must have crosschecked the spelling 4 or 5 times and still missed it!



Thanks for your help

Ian





"Paul Clement" <UseAdddressAtEndofMessage@swspectrum.com>wrote in message



-