Automating import of Excel into Access

[smith844]

Hello All
is there a way (i guess using VBA) to import an excel spreadsheet into an access table without having to go through the wizards, if you know all the field names and other variables ??

Yours in anticipation

DJS

 

[ScottGem]

Hello All
is there a way (i guess using VBA) to import an excel spreadsheet into an access table without having to go through the wizards, if you know all the field names and other variables ??

Yours in anticipation

DJS

See TransferSpreadsheet method in Access Help

 

[wodonnell]

A variation on this question...

is it possible to bypass the wizard to import a CSV file, if you know all the parameters in advance?

 

[ScottGem]

If I understand you, you have to go thru the Import wizard to do a manual import.

 

[ghudson]

TransferSpreadsheet() or TransferText() methods will do what you want for importing via code.

 

[wodonnell]

TransferText was the one - worked a treat.

 

[clerics]

you all 're right. But please help me with this problem.

code:

Function get_seet_name(EXCEL_file As String, EXCEL_seet As String, strCOMB As String)
On Error GoTo get_seet_name_error
    
    
    Dim db          As DAO.Database
    Dim tbl         As DAO.TableDef
    Dim xlFileName As String
    Dim xlSeetName As String
    Dim nCount      As Long

    
[COLOR="seagreen"]'Set access[/COLOR]
    xlFileName = EXCEL_file
    
    Set db = OpenDatabase(xlFileName, True, True, "Excel 8.0;")
    
    nCount = 0
    
    For Each tbl In db.TableDefs
        
        If Right$(tbl.Name, 1) = "$" Or Right$(tbl.Name, 2) = "$'" Then
             If Right$(tbl.Name, 1) = "$" Then
                xlSeetName = Left$(tbl.Name, Len(tbl.Name) - 1)
            Else
                xlSeetName = Mid$(tbl.Name, 2, Len(tbl.Name) - 3)
            End If
            
[COLOR="seagreen"]            'Insert to ComboBox[/COLOR]
            'F![EXCEL_seet_name].AddItem xlSeetName
            If nCount >= 1 Then
                strCOMB = Trim(strCOMB) & ";" & Trim(xlSeetName)
            Else
                strCOMB = Trim(xlSeetName)
            End If
            
            nCount = nCount + 1         [COLOR="seagreen"]'number of sheet[/COLOR]
        End If
    Next tbl
    
    db.Close
    Set db = Nothing

    Exit Function

get_seet_name_error:

    MsgBox "you have error with this function。", 16, "error message"
    
    Exit Function
End Function

this code is in Click_event of button :

Call get_seet_name(Me.EXCEL_file_name, Me.EXCEL_seet_name, strCOMB)
Me.EXCEL_seet_name.RowSource = strCOMB
Me.EXCEL_seet_name.Visible = True

With this function i can get the name of each sheet in Excel file and insert it to Combobox.After that i use TransferSpreadsheet() to get data . But it just right with *.xls not with *.cvs . Please check it for me ! Thanks alot !

 

[Bat17]

With this function i can get the name of each sheet in Excel file and insert it to Combobox.After that i use TransferSpreadsheet() to get data . But it just right with *.xls not with *.cvs . Please check it for me ! Thanks alot !

CSV only has one sheet so you won't be able to list them all!
Peter

 

[clerics]

Oh... i did not check with Excel..( so noob with Excel) Thanks friend ... sorry all ...