import date from Excel to Access?

[yn19832]

Hi, I want to transfer an excel spreadsheet to Access, which contains several sheets. Could anyone tell me how to refer to each sheet when using VBA?
Many thanks.

 

[boblarson]

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "YourTableYouWantTheDataImportedTo", "C:\YourFileName.xls", True, "Sheets!YourSheetNameHere"

If using below 2000, change the parts to fit your Excel version and the in the other parts plug in your own names.

 

[yn19832]

Many thanks.

But I am afraid it is not that simple. I can not directly import the excel data, as I need to dispaly data with different forms in Access. I have almost done all the codes with VBA, the problem now is I want to creat a loop to refresh the data in Access from different sheets. I just wonder how to go from one sheet to another.
Could you help me sort it out?

 

[boblarson]

Many thanks.

But I am afraid it is not that simple. I can not directly import the excel data, as I need to dispaly data with different forms in Access. I have almost done all the codes with VBA, the problem now is I want to creat a loop to refresh the data in Access from different sheets. I just wonder how to go from one sheet to another.
Could you help me sort it out?

If you had asked that question to begin with (and posted the code you were using), we could give you more relevant information. I gave you AN answer to the question you asked which was "Could anyone tell me how to refer to each sheet when using VBA" and DoCmd.TransferSpreadsheet IS using VBA, but just not the VBA you're looking for. So, if you would be so kind as to post the code that you are using and want to loop between the sheets, we might be able to give you an answer that fits what you're actually looking for.

 

[yn19832]

many thanks.
My codes are as following:

Sub UpdateIndexes()
Dim rec As DAO.Recordset, inad1 As Address, i As Integer,

Set db = CurrentDb
Set rec = db.OpenRecordset("tblIndex")

inad1.Wbook = "\\c\file\*.xls"
Excel.Workbooks.Open inad1.Wbook, False, True

With ActiveWorkbook

I do not know about this part. The excel contains 9 sheets, for each
sheet, I want to call a function I have already defined . I do not know how to loop that.


End With


End Sub

 

[yn19832]

many thanks.
My codes are as following:

Sub UpdateIndexes()
Dim rec As DAO.Recordset, inad1 As Address, i As Integer,

Set db = CurrentDb
Set rec = db.OpenRecordset("tblIndex")

inad1.Wbook = "\\c\file\*.xls"
Excel.Workbooks.Open inad1.Wbook, False, True

With ActiveWorkbook

I do not know about this part. The excel contains 9 sheets, for each
sheet, I want to call a function I have already defined . I do not know how to loop that.


End With


End Sub