transfer data from an excel soreadsheet to an access database to show in report!

[maisam]

hi guys,

i am creating a report in an access databse. in this report i need to show data from a spreadsheet that is automatically updating every day.

how can i transfer this data into my databse in order to show it in the report. is there any way that i can populate a table with the data from the spreadsheet?

ps. i am a beginner in access databases and i would appreciate if you can help me with this.

Cheers

 

[Malcy]

Hi
I had to do similar and this is how I did it. May not be neatest but it works.
The code I use is

Private Sub cmdCpImpWkData_Click()
On Error GoTo Err_cmdCpImpWkData_Click
    Dim cnn As ADODB.Connection
    Dim strSQL As String
    Dim strPath1 As String
    Dim strPath2 As String
    
' Define paths
    strPath1 = "C:\Documents and Settings\Alan\My Documents\WorkData\StaffHolidayPlanner\Import\MgmShpExp.xls"
    strPath2 = "C:\Documents and Settings\Alan\My Documents\WorkData\StaffHolidayPlanner\Import\MgmShpWeekFix.xls"
    
' Clear down the previous upload files in the database
    DoCmd.DeleteObject acTable, "tblWeekFixUL"
    DoCmd.DeleteObject acTable, "tblWeekDataUL"
    
' Check that MgmShpExp.xls is in the import folder
    If Dir(strPath1) = "" Then
        MsgBox "MgmShpExp.xls is not in the Import folder. Please check.", vbExclamation, "Halt process"
        
        Exit Sub
    End If

' Check that MgmShpWeekFix.xls is in the import folder
    If Dir(strPath2) = "" Then
        MsgBox "MgmShpWeekFix.xls is not in the Import folder. Please check.", vbExclamation, "Halt process"
        
        Exit Sub
    End If
    
' Import the Excel files from the C:\ directory
    DoCmd.TransferSpreadsheet acImport, 8, "tblWeekDataUL", strPath1, True, "a1:g30"
    DoCmd.TransferSpreadsheet acImport, 8, "tblWeekFixUL", strPath2, True, "a1:b2"
    
' Update the upload check in tblWeekControl
    Set cnn = CurrentProject.Connection
    
    strSQL = "UPDATE [tblWeekControl] INNER JOIN [tblWeekFixUL] ON ([tblWeekControl].[lngWcYear] = [tblWeekFixUL].[lngYearFix]) AND ([tblWeekControl].[bytWcWeek] = [tblWeekFixUL].[bytWeekFix]) SET [tblWeekControl].[bImport] = Yes;"
        cnn.Execute strSQL
    
    Set cnn = Nothing
    
    MsgBox "The tables have been successfully imported", vbOKOnly, "Progress information"

You will not want to do all the bits I have had to do but it hopefully gives you a framework you can adapt as needed.
Hope this is some help
Good luck

 

[SammyJ]

If the spreadsheet doesn't change in name or format, then you might find it easier to create a link to it and run a report from it directly.