Solved IMPORT EXCEL FILE TO ACCESS TABLE

georg0307

Registered User.
Local time
Today, 13:33
Joined
Sep 11, 2014
Messages
91
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
 

Attachments

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

Sent from phone...
 
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
 

Attachments

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
 
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.
 
Have a look at the attached files, using the Access db you can import the DataList sheet in the modified Excel file.

Cheers,
Vlad
 

Attachments

DearAll,

Thanks to all.

I will try, with your suggestions.

Best regards.
 
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
 
Look at that:
Code:
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
 
Look at that:
Code:
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.
 
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
 
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
 

Users who are viewing this thread

Back
Top Bottom