473,231 Members | 1,706 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,231 software developers and data experts.

VBA code help Export Excel data to Access table

I'm stuck in this code. Hope you can and will help me. I launch it
from excel.
I have made the following code work, but not as i whant. I need the
ranges to be working with something like xlDown.
eg. this only transferes the first record in the area.
..Fields("Uge").Value = ws.Range("A98").Value
Sub SelectMaster()

Dim db As Database
Dim rs1 As Recordset
Dim ws As Worksheet

Set ws = ActiveSheet
Set db = OpenDatabase("c:\data\BPO\Timereview\TimeReview.md b")

Set rs1 = db.OpenRecordset(Name:="TimeReview", Type:=dbOpenDynaset)
With rs1
..AddNew
..Fields("Uge").Value = ws.Range("A98").Value
..Fields("Manager").Value = ws.Range("B98").Value
..Fields("Medarbejder").Value = ws.Range("C98").Value
..Fields("MA-niv").Value = ws.Range("D98").Value
..Fields("Totaltimer").Value = ws.Range("E98").Value
..Fields("Overtid").Value = ws.Range("F98").Value
..Fields("DirTimer").Value = ws.Range("G98").Value
..Fields("Ferie").Value = ws.Range("H98").Value
..Fields("Helligdage").Value = ws.Range("I98").Value
..Fields("Sygdom").Value = ws.Range("J98").Value
..Fields("Barsel").Value = ws.Range("K98").Value
..Fields("Skole/intern uddannelse").Value = ws.Range("L98").Value
..Fields("Chargeability").Value = ws.Range("M98").Value
..Fields("Efficiency").Value = ws.Range("N98").Value
..Fields("Opsparet overtid").Value = ws.Range("O98").Value

..Update
End With
rs1.Close

'close database
db.Close

'Clean up
Set rs1 = Nothing
Set rs2 = Nothing
Set db = Nothing
End Sub
Nov 13 '05 #1
3 25004
Tom
One way is below. I was coming at it from Access, not Excel, so you
will need to modify a bit. Its not the most elegant, but it works. My
main problem was that the spreadsheet I was getting the data from
didn't have column names, otherwise I could have just used an insert
query instead of the klunky walk thru the recordset.

Some explaination first...

strFile = the full path and file name of the spreadsheet I was
importing
Input1 was the named area in the spreadsheet I was importing

************************************************** ************************************
Set db = CurrentDb
Set XLTable = db.CreateTableDef("Temp")
XLTable.Connect = "Excel 5.0;HDR=YES;IMEX=2;DATABASE=" & strFile
XLTable.SourceTableName = "Input1"
Input_Table was the table I wanted the data to end up in
db.TableDefs.Append XLTable

sqlstr = "SELECT * FROM Temp;"
Set rstXL = CurrentDb.OpenRecordset(sqlstr, dbOpenSnapshot)
Set rstAcc = CurrentDb.OpenRecordset("Select * from Input_Table",
dbOpenDynaset)

rstXL.MoveFirst
Do Until rstXL.EOF
rstAcc.AddNew
For n = 0 To rstXL.Fields.Count - 1
rstAcc.Fields(n) = rstXL.Fields(n)
Next n
rstAcc.Update
rstXL.MoveNext
Loop
Set rstXL = Nothing
Set rstAcc = Nothing
'Remove the attached table because it's no longer needed.
db.TableDefs.Delete "Temp"

Set db = Nothing
************************************************** *************************

Good luck

Nov 13 '05 #2
Here is one way to write data from Excel to Access. First, add this
subroutine to a Standard module in your Access mdb. Note: this
subroutine will be pulling data from an Excel file that resides in the
same directory as the mdb and from a sheet called "Sheet1" (Sheet1$ -
will need the $ dollar sign symbol appended to the sheetname). You can
change the location of the excel file and filename and Sheet name. You
can hardcode the location. But to try this example out, create an Excel
file called Book1 and plant it in the same dir as your mdb. Add some
data to Sheet1, preferrably left oriented (means starting on Column A)
and organized (meaning 10 rows will all have 10 columns of data and each
column will have the same data per row, like colA will be text, colB
int, colC dates, colE text, etc).

Sub ConnectToExcel()
Dim DB As Database, tdf As TableDef, strPath As String
Dim strTable As String, strConnect As String
Dim strSourceTable As String

Set DB = CurrentDb
strPath = Left(DB.Name, Len(DB.Name) - Len(Dir(DB.Name)))
strPath = strPath & "Book1.xls"
strConnect = "Excel 5.0;HDR=YES;IMEX=2;DATABASE=" & strPath

For Each tdf In DB.TableDefs
If tdf.Name = "Book1" Then DB.TableDefs.Delete tdf.Name
Next
Set tdf = DB.CreateTableDef("Book1")
tdf.Connect = strConnect
tdf.SourceTableName = "Sheet1$"
DB.TableDefs.Append tdf
DoCmd.RunSql "Select Into YourAccTbl Select * From Book1"
DB.TableDefs.Delete strTable

End Sub

You can invoke this routine straight from Access or from Excel. To
invoke it from Excel, use Comm Automation:

Set a reference to the Microsoft Access Object Library in
Tools/References

