Copying Data from Excel to Access

[JaneB19]

Morning everybody!

I'm having problems with TransferSpreadsheet action in a macro that I'm trying to create. All the arguments are fine apart from Range!

I am trying to get data from a spreadsheet and want to do it automatically. The spreadsheet is in a report/form formate so the data cells are spread around the sheet, which is causing the problem. Does anybody know how to upload several ranges? Plus it doesn't like the table fields, I have tried not naming a table so that Access creates its own table, but that doesn't work either! :-(

I also have the following error appear whenever I try to run the macro:

The contents of fields in the 0 record(s) were deleted, and 0 record(s) were lost due to key violations. etc...

Can anybody help me with any of the problems? Or, suggest any other ways of getting data from Excel into Access?

Thanks

[PC2]

 

[hermanlaksko]

I create a tbl on the fly and ensure that I have enough fields in that tbl to handle incomming columns from excel.

I have a bit of code for you here:
SQLStr = "Create Table Import ( F1 NvarChar(250)collate SQL_Latin1_General_CP1_CI_AS, F2 NvarChar(250)collate SQL_Latin1_General_CP1_CI_AS, F3 NvarChar(250)collate SQL_Latin1_General_CP1_CI_AS, F4 NvarChar(250)collate SQL_Latin1_General_CP1_CI_AS,F5 NvarChar(250)collate SQL_Latin1_General_CP1_CI_AS," _
& " F6 NvarChar(250)collate SQL_Latin1_General_CP1_CI_AS, F7 NvarChar(250)collate SQL_Latin1_General_CP1_CI_AS, F8 NvarChar(250)collate SQL_Latin1_General_CP1_CI_AS, F9 NvarChar(250)collate SQL_Latin1_General_CP1_CI_AS, F10 NvarChar(250)collate SQL_Latin1_General_CP1_CI_AS, F11 NvarChar(250)collate SQL_Latin1_General_CP1_CI_AS, F12 NvarChar(250)collate SQL_Latin1_General_CP1_CI_AS,F13 NvarChar(250)collate SQL_Latin1_General_CP1_CI_AS)"

DoCmd.RunSQL SQLStr

I use this to create a tbl on an SQL server and as I create var. tbls I have put the above into a part of a select case statement. Dont forget to delete the tbl first

Herman

 

[JaneB19]

Hi Herman,

There are a few questions I need to ask!

I'm not using an SQL Server just Access 2000 and Excel 2000 professional. Will you above coding need any major ajustment? It's just that I'm a bit confused about what each bit does. Sorry!

I notice that you've got F1, F2, F3 etc in your coding? Are these specific cell references? i.e. F1 instead of B8 or equivilant? Also I'm not too sure whether I'd need all of the "SQL_Latin1_General_CP1_CI_AS"? Is this needed or is there a default value which I could use instead?

Thank you for your help, it's very much appreciated!


[PC2]

 

[hermanlaksko]

The F1 etc. stands for Field1.

As I was not shure what you ware developing I let you have the lot , but no you should only use Create Table Import ( F1 String(250, F2 String(250), etc)
Herman

 

[philcon]

Hi Jane,

Would it make your life easier if you were to insert a worksheet in the spreadsheet which drew all your output data together, and then Name that range in excel, prior to impoting into access?

Just a thought


Phil.

 

[JaneB19]

I think it might clear up a few problems if I explained exactly what I'm doing rather than just telling you what the problem is!

There is a form which is to use to submit data to a client (they have their own database which it is entered into, this is done manually into another spreadsheet!). My company wish to gather the data also but in their own version. (I hope this is making sense)

The data is being gathered from the spreadsheet/form which will have been saved on our network incase we have to refer to it but my company would prefer if we could collate the data. (I think I'm repeating myself)

I would like to gather the data by accessing the Excel form of the data through Access, i.e. searching for the new form and then uploading its data into the database. This is the point where the upload problem comes in!

I hope this helps you both to help me? Never mind if it doesn't

Thanks again
Jane

P.S. Herman - Thanks for clearing that up about what to exclude from the coding!

[PC2]

 

[JaneB19]

I think it might clear up a few problems if I explained exactly what I'm doing rather than just telling you what the problem is!

There is a form which is to use to submit data to a client (they have their own database which it is entered into, this is done manually into another spreadsheet!). My company wish to gather the data also but in their own version. (I hope this is making sense)

The data is being gathered from the spreadsheet/form which will have been saved on our network incase we have to refer to it but my company would prefer if we could collate the data. (I think I'm repeating myself)

I would like to gather the data by accessing the Excel form of the data through Access, i.e. searching for the new form and then uploading its data into the database. This is the point where the upload problem comes in!

I hope this helps you both to help me? Never mind if it doesn't

Thanks again
Jane

P.S. Herman - Thanks for clearing that up about what to exclude from the coding!

[PC2]

 

[hermanlaksko]

Let me try and see if I can get you on the right track.

The below will create an empty tbl for your import:

Function CreateTbl(TblName As String)
On Error Resume Next
DoCmd.RunSQL "Detele Table " & TblName

Select Case TblNavn
Case "WW_Import"
sqlstr = "Create Table WW_Import ( F1 String(250), F2 String(250), F3 String(250)," _
& "F4 String(250),F5 String(250), F6 String(250), F7 String(250), F8 String(250))"

Case "SomeOtherTbl"
sqlstr = "Create Table SomeOtherTbl( F1 String(250), F2 String(250), F3 String(250)," _
& "F4 String(250),F5 String(250), F6 String(250), F7 String(250), F8 String(250))"
End Select
DoCmd.RunSQL sqlstr
End Function

The rest is just import into the created table and transfering that data to your var. tbl(s) and delete the created tbl as this should only be used as a temp tbl.

Herman

 

[ProjectExplorer]

You could import the data into variables using the following code and then use DAO or ADODB code to apply the variable values to your access database. This method is better if the data is spread arround in Excel.
You are actually specifying which cells the data is in.

Dim xlobj As Object, strExcelFile As String
strExcelFile = "c:\Folder Name\Spreadsheet.xls"
Set xlobj = CreateObject("excel.application&quot
With xlobj
.Workbooks.Open Filename:=strExcelFile
intNumber = .Worksheets("Work Sheet Name&quot.Cells(1, 1).Value
End With
xlobj.Application.Quit
Set xlobj = Nothing

 

[JaneB19]

Hi Thames

Is it ok to ask you a couple of questions about the above coding? These might sound stupid, but I've never done any serious VBA coding before so it's all a bit baffeling!

How does it work? I'm just checking that what I think each line does is actually what it does.

Declare variables. Sets the ExcelFile name to the name of the spreadsheet I wish to get the data from. Sets xlobj to a new object, which is an Excel application. (running out of steam here, not too sure but...) I'm sorry but I don't know where to start with the "With" bit of the coding. Quit the Excel Application. Reset xlobj.

I'm going to assume that "Filename" has to be changed. That's the name of the Excel file? Also, the Cells( , ).Value The Cells, I don't suppose you can write B8, or does it have to be the number of columns across by the number of rows down, is that the correct order or is it rows by columns.

Opps, I've gone over my "couple of questions"! I'm sorry to ask you so many but I it's just so that I know what I'm changing and so I understand what I'm doing rather than stabbing around in the dark!

Thanks
Jane

[PC2]

 

[hermanlaksko]

Looks like they are discussing something like this right next door try this thread thread181-611208
Also you could try this url

http://www.wpower.fsbusiness.co.uk/

From then same thread

Herman

 

[ProjectExplorer]

Hope this helps. I have amended it slightly to show how to extract more than one data item.

Dim xlobj As Object, strExcelFile As String
Dim strDataItems(2) As String

This declares the variable xlobj as an Object and the variable strExcelFile as a String. StrDataItem(2) is a variable Array to hold the incoming data items.

strExcelFile = "c:\Folder Name\Spreadsheet.xls"

This is the path to your Excel spreadsheet (you need to change the path as appropriate)

Set xlobj = CreateObject("excel.application&quot

This creates an instance of Excel

With xlobj
.Workbooks.Open Filename:=strExcelFile
strDataItem(2) = .Worksheets("Work Sheet Name&quot.Cells(1, 1).Value
strDataItem(1) = .Worksheets(Work Sheet Name”).Cells(2,2).Value
End With

This opens the Excel file and extracts the data item from cells A:1 and B:2 in the work sheet called “Work Sheet Name” (you need to change this to the name of your worksheet).

xlobj.Application.Quit
Set xlobj = Nothing

This closes the excel application and removes the object from memory.

Once you have captured the data in variables you then use the DAO or ADODB .add method to apply the data to your database. Let me know if you cannot find this code.

Regards

 

[JaneB19]

Hiya

The only way I know how to set up an ADODB connection is from an ASP. It is the same or similar to that coding?



[PC2]

 

[ian0001]

RE: ADOBC or DAO...ing what about ....

Once you have all your cells loaded into the strDataItems(x) array. Why not just open a recordset of the appropriate table/s and just edit or addnew the variables in. The code to do this is exampled in the Access F1 (help).

Also once I was importing from excel into Access97 using similar code & I just couldn't load columns A,B or C. All other columns worked OK with the same code. Well this may help :/

 

[ProjectExplorer]

Jane

If you are using Access 97 then you should use DAO if you are using Access 2000 you can use either DAO or ADODB.

 

[ProjectExplorer]

Jane

The DAO code would be along the following lines:

Dim Db As DAO.Database
Dim rs As DAO.Recordset

Set Db = CurrentDb
Set rs = Db.OpenRecordset("tblName&quot

With rs
.AddNew
rs("FieldNameOne&quot = strDataItem(1)
rs("FieldNameTwo&quot = strDataItem(2)
.Update
End With

Set Db = Nothing
Set rs = Nothing

TblName is the name of your table.
“FieldNameOne / Two” are the names of the two fields that you are writing data to.

Regards