Excel to Access

[bpeirson]

I can import data from Access to Excel then calculate table entries but I can't export back to Access using VBA.

After I start an instance of Access from Excel's VBA I don't know which methods to use to "Get External Data" and add it to the original table I found it in. I would like all the code to be started from a click of a button on an Excel spreadsheet.

Can anyone point me in the right direction. The best case would be to update the fields which have changed. At the very least I would like to be able to overwrite the original table with the transformed table.

I am using Excel because it is a program that others in my office are comfortable using.

Thanks.

 

[PHV]

In the access VBA help, have a look at the DoCmd.TransferSpreadsheet method.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[bpeirson]

Thanks, everything starts out well enough now but I get an error while calling appAccess.DoCmd.TransferSpreadsheet.

The error is

Run-time error '2391':
Field 'F1' doesn't exist in destination table 'TimeTicket.'

The F1 will change to F9 if I include headings in the table. The MSDN site is not very forthcoming with information unless you are a skilled programmer. Any help is appreciated.

Here is my code in case the error is caused by something else.

Sub AddToDataBase()

''Copy the modified table to a separate, temporary workbook.
    Sheets("TimeTicket").Select
    Sheets("TimeTicket").Copy
    Rows("1:1").Select
    Selection.Delete Shift:=xlUp
    ActiveWorkbook.SaveAs Filename:= _
        "\\terrick-server\Administration\Work Orders\Information\TimeTicket.xls", _
        FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
        ReadOnlyRecommended:=False, CreateBackup:=False
''Start Access and transfer data
Dim strDB As String
strDB = "T:\Program Files\E2\Blswin32\Dat\WEEKLYBACKUP\old15BLSDATA.MDB"
Set appAccess = CreateObject("Access.Application")
appAccess.OpenCurrentDatabase strDB
appAccess.Visible = True

appAccess.DoCmd.TransferSpreadsheet acImport, , "TimeTicket", _
    "\\terrick-server\Administration\Work Orders\Information\TimeTicket.xls", _
    False

appAccess.CloseCurrentDatabase
appAccess.Quit
Set appAccess = Nothing

'' remove temporary spreadsheeet TimeTickets.xls
    Dim oFso As Object
    Set oFso = CreateObject("Scripting.FileSystemObject")
    Call oFso.DeleteFile("\\terrick-server\Administration\Work Orders\Information\TimeTicket.xls", True)
End Sub

 

[Remou]

You can do this with ADO, too. Here is an example.

    Set cn = CreateObject("ADODB.Connection")
    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
            "Data Source=" & ActiveWorkbook.FullName & ";" & _
            "Extended Properties=""Excel 8.0;HDR=YES;"""

    'Create a table in an mdb from sheet1
    strSQL = "SELECT * INTO NewTable IN 'C:\Docs\LTD.mdb' FROM [Sheet1$]"

    cn.Execute strSQL, lngRecsAff, adExecuteNoRecords

http://lessthandot.com

 

[bpeirson]

Thanks for the alternate method.

I found my problem, there was phantom data in the columns to the right of the true data. When I did a manual import from Access I saw that Access was trying to import 45 columns when only 8 columns exist in the table.

Brad

 

[bpeirson]

Remou, will the ADO code you posted create a new table or add data to an existing table?

I have realized that not all the computers running my code will have Access installed so I need to use a method available to all computers.

I have no experience using ADO or Jet. A push in the right direction will be appreciated.

I looked on the "lessthandot" site but I am not sure what I am looking for.

Brad

 

[Remou]

The code I posted will create a new table.

LessThanDot is mainly my signature, there is a thin blue line that separates signatures from posts However, you may find some useful stuff in the wiki.

http://lessthandot.com

 

[xlbo]

To change the ADO code to update the table simply change the SQL command to an INSERT INTO or UPDATE command...

You basically....

Set up a connection to the database:
Set cn = CreateObject("ADODB.Connection")
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & ActiveWorkbook.FullName & ";" & _
"Extended Properties=""Excel 8.0;HDR=YES;"""

Identify the SQL string you wish to execute:
strSQL = "SELECT * INTO NewTable IN 'C:\Docs\LTD.mdb' FROM [Sheet1$]"

Execute the SQL string against the connection:
cn.Execute strSQL, lngRecsAff, adExecuteNoRecords

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question