Solved IMPORT EXCEL FILE TO ACCESS TABLE

[georg0307]

Dear All,

I need to import data to an Access table only certain Excel range ( Attached the pdf image of excel file).

I am newbie, for this matter.

Could you please help with the VBA code to use for this case?

Many thanks in advance,

Best regards,

Georg

 

[theDBguy]

Hi. Sorry can't look at your file right now, but have you tried the TransferSpreadsheet method?

Sent from phone...

 

[georg0307]

Hi,

thank you for prompt reply.

I added the *.zip file of the original Excel file.

I don't know how to add excel specific ranges (not homogeneous, but fixed) , to specific fields of the access table.

I attached an image (IMPORT_EXCEL.pdf) of what I need.

I hope this draw is clear.

Georg

 

[bastanu]

You will need to use Excel automation for that, or easier you could add a sheet in your Excel file where you build a list with the first row as the field names and second the values. That sheet can then be very easily imported in Access using the TransferSpreadsheet method.
Cheers,
Vlad

 

[Pat Hartman]

If you have a named range, TransferSpreadsheet can import the specified range. Otherwise, as the others have said, you will need to use OLE automation.

 

[bastanu]

Have a look at the attached files, using the Access db you can import the DataList sheet in the modified Excel file.

Cheers,
Vlad

 

[georg0307]

DearAll,

Thanks to all.

I will try, with your suggestions.

Best regards.

 

[theDBguy]

DearAll,

Thanks to all.

I will try, with your suggestions.

Best regards.

Good luck. Let us know how it goes.

 

[georg0307]

Have a look at the attached files, using the Access db you can import the DataList sheet in the modified Excel file.

Cheers,
Vlad

Hi,

I cannot modify named ranges in the Excel file, or any other feature of the same.
The file is as the last I sent.
I think I need something different, OLE automation may be a solution, but it is something out of my knowledge.
Could you help me with this, is there any other way?

Thanks in advance,
Georg

 

[ebs17]

Look at that:

Sub Import_XLData(ByVal XLFile_FullPath As String)
    Dim rs As DAO.Recordset
    Dim oXL As Object
    Dim oWB As Object

    Set rs = CurrentDb.OpenRecordset("PIR_TABLE", dbOpenDynaset)

    Set oXL = CreateObject("Excel.Application")
    Set oWB = oXL.Workbooks.Open(XLFile_FullPath)
    With oWB.worksheets("ISSUES")
        rs.AddNew

        rs.Fields("RIP") = .Range("B8").Value         ' possibly still type conversion
        ' ... other fields

        rs.Update
        rs.Close
    End With

    oWB.Close SaveChanges:=False
    oXL.Quit

    Set oWB = Nothing
    Set oXL = Nothing
    Set rs = Nothing
End Sub

Eberhard

 

[georg0307]

Look at that:

Sub Import_XLData(ByVal XLFile_FullPath As String)
    Dim rs As DAO.Recordset
    Dim oXL As Object
    Dim oWB As Object

    Set rs = CurrentDb.OpenRecordset("PIR_TABLE", dbOpenDynaset)

    Set oXL = CreateObject("Excel.Application")
    Set oWB = oXL.Workbooks.Open(XLFile_FullPath)
    With oWB.worksheets("ISSUES")
        rs.AddNew

        rs.Fields("RIP") = .Range("B8").Value         ' possibly still type conversion
        ' ... other fields

        rs.Update
        rs.Close
    End With

    oWB.Close SaveChanges:=False
    oXL.Quit

    Set oWB = Nothing
    Set oXL = Nothing
    Set rs = Nothing
End Sub

Eberhard

Hi,
Thanks in advance, let's try and let you know.

 

[bastanu]

Here you go.
Cheers,
Vlad

 

[georg0307]

Hi Vlad,

sorry for late answer but here in Italy as you know per News we have some CoronaVirus problems. Only to say thank you very much this code is perfect for me.

Georg

 

[bastanu]

You're very welcome Georg, good luck to you guys,unreal what's happening, last fall we were in Venice, Bologna,Florence and its is heart-breaking to see the impact of the virus now.
Cheers,
Vlad