Hi, and thanks for previous help.
I use following code to export from excel to access. It is executed in
excel.
I have an excel spreadsheet with one sheet pr. week. this code is
therefore executed on every sheet, and is supposed to be it on comming
sheets as well. Therefore I would very much like if I could check if
the value in A98 (excel) matches a record in the access table
TimeReview, field "Uge". And if it does the export should be cancled.
Hope you can help.
Regards Mads
************************************************** *******'
Sub DAOFromExcelToAccess()
' exports data from the active worksheet to a table in an Access
database
Dim db As Database, rs As Recordset, r As Long
Set db = OpenDatabase("c:\data\BPO\Timereview\TimeReview.md b")
' open the database
Set rs = db.OpenRecordset("TimeReview", dbOpenTable)
' get all records in a table
r = 98 ' the start row in the worksheet
Do While Len(Range("A" & r).Formula) > 0
' repeat until first empty cell in column A
With rs
.AddNew ' create a new record
' add values to each field in the record
.Fields("Uge") = Range("A" & r).Value
.Fields("Manager") = Range("B" & r).Value
.Fields("Medarbejder") = Range("C" & r).Value
.Fields("MA-niv") = Range("D" & r).Value
.Fields("Totaltimer") = Range("E" & r).Value
.Fields("Overtid") = Range("F" & r).Value
.Fields("DirTimer") = Range("G" & r).Value
.Fields("Ferie") = Range("H" & r).Value
.Fields("Helligdage") = Range("I" & r).Value
.Fields("Sygdom") = Range("J" & r).Value
.Fields("Barsel") = Range("K" & r).Value
.Fields("Skole/intern uddannelse") = Range("L" & r).Value
.Fields("Chargeability") = Range("M" & r).Value
.Fields("Efficiency") = Range("N" & r).Value
.Fields("Opsparet overtid") = Range("O" & r).Value
' add more fields if necessary...
.Update ' stores the new record
End With
r = r + 1 ' next row
Loop
rs.Close
Set rs = Nothing
db.Close
Set db = Nothing
End Sub 5 7208
Hi Mads
You could just use another recordset to check for existing records something
like
Dim db As Database, rs As Recordset, rsCheck As Recordset, strSQL As String
r As Long
Set db = OpenDatabase("c:\data\BPO\Timereview\TimeReview.md b")
' open the database
'check for existing records
strSQL = "SELECT * FROM TimeReview WHERE Uge = " & Range("A98")
Set rsCheck = db.OpenRecordset(strSQL)
If Not rsCheck.BOF And Not rsCheck.EOF Then
MsgBox("Records already present")
rsCheck.Close
Set rsCheck = Nothing
Exit Sub
End If
rsCheck.Close
Set rsCheck = Nothing
Set rs = db.OpenRecordset("TimeReview", dbOpenTable)
' get all records in a table
r = 98 ' the start row in the worksheet
Do While Len(Range("A" & r).Formula) > 0
etc................
Cheers,
Peter
Hi Peter
Thanks a lot it looks like what i need. But I recieve a complie error on
the second row of your code.
r As Long
Why is that. I am pretty new to VBA. SO sorry for that.
Hope you know why.
Cheers
, Newbie Mads
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
"Peter Hoyle" <pe*********@ntlworld-NOT.com> wrote in message news:<q8************@newsfe1-gui.ntli.net>... Hi Mads
You could just use another recordset to check for existing records something like
Dim db As Database, rs As Recordset, rsCheck As Recordset, strSQL As String r As Long Set db = OpenDatabase("c:\data\BPO\Timereview\TimeReview.md b") ' open the database
'check for existing records strSQL = "SELECT * FROM TimeReview WHERE Uge = " & Range("A98") Set rsCheck = db.OpenRecordset(strSQL) If Not rsCheck.BOF And Not rsCheck.EOF Then MsgBox("Records already present") rsCheck.Close Set rsCheck = Nothing Exit Sub End If
rsCheck.Close Set rsCheck = Nothing
Set rs = db.OpenRecordset("TimeReview", dbOpenTable) ' get all records in a table r = 98 ' the start row in the worksheet Do While Len(Range("A" & r).Formula) > 0 etc................
Cheers, Peter
Hi Peter
Thanks a lot it looks like what i need. But I recieve a complie error on
the second row of your code.
r As Long
Why is that. I am pretty new to VBA. SO sorry for that.
Hope you know why.
Cheers
, Newbie Mads
Hi Mads.
Hopefully you've sorted this one now.
If not then the first line should have been...
Dim db As Database, rs As Recordset, rsCheck As Recordset, strSQL As String,
r As Long
This should be all on one line but gets split by the news reader.
Dim db As Database
Dim rs As Recordset
Dim rsCheck As Recordset
Dim strSQL As String
Dim r As Long
would be the equivalent,
Cheers,
Peter
Hi again.
Ya the newbie got that figured out...(but thanks any way)
But I have someproblems with this part:
strSQL = "SELECT * FROM TimeReview WHERE Uge = " & Range("A98")
Set rsCheck = db.OpenRecordset(strSQL)
there is a datatype mismatch. Could it be beacause of the fact that my
weeknumbers isn't numbers but text in both excel and access. And if
what to do?
Hope some helo is available.
Cheers Mads
"Peter Hoyle" <pe*********@ntlworld-NOT.com> wrote in message news:<ZA*************@newsfe3-gui.ntli.net>... Hi Mads.
Hopefully you've sorted this one now.
If not then the first line should have been...
Dim db As Database, rs As Recordset, rsCheck As Recordset, strSQL As String, r As Long
This should be all on one line but gets split by the news reader.
Dim db As Database Dim rs As Recordset Dim rsCheck As Recordset Dim strSQL As String Dim r As Long
would be the equivalent,
Cheers, Peter This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: TadPole |
last post by:
Hi all,
My main problems are:::::::::
1. Set a value within a block container that can be used and changed by
subsequent templates/block-containers/tables etc..
2. get/determine/find the...
|
by: Frank K. |
last post by:
Hello,
Hoping someone can help me out.
When I select all records in a table, copy them to the clip board, and paste
them to Excel the names of the fields appear in the first row in Excel....
|
by: Chris Millar |
last post by:
I have a user control that i wish to extend to change the date when the user
selects the numeric up down button.
The code explains itself, hope someone can help.
any ideas appreaciated..
...
|
by: bienwell |
last post by:
Hi all,
Data displayed on the datalist control is bound by the column name of the
dataset like this :
<%# DataBinder.Eval(Container.DataItem, "title")%>
Could I use an element of the array...
|
by: qiong |
last post by:
Dear friends,
Good evening,
I have some problems with finding keyword ":\" for each cell. My objective is to find that keyword in each cell in each worksheet which represent the link. Eg:...
|
by: aotemp |
last post by:
Hi,
Im having a reaaally hard time with something... Im trying to read a
cell of data into a String variable. It seems like such a simple task
too... Get the excel spreadsheet, get the...
|
by: brenty66 |
last post by:
I have a userform that is used as an interface for information stored on excel spreadsheets. The user enters bits of information into fields on the userform which are place on the corresponding...
|
by: suresh_punniyakkodi |
last post by:
Hellow Friends,
I have one doubt, please help me...
In Excel, i have lot of rows and coloumns, i need to read all cell values with in rows and coloumn limit...
At the time...
|
by: Ronald Raygun |
last post by:
I have a form on an HTML page with the usual user name, email etc. I
want to be able to display a popup window (callout?) when a text input
control element is clicked.
For example, for the form:...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
| |