VB Code to Import data from Excel to Access

[nshah1013]

I am new to VB. I am trying to get a sample code to import data from excel spreadsheet to access table. I can do it manually but i want to automate the process as it needs to be done every now and then. If any one can help me in this it will be greatly appriciated. Also let me know if it is posible to set parameter for selecting the file to import.

 

[Pat Hartman]

You can use the TransferSpreadsheet Method to automate the process. You can replace the file name argument with a reference to a form field where the user can enter the entire path to the spreadsheet he wants to import or you can use the common dialog API to bring up the dialog that lets him browse to the file he wants to import.

 

[nshah1013]

Thank you for your reply. but as i Said I m not too familier with writing VB code. Is it posible for you to provide me with sample code. I trying using the DoCmd.TransferSpreadsheet, and i can get the file imported but I dont know how to put in the Dialog box to browes the path.

Please help me
Thanks

 

[Jibbadiah]

-- replace this "c:\temp\filename.xls" with your excel filename.

Private Sub ImportXLSheets()

Dim WrksheetName As String
Dim i As Integer
Dim xl As Object
Set xl = CreateObject("Excel.Application")

xl.Visible = True
xl.Workbooks.Open "c:\temp\filename.xls"

With xl
.Visible = True
With .Workbooks(.Workbooks.Count)
For i = 1 To .Worksheets.Count
WrksheetName = .Worksheets(i).NAME
DoCmd.TransferSpreadsheet (acImport), acSpreadsheetTypeExcel97, WrksheetName, "c:\temp\filename.xls"
Next i
End With

End With
Set xl = Nothing

End Sub

 

[nshah1013]

Thanks james

This code worked just fine. You really helped me out here. Thanks again.

 

[vijugopal]

-- replace this "c:\temp\filename.xls" with your excel filename.

Private Sub ImportXLSheets()

Dim WrksheetName As String
Dim i As Integer
Dim xl As Object
Set xl = CreateObject("Excel.Application")

xl.Visible = True
xl.Workbooks.Open "c:\temp\filename.xls"

With xl
.Visible = True
With .Workbooks(.Workbooks.Count)
For i = 1 To .Worksheets.Count
WrksheetName = .Worksheets(i).NAME
DoCmd.TransferSpreadsheet (acImport), acSpreadsheetTypeExcel97, WrksheetName, "c:\temp\filename.xls"
Next i
End With

End With
Set xl = Nothing

End Sub

The above code works fine for exporting data from an excel file to access. Is it fesible to export to an already existing access file ? If so where to mentione the destination file/table name ? Any experienced one please replay

 

[Jibbadiah]

Hi vijugopal,

The code below is run from an existing Access database (not from Excel).
It opens an excel workbook, cycles through every worksheet and imports the worksheet data to it's own table (named the same as the worksheet). If you just want to import a single worksheet then just use the transferspreadsheet command. Look at the term "transferspreadsheet" in Access help and it will tell you what the arguments are in more detail.

DoCmd.TransferSpreadsheet [transfertype][, spreadsheettype], tablename, filename[, hasfieldnames][, range]

Regards,

James

 

[vijugopal]

Dear Mr. James,
Thank you very much. I will try this.
Viju Goapl