PDA

View Full Version : Solved: Extract data from SQL Server table into excel



abhay_547
04-25-2010, 05:59 AM
Hi All,

I have recorded a macro from excel by using Data-Import External Data-> Import Data and the connection I have established from SQL server and while importing data I have edited the query with my query but when I try to run the macro it shows and .i.e table does not exists but actually I have checked my database name and other tables present in my sql server and the name convention in my query is my query is exactly same but still it shows the above error. Following is my code and I don't what is going wrong. Please help.

Sub ExtractDatafromSQL()

With ActiveSheet.QueryTables.Add(Connection:=Array( _
"OLEDB;Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Data Source=DB-77716EFB0313\SQLEXPRESS;Use Procedure for" _
, _
" Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=DB-77716EFB0313;Use Encryption for Data=False;Tag with column col" _
, "lation when possible=False;Initial Catalog=meta_data"), Destination:=Range( _
"A1"))
.CommandType = xlCmdTable
.CommandText = Array("SELECT mydata.*, [Cost Center mapping].[Product UBR Code], [Cost Center mapping].[Sub Product UBR Code], [Cost Center mapping].[Sub-sub Product UBR Code], [Cost Element Mapping].FSI_LINE1_code, [Cost Element Mapping].FSI_LINE2_cod" _
, _
"e, [Cost Element Mapping].FSI_LINE3_code, [Country and Region Mapping].Country, [Country and Region Mapping].Region" & Chr(10) & "FROM ((mydata INNER JOIN [Cost Center mapping] ON mydata.[Cost Center] = [Cost Center mapping].[Cost Center]) IN" _
, _
"NER JOIN [Cost Element Mapping] ON mydata.[Unique Indentifier 1] = [Cost Element Mapping].CE_SR_NO) INNER JOIN [Country and Region Mapping] ON mydata.[Company Code] = [Country and Region Mapping].[Company Code]" & Chr(10) & "WHERE ((([Cost Ce" _
, _
"nter mapping].[Product UBR Code])=""P_6957"") AND (([Cost Center mapping].[Sub Product UBR Code])=""P_8456"" Or ([Cost Center mapping].[Sub Product UBR Code])=""P_8453"") AND (([Cost Center mapping].[Sub-sub Product UBR Code])=""P_6975"" " _
, _
"Or ([Cost Center mapping].[Sub-sub Product UBR Code])=""P_6984"") AND ((([Cost Element Mapping].[FSI_LINE1_code])=""F1750000000"") AND (([Cost Element Mapping].[FSI_LINE2_code])=""F1753000000"" Or ([Cost Element Mapping].[FSI_LINE2_code])=""F1757001000"") " _
, _
"AND (([Cost Element Mapping].[FSI_LINE3_code])=""F1753007000"" Or ([Cost Element Mapping].[FSI_LINE3_code])=""F1757002000"") AND ((mydata.Period)=1) AND ((mydata.Year)=2010)));" _
)
.Name = "DB-77716EFB0314_SQLEXPRESS meta_data mydata"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.SourceConnectionFile = _
"C:\DB-77716EFB0314_SQLEXPRESS meta_data mydata.odc"
.Refresh BackgroundQuery:=False
End With
End Sub

Thanks for your help in advance.:bow:

lucas
04-25-2010, 06:07 AM
please use the vba tags when posting code. NOT the html tags..

It's not html, it's vba code.

abhay_547
04-25-2010, 07:48 AM
Hi Steve,

I have used VBA tags only. Please see my post properly..

lucas
04-25-2010, 07:52 AM
My apologies. It ran so far off the screen to the right that I mistakenly thought you had used the wrong tags.

mdmackillop
04-25-2010, 01:43 PM
It's very easy to make a mistake in these long sql strings. I'd try to get it working with a very simple import first to test connection, then proceed from there.

abhay_547
04-25-2010, 07:54 PM
Hi mdmackillop,

I have already tried the same and it's working fine with the below (Highlighted in Bold). But when I put my another sql statements which are there in my earlier post it doesn't work.

Sub ExtractDatafromSQL()

With ActiveSheet.QueryTables.Add(Connection:=Array( _
"OLEDB;Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Data Source=DB-77716EFB0313\SQLEXPRESS;Use Procedure for" _
, _
" Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=DB-77716EFB0313;Use Encryption for Data=False;Tag with column col" _
, "lation when possible=False;Initial Catalog=meta_data"), Destination:=Range( _
"A1"))
.CommandType = xlCmdTable
.CommandText = Array("""meta_data"".""dbo"".""mydata""")
.Name = "DB-77716EFB0314_SQLEXPRESS meta_data mydata"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.SourceConnectionFile = _
"C:\DB-77716EFB0314_SQLEXPRESS meta_data mydata.odc"
.Refresh BackgroundQuery:=False
End With
End Sub

Thanks for your help in advance...:bow:

stanl
04-27-2010, 05:09 AM
Hi All,

I have recorded a macro from excel by using Data-Import External Data-> Import Data and the connection I have established from SQL server

From my experience, it is much easier to use MDAC and "SELECT INTO" or the native SQL Server Functions (openrecordset(); opendatabase()) since these can be run as stored procs and create Excel Files on the fly w/out embedded macro code. .02 Stan

abhay_547
04-27-2010, 11:17 AM
Hi Stan,

First thing I don't know how to use MDAC option and that's why I am recording the macro to extract data from SQL table using Data-Import External Data-> Import Data in excel. However I have heard that if user uses the Import External Data option for pulling the data from SQL or Access data tables through recorded macro or manually then it's much faster then MDAC.

As I said in my earlier post the code works fine when I don't enter any parameters. I tried to check the same code to pull the data from Access and it's working fine with access but when I try to use it with SQL server it doesn't work. Please help.

Thanks for your help in advance.

abhay_547
04-28-2010, 12:05 PM
Hi Stan,

Did you get the chance to look into the above. ?

abhay_547
05-19-2010, 03:13 AM
Hi mdmackillop,

Finally I got it, I have made the below change to my code and it works now.

Old Line :
.CommandType = xlCmdTable

New line :
.CommandType = xlCmdsql

Any ways, Thanks a lot.