Inporting Summary Sheets

[Kapelluschsa]

Hi everyone,

I am currently trying to figure out a way in which I can import entire excel summary sheets into an access query. Is there a way I can refer the a query to a summary sheet by telling it which cell in excel to reference to each cell in the table? That way I could import each report instead of typing each of them manually from excel to access.

[maximus]

You can use Docmd.TransferSpreadSheet to import data from Excel sheet.

I have used this code to Transfer Data from an Excel Sheet name Book2.xls in my C:\. I have Imported data only from Sheet1 Range A1:F13 into a table MYTable.

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "MyTable", "C:\Book2.xls", True, "Sheet1!A1:F13"

[maximus]

You can also dynamically assign the Criteria and Table Name, File Name etc and Example.

I have used the Following Text Boxes to assign the following:
Table Name=Text1
SheetName=Text3
OpeningRange=Text5
ClosingRange=Text7

The code:
Dim strTableName As String
Dim strRange As String

strTableName = ""
strRange = ""

If IsNull(Me.Text1) Then
MsgBox "Please Type a Table name"
Exit Sub
Else
strTableName = Me.Text1

If IsNull(Me.Text3) Then
MsgBox "Please type a Valid Sheet Name"
Exit Sub
Else
If IsNull(Me.Text5) Then
If IsNull(Me.Text7) Then
strRange = Me.Text3 & "$"
End If
End If
If Not IsNull(Me.Text5) Then
If IsNull(Me.Text7) Then
MsgBox "Please Type Closing Range"
Exit Sub
End If
End If
If IsNull(Me.Text5) Then
If Not IsNull(Me.Text7) Then
MsgBox "Please Type the opening Range"
Exit Sub
End If
End If
If Not IsNull(Me.Text5) Then
If Not IsNull(Me.Text5) Then
strRange = Me.Text3 & "!" & Me.Text5 & ":" & Me.Text7
End If
End If
End If

End If

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, strTableName, "C:\Book2.xls", True, strRange

[maximus]

have u been able to solve your problem? Please mark this threas solved if it helped u to solve ur problem.