Importing Excel Spread Sheets with VBA

[dpimental]

I need to be able to import spread sheets that have empty columns, which I do not want to import?

How can I do this?

Also, the sheet that I want to import is not the first one, how do I specify the correct sheet?

David Pimental
(US, Oh)
dpimental@juno.com

 

[glalsop]

Have a look at the TransferSpreadsheet method in vba help, it may be what you need.

-Gary

 

[dpimental]

It didn't help me. I need to be able to either identify empty columns, or specify the columns to transfer, or do them one at a time.

I know that when you use the wizard, it allows for you to "do not import field (skip)". I need to be able to do that when it is determined that either the column is empty or the first cell in that column is empty.

What do you think?

David Pimental
(US, Oh)
dpimental@juno.com

 

[peryan77]

Something like this...

Sub ADOFromExcelToAccess()
' exports data from the active worksheet to a table in an Access database
' this procedure must be edited before use
Dim cn As ADODB.Connection, rs As ADODB.Recordset, r As Long
' connect to the Access database
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=C:\FolderName\DataBaseName.mdb;"
' open a recordset
Set rs = New ADODB.Recordset
rs.Open "TableName", cn, adOpenKeyset, adLockOptimistic, adCmdTable
' all records in a table
r = 3 ' 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("FieldName1") = Range("A" & r).Value
.Fields("FieldName2") = Range("B" & r).Value
.Fields("FieldNameN") = Range("C" & 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
cn.Close
Set cn = Nothing
End Sub

 

[dpimental]

I need to do it all from Access. I am surprised that since the wizard has access to be able to determine what fields can get imported, that we can't access that information. Any other ideas?

I mean, I know that if a field begins with "Field" then I can delete it from the table?

David Pimental
(US, Oh)
dpimental@juno.com

 

[jebry]

Hi David!

Go into Excel and highlight what you want imported then give the range a name. You can use that name to import only what you want.

hth


Jeff Bridgham
bridgham@purdue.edu