Then

Dim accApp As Access.Application
Set accApp = CreateObject("Access.Application")
accApp.OpenCurrentDatabase ("C:\mdb1.mdb")
accApp.Run("ConnectToExcel")
accApp.CloseCurrentDatabase
Set accApp = Nothing

The connectString

"Excel 5.0;HDR=YES;IMEX=2;DATABASE=" & strPath

works with Excel97. For Excel2k or higher might have to change Excel
5.0 to Excel 9.0, 10.0 or it may work fine the way it is.

The "ConnectToExcel" subroutine will link the Excel sheet ("Sheet1$" or
whatever you name your sheet - you have to append $ to the Sheet name)
and then use Sql to retrieve whatever is on Sheet1 and plant it into a
table of your naming. Once the data is in Access it is much easier to
manipulate. It is always easier to parse data on home ground than to
parse it remotely. So you retrieve bulk data from Sheet1 then use
Access code to parse what you need. Much easier this way. Access will
only pick up whatever you put in Sheet1. If you only want to retrieve
10 records from SheetWhatever, then only plant 10 records in that sheet.
That will be your distribution sheet.

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 13 '05 #3
Mads Petersen wrote:
I'm stuck in this code. Hope you can and will help me. I launch it
from excel.
I have made the following code work, but not as i whant. I need the
ranges to be working with something like xlDown.
eg. this only transferes the first record in the area.
.Fields("Uge").Value = ws.Range("A98").Value
Sub SelectMaster()

Dim db As Database
Dim rs1 As Recordset
Dim ws As Worksheet

Set ws = ActiveSheet
Set db = OpenDatabase("c:\data\BPO\Timereview\TimeReview.md b")

Set rs1 = db.OpenRecordset(Name:="TimeReview", Type:=dbOpenDynaset)
With rs1
.AddNew
.Fields("Uge").Value = ws.Range("A98").Value
.Fields("Manager").Value = ws.Range("B98").Value
.Fields("Medarbejder").Value = ws.Range("C98").Value
.Fields("MA-niv").Value = ws.Range("D98").Value
.Fields("Totaltimer").Value = ws.Range("E98").Value
.Fields("Overtid").Value = ws.Range("F98").Value
.Fields("DirTimer").Value = ws.Range("G98").Value
.Fields("Ferie").Value = ws.Range("H98").Value
.Fields("Helligdage").Value = ws.Range("I98").Value
.Fields("Sygdom").Value = ws.Range("J98").Value
.Fields("Barsel").Value = ws.Range("K98").Value
.Fields("Skole/intern uddannelse").Value = ws.Range("L98").Value
.Fields("Chargeability").Value = ws.Range("M98").Value
.Fields("Efficiency").Value = ws.Range("N98").Value
.Fields("Opsparet overtid").Value = ws.Range("O98").Value

.Update
End With
rs1.Close

'close database
db.Close

'Clean up
Set rs1 = Nothing
Set rs2 = Nothing
Set db = Nothing
End Sub

I haven't worked with VBA in Excell for a while.
This may help to point put you in the right direction.
Also look at Selection.SpecialCells(xlCellTypeLast Cell).Select

In my program I was taking a text file of unknown length from a scanner
application and appending it to existing data in Excel,
then generating a new graph.

This is Air Code.

Dim X as integer
Dim R as integer
R = ActiveSheet.UsedRange.Rows.Count
rs1.AddNew
for X="Some row number" to R 'or maybe R-1 , not sure
rs1.Fields("Uge").Value = ws.Cells("A",X).Value
rs1.Fields("Manager").Value = ws.Cells("B",X).Value
Next X
rs1.Update
Hope this helps.
Ron

--
Ronald W. Roberts
Roberts Communication
rw*@robcom.com

Nov 13 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Santa-D | last post by:
I've got an excel sheet that I need to import into a table, however, I want the function to automatically manipulate the data prior to it being imported. For example, i have a field called and...
1
by: rajangjr | last post by:
hi every one.... can anyone tell me the java code for converting excel data in to the database
1
by: Patonar | last post by:
Hi, I would like to be able to using VBA from an Excel macro - go through each line in my spreadsheet and add to a database. I know that this can be done via the import external data tool in...
1
by: dbalan | last post by:
I have excel file where the data is in horizontal manner. I have an output in access table, in vertical manner. I have a snap shot of data which I would like to attach which better describes my...
1
by: panku007 | last post by:
hi All, I wanted to export excel data into ms word document through java coding.So please send me reply,according to my question. thanks
1
by: san1014 | last post by:
Hi How to import and export excel data into oracle database using java. Thank you
7
by: beulajo | last post by:
Hai. I have student marks in excel sheet. I need to export this data in to msaccess. I have no problem in exporting the data. My excel sheet is in this format I have 40 students. Each student had...
12
by: anand padia | last post by:
I have a master access table where we store all the employee information. I have various application developed in excel which imports and uses information in master. Now I want to develop a excel...
3
by: prashantdixit | last post by:
Hi, I am trying to export excel data to text file in a particular format. The Format for each ROW of Excel is like IMAN_ROOT/bin/import_file -f=<Column A> -type=<Column B> -d=<Column C>...
0
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.