exporting to excel

[Rowan Drummond]

Hi Justin

I have tried to get this working but my solution is not fool proof. The
problem is to do with the way Access treats fields that are null or
contain the text "" ie look blank but are not null. If you can't get
this to work I suggest try one of 3 options:
1) Look into having your users update the Access database directly using
an Access Userform.
2) Repost your question giving as much detail as possible and maybe the
code below. I know there are other people on this newsgroup that are a
lot more skilled at Access than myself.
3) Get some good books <g>

This is where I got to:

'--Declarations-------------------------------------------
Option Explicit
Private Const m_cDBLocation As String _
= "C:\Temp\db1.mdb" '<<Change Database Path and Name

'----Code Start--------------------------------------------
Sub loadData()

Dim MyCn As ADODB.Connection
Dim SQLStr As String
Dim rs As ADODB.Recordset
Dim i As Long
Dim r As Long
Dim delRows As Range
Dim TableName As String
Dim ID As Long
Dim CDICmt As String
Dim CoOrdId As String
Dim CoOrdCmt As String

TableName = "CDI Import_Detail"

Set MyCn = New ADODB.Connection

MyCn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; " & _
"DBQ=" & m_cDBLocation

i = Cells(Rows.Count, 1).End(xlUp).Row

For r = 12 To i '<<change start row

ID = Range("A" & r).Value
CDICmt = Range("M" & r).Value
CoOrdId = Range("L" & r).Value
CoOrdCmt = Replace(Range("N" & r).Value, "'", "")
CoOrdCmt = Replace(CoOrdCmt, """", "")

If CDICmt <> "" Then 'only process row if dropdown populated

'check to see if anything changed
Set rs = RunQuery(TableName, ID, CDICmt, CoOrdId, CoOrdCmt, True)


If rs.RecordCount > 0 Then
'if id in table then prompt to update
If MsgBox("Do you want to update record " & _
ID, vbYesNo) = vbYes Then

'Create update query
SQLStr = "UPDATE [" & TableName & "]" _
& " Set [Coordinator ID] = '" & CoOrdId & "', " _
& "[CDI Comments] = '" & CDICmt & "', " _
& "[Coordinator Comments] = '" & CoOrdCmt & "', " _
& "[Date file Uploaded] = '" & Now() & "'" _
& " Where [CDI ID] = " & ID

MyCn.Execute SQLStr

If CDICmt = "Logistic Asset Validated" Then
If delRows Is Nothing Then
Set delRows = Range("A" & r)
Else
Set delRows = Union(delRows, Range("A" & r))
End If
End If

End If
End If

End If

Next r

If Not delRows Is Nothing Then delRows.EntireRow.Delete


MsgBox "Data has been uploaded to CDI ERROR DATA"
Set rs = Nothing
MyCn.Close
Set MyCn = Nothing

End Sub

Public Function RunQuery(ByVal TableName As String, ByVal CDIID As _
Long, ByVal CDICmt As String, ByVal CoOrdId As String, ByVal CoOrdCmt _
As String, ByVal blnConnected As Boolean) As ADODB.Recordset

'this function is meant to check if anything has changed
'problem is if you change one field but not others it
'appears that the update query above changes the Null fields
'not changed to ""
'The next time you run the function without making any further
'changes you are prompted to update that record again.

'One possible solution: Change the SQLstr update query to
'only update fields where the value of the variale is not ""
'but this would mean the user could never remove a comment

Dim strConnection As String

strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & m_cDBLocation & ";"

Set RunQuery = New ADODB.Recordset
With RunQuery
.CursorLocation = adUseClient
.CursorType = adOpenStatic
.LockType = adLockBatchOptimistic
End With

If CoOrdId = "" And CoOrdCmt = "" Then
RunQuery.Open "Select * from [" & TableName & _
"] Where ([Coordinator ID] is Not Null" & _
" or [CDI Comments] <> '" & CDICmt & _
"' or [CDI Comments] is Null or [Coordinator Comments]" _
& " is Not Null)" _
& " and [CDI ID] = " & CDIID & "", strConnection, , , _
adCmdText
ElseIf CoOrdId = "" And CoOrdCmt <> "" Then
RunQuery.Open "Select * from [" & TableName & _
"] Where ([Coordinator ID] is Not Null" & _
" or [CDI Comments] <> '" & CDICmt & _
"' or [CDI Comments] is Null or [Coordinator Comments]" _
& " <> '" _
& CoOrdCmt & "' or [Coordinator Comments] is Null)" & _
" and [CDI ID] = " & CDIID & "", strConnection, _
, , adCmdText
ElseIf CoOrdId <> "" And CoOrdCmt = "" Then
RunQuery.Open "Select * from [" & TableName & _
"] Where ([Coordinator ID] <> '" & CoOrdId & _
"' or [Coordinator ID] is Null or [CDI Comments]" _
& " <> '" & CDICmt & _
"' or [CDI Comments] is Null or [Coordinator Comments]" _
& " is Not Null)" _
& " and [CDI ID] = " & CDIID & "", strConnection, , , _
adCmdText
ElseIf CoOrdId <> "" And CoOrdCmt <> "" Then
RunQuery.Open "Select * from [" & TableName & _
"] Where ([Coordinator ID] <> '" & CoOrdId & _
"' or [Coordinator ID] is Null or [CDI Comments]" _
& " <> '" & CDICmt & _
"' or [CDI Comments] is Null or [Coordinator Comments]" _
& " <> '" _
& CoOrdCmt & "' or [Coordinator Comments] is Null)" & _
" and [CDI ID] = " & CDIID & "", strConnection, _
, , adCmdText
End If

If blnConnected = False Then Set RunQuery.ActiveConnection = _
Nothing
End Function

'-----End Code------------------------------------------------

Regards
Rowan

 

[PayPaul]

In Internet Explorer there is a menu that comes up when you right click on a
webpage which allows you to 'export to Excel". I've done this but the data,
which comes from flickr is only organised into rows.
I'm trying to extract the "views" numbers to add them up.
What would be the best way to accomplish this? Are there settings in IE that
change how data is exported or is there a function in Excel that will allow
me to add up numbers adjacent to text in a cell given the condition that the
text is something like "views"?