Random errors when Macro for Importing data from one excel file to another

[saadkhan]

Hi excel experts

I have gone through the forum and searched for the past few weeks trying to get the following macro to work. When it no longer gives me errors, I share it with others and someone fires an email back saying the macro doesnt work (with the files attached) and it also doesnt work on my machine.

There are 2 excel files. One is the data file (xls) and the report sheet with the macro (xlsm). The user opens the report sheet. It has a macro that prompts the user which data file he wishes to imported to the report sheet. The macro imports the data file into another worksheet by the name of "Target" in the report sheet.

This procedure and the macro I come up with and tinker with every now and then comes back to me with some random error. Either its "Invalid procedure call or argument" or its "Automation error The object invoked has disconnected from its clients" or its just some other Run time error.

I would be thoroughly happy if you experts can tell me a perfect implementation of this macro. I'm using the following code.

    Sheets("Target").Select
    Range("A3").Select
    vntFilename = Application.GetOpenFilename(filefilter:="Excel Files, *.xls", _
    Title:="Choose Daily 2G Report data file", MultiSelect:=False)
    If vntFilename = False Then 'user hit cancel
        Sheets("Control").Visible = False
        Sheets("Target").Visible = False
        Application.ScreenUpdating = True
        Exit Sub
    End If
    sPath = Left(vntFilename, InStrRev(vntFilename, "\") - 1)
    sDB = Split(Split(vntFilename, "\")(UBound(Split(vntFilename, "\"))), ".")(0)

    sConn = "OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;"
    sConn = sConn & "Password="""";User ID=Admin;"
'v    sConn = sConn & "Data Source=" & sPath & ";"
    sConn = sConn & "Data Source=" & vntFilename & ";"
    sConn = sConn & "Mode=Share Deny Write;Extended Properties=""HDR=YES;"";"
    sConn = sConn & "Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";"
    sConn = sConn & "Jet OLEDB:Database Password="""";Jet OLEDB:Engine Type=35;Jet OLEDB:Database Locking Mode=0;"
    
    sConn = sConn & "Jet OLEDB:Global Partial Bulk Ops=2;"
    sConn = sConn & "Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="""";"
    sConn = sConn & "Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;"
    sConn = sConn & "Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;"
    sConn = sConn & "Jet OLEDB:SFP=False;"
    sConn = sConn & "Jet OLEDB:Support Complex Data=False"
    
    Range("A3").Select
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=sConn, Destination:=Range("$A$3")). _
        QueryTable
        .CommandType = xlCmdTable
        .CommandText = Array("Data$")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .SourceDataFile = vntFilename
        .ListObject.DisplayName = "Table_report_6"
        .Refresh BackgroundQuery:=False
    End With

My machine is a Windows XP running Excel 2003 (has a plugin to open/save Excel 2007 documents). Others in the team have different OS (either WinXP or Win7) and Excel versions (either Excel2003 or Excel2007). Would there be any generic way of making this work across all OS/Excel-versions!?

Really appreciate the help!

 

[saadkhan]

Could I be missing something !? Is the code I'm using correct ? Does anyone have a better implementation ?