OLE error

[mishra.ananda]

Hi All;

I am created a excel sheet in excel 2003 in which we import another excel sheet. for this I use OLEDB connection. And the entire code written inside the Macro is given below;



Sub san()
'
' san Macro
' Macro recorded 07/07/2009 by DRDA
'

'
NewFN = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls), *.xls", Title:="Please select a file")
Cells.Select
Selection.ClearContents
On Error GoTo x
Selection.QueryTable.Delete
x:

With ActiveSheet.QueryTables.Add(Connection:=Array( _
"OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source=" & NewFN & ";Mode=Share Deny Writ" _
, _
"e;Extended Properties=""HDR=YES;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Database Password="""";Jet OLED" _
, _
"B:Engine Type=35;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet " _
, _
"OLEDB:New Database Password="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Loca" _
, _
"le on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False" _
), Destination:=Range("A1"))
.CommandType = xlCmdTable
.CommandText = Array("Database")
.Name = NewFN '"Belpara"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = True
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.SourceDataFile = NewFN '"E:\My Documents\Balangir\Belpara.XLS"
.Refresh BackgroundQuery:=True ' False
End With
End Sub



When ever I want to run the same Macro in excel2007 it is not possible.

I think there is a problem in connection So ijust modified it as;



Provider=Microsoft.Jet.OLEDB.4.0; <===> Provider=Microsoft.ACE.OLEDB.12.0;



So please help me to solve this problem......

Thank you

[DonkeyOte]

Welcome to the Board, however, please take the time to read the Forum Rules before posting any further.

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here


As you can see I have moved your post into your own thread, note below:


Rule 2 of our Forum

Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.