Consulting

Results 1 to 10 of 10

Thread: Solved: Extract data from SQL Server table into excel

  1. #1

    Solved: Extract data from SQL Server table into excel

    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.

    [VBA]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[/VBA]

    Thanks for your help in advance.

  2. #2
    Moderator VBAX Wizard lucas's Avatar
    Joined
    Jun 2004
    Location
    Tulsa, Oklahoma
    Posts
    7,323
    Location
    please use the vba tags when posting code. NOT the html tags..

    It's not html, it's vba code.
    Steve
    "Nearly all men can stand adversity, but if you want to test a man's character, give him power."
    -Abraham Lincoln

  3. #3

    Extract data from MS Access table using excel macro.

    Hi Steve,

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

  4. #4
    Moderator VBAX Wizard lucas's Avatar
    Joined
    Jun 2004
    Location
    Tulsa, Oklahoma
    Posts
    7,323
    Location
    My apologies. It ran so far off the screen to the right that I mistakenly thought you had used the wrong tags.
    Steve
    "Nearly all men can stand adversity, but if you want to test a man's character, give him power."
    -Abraham Lincoln

  5. #5
    Administrator
    VP-Knowledge Base
    VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    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.
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  6. #6

    Extract data from MS Access table using excel macro.

    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.

    [VBA]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[/VBA]

    Thanks for your help in advance...

  7. #7
    VBAX Master stanl's Avatar
    Joined
    Jan 2005
    Posts
    1,141
    Location
    Quote Originally Posted by abhay_547
    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

  8. #8

    Smile Extract data from SQL Server table in excel

    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.

  9. #9

    Extract data from SQL Server table into excel

    Hi Stan,

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

  10. #10

    Extract data from SQL Server table into excel

    Hi mdmackillop,

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

    Old Line :
    [VBA].CommandType = xlCmdTable [/VBA]

    New line :
    [VBA].CommandType = xlCmdsql [/VBA]

    Any ways, Thanks a lot.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •