S
Syvman
Hi all... I received some really good help on a similar topic here a
few weeks back, so I thought I might pose another question to anyone
who might know the answer:
I am trying to dump some data from an excel spreadsheet into an access
table. I am doing this by looping through each excel spreadhsheet's
worksheet name and brining the raw data out of each one using an append
query SQL statement (the INSERT INTO line).
The following code works fine, except the DoCmd.RunSQL statement at the
end. For some reason, this subprocedure NEVER empties out the nulls
that are in the table after transferring all the data from the
spreadsheet into the access table. I created a query with the EXACT
same SQL statement and ran it after the fact, and it empties all the
nulls just fine. What am I doing wrong? Does it have something to do
with the ADODB connection? It appears that the delete query isn't
running because the INSERT INTO isn't finished yet or something.
Anyone's input would be greatly appreciated! Thanks!
-Terry
Public Sub ListWorksheets(ByRef strFullName As String)
Dim cnn As ADODB.Connection
Dim cat As ADOX.Catalog
Dim tbl As ADOX.Table
Dim strSQL As String
Dim wkshtname As String
Set cnn = New ADODB.Connection
With cnn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source=" & strFullName & ";" & _
"Extended Properties=""Excel 8.0;HDR=No;"""
.Open
End With
Set cat = New ADOX.Catalog
Set cat.ActiveConnection = cnn
DoCmd.RunSQL ("DELETE * FROM Table1") 'Empty table for use
For Each tbl In cat.Tables
' Worksheet names always end in the "$" character.
If Right(tbl.Name, 1) = "$" Then
wkshtname = tbl.Name
ElseIf Right(tbl.Name, 2) = "$'" Then
wkshtname = Mid(tbl.Name, 2, Len(tbl.Name) - 2)
Else
wkshtname = "INVALID"
End If
If wkshtname <> "INVALID" Then
strSQL = "INSERT INTO Table1 (F1) IN
'c:\test\worksheetnames.mdb' SELECT * FROM [" & wkshtname & "B4:B]"
Debug.Print strSQL
cnn.Execute strSQL
Else
End If
Next tbl
DoCmd.RunSQL "DELETE Table1.*, Table1.F1 FROM Table1 WHERE
(((Table1.F1) Is Null));"
cnn.Close
Set cat = Nothing
Set cnn = Nothing
End Sub
few weeks back, so I thought I might pose another question to anyone
who might know the answer:
I am trying to dump some data from an excel spreadsheet into an access
table. I am doing this by looping through each excel spreadhsheet's
worksheet name and brining the raw data out of each one using an append
query SQL statement (the INSERT INTO line).
The following code works fine, except the DoCmd.RunSQL statement at the
end. For some reason, this subprocedure NEVER empties out the nulls
that are in the table after transferring all the data from the
spreadsheet into the access table. I created a query with the EXACT
same SQL statement and ran it after the fact, and it empties all the
nulls just fine. What am I doing wrong? Does it have something to do
with the ADODB connection? It appears that the delete query isn't
running because the INSERT INTO isn't finished yet or something.
Anyone's input would be greatly appreciated! Thanks!
-Terry
Public Sub ListWorksheets(ByRef strFullName As String)
Dim cnn As ADODB.Connection
Dim cat As ADOX.Catalog
Dim tbl As ADOX.Table
Dim strSQL As String
Dim wkshtname As String
Set cnn = New ADODB.Connection
With cnn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source=" & strFullName & ";" & _
"Extended Properties=""Excel 8.0;HDR=No;"""
.Open
End With
Set cat = New ADOX.Catalog
Set cat.ActiveConnection = cnn
DoCmd.RunSQL ("DELETE * FROM Table1") 'Empty table for use
For Each tbl In cat.Tables
' Worksheet names always end in the "$" character.
If Right(tbl.Name, 1) = "$" Then
wkshtname = tbl.Name
ElseIf Right(tbl.Name, 2) = "$'" Then
wkshtname = Mid(tbl.Name, 2, Len(tbl.Name) - 2)
Else
wkshtname = "INVALID"
End If
If wkshtname <> "INVALID" Then
strSQL = "INSERT INTO Table1 (F1) IN
'c:\test\worksheetnames.mdb' SELECT * FROM [" & wkshtname & "B4:B]"
Debug.Print strSQL
cnn.Execute strSQL
Else
End If
Next tbl
DoCmd.RunSQL "DELETE Table1.*, Table1.F1 FROM Table1 WHERE
(((Table1.F1) Is Null));"
cnn.Close
Set cat = Nothing
Set cnn = Nothing
End Sub