Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

How to get the Create DDL statement from SQL server through excel.

As you know, we can connect SQL server through excel VB and we can get the
data from a DB using DML statement. And also we can create a Table on the SQL
server DB, through excel VB as command execution. The following is a simple
example I used.

Sub ExecuteDDL()
Dim Cn As New ADODB.Connection
Dim Cmd As New ADODB.Command
Dim adoRs As New ADODB.Recordset

Cn.Provider = "sqloledb"
Cn.ConnectionTimeout = 7
Cn.CommandTimeout = 0
Cn.Provider = "sqloledb"
Cn.ConnectionString = "Data Source=TEST;DSN=TEST;UID=TEST;PWD=TEST�
Cn.Open

Set Cmd.ActiveConnection = Cn
Cmd.CommandText = "CREATE TABLE TEST_TABLE (C1 int, C2 char(100))"
Cmd.CommandType = adCmdText
Cmd.Execute

Cn.Close
End Sub

Before the execution above, I want to get the DDL statement of a Table from
DB.
The procedure that I want is like this.
I have a Table name that I want to recreate from DB.
1. Get the create DDL statement from DB
2. Edit the DDL statement
3. Execution DDL statement
I want to process above 3 step on excel through VB, but I don’t know how to
get the create DDL statement through excel macro( Number 1 step).

I can script DDL statement (Create or Drop, etc...) from DB through SQL
Sever Enterprise Manager Tool and make a text file from it.
But that way is very inconvenient to handle many tables and is required too
many manual steps.
It will be really helpful to all user for SQL server and Excel
Thanks a lot.


Post your answer or comment

comments powered by Disqus

Related Results

  1. Hot to get the create DDL statement from SQL Server through excel.
  2. How to get the selected textcell from a drop down list
  3. How to get the Address of the First/Last record from a Column/Row ?
  4. How to get the Menu of javascript from VBA?
  5. How to get the Font Color, Fill Color of cells with Conditional Formatting for more than 3 criterias using VBA?
  6. How to get the coordinate of the lower-bound cell in Excel 2003 with C#
  7. How to get the co-ordinates for placing an AutoShape Dynamically in a Sheet?
  8. How to get the sql procedure rs's table value and the return value with access vba
  9. How to limit the range of culomn from this A1 to last A column
  10. How to get the leading zero's added in a document
  11. How to determine the top 5 occurrences from a list of numbers?
  12. How to get the row index in excel shee
  13. How to get the row index in excel sheet
  14. How to get the (average or sum) in a limited period of time
  15. How to get the number of days in a year?
  16. Does anyone know how to get the code behind the standard excel form?
  17. How to get the Cursor always SET on the extreme left in a ComboBox?
  18. How to get the next empty row in a half filled Wroksheet?
  19. In vba what command is used to determine if a particular cell on a particular sheet changed? some kind of event? how to get the old and new value of the cell?
  20. How to get the number of days between two date formatted fields in vba for excel?
  21. How to get the total number of Cells in a specified cell?
  22. How to get the alphabetical reference of a cell
  23. How to get the Sum in a Single SUMPRODUCT maintaining the Different Criterions? Part-II
  24. How to get the complete list Excel Worksheet Functions
As you know, we can connect SQL server through excel VB and we can get the
data from a DB using DML statement. And also we can create a Table on the SQL
server DB, through excel VB as command execution. The following is a simple
example I used.

Sub ExecuteDDL()
Dim Cn As New ADODB.Connection
Dim Cmd As New ADODB.Command
Dim adoRs As New ADODB.Recordset

Cn.Provider = "sqloledb"
Cn.ConnectionTimeout = 7
Cn.CommandTimeout = 0
Cn.Provider = "sqloledb"
Cn.ConnectionString = "Data Source=TEST;DSN=TEST;UID=TEST;PWD=TEST�
Cn.Open

Set Cmd.ActiveConnection = Cn
Cmd.CommandText = "CREATE TABLE TEST_TABLE (C1 int, C2 char(100))"
Cmd.CommandType = adCmdText
Cmd.Execute

Cn.Close
End Sub

Before the execution above, I want to get the DDL statement of a Table from
DB.
The procedure that I want is like this.
I have a Table name that I want to recreate from DB.
1. Get the create DDL statement from DB
2. Edit the DDL statement
3. Execution DDL statement
I want to process above 3 step on excel through VB, but I don’t know how to
get the create DDL statement through excel macro( Number 1 step).

I can script DDL statement (Create or Drop, etc...) from DB through SQL
Sever Enterprise Manager Tool and make a text file from it.
But that way is very inconvenient to handle many tables and is required too
many manual steps.
I believe there is a way to get the statement through excel VB.
Please release my inquiry
Thanks a lot.

How to get the selected textcell from a drop down list?

Cheers

Ollie Riches

How to get the Address of of the First/Last record from a Column/Row ?

Dear Forum,

I am in need of getting the Address as well as the value of data stored in Column/Row

Ex:

Row 1 Col A Col B
Row 2 28/7/2010
Row 3 28/7/2011
Row 4 28/7/2012
Row 5 28/7/2013
Row 6 28/7/2014
Row 7 28/7/2015
Row 8 28/7/2016 Tank 3
Row 9 28/7/2017 Tank 3
Row 10 28/7/2018 Tank 3
Row 11 28/7/2019 Tank 3
Row 12 28/7/2020 Tank 3
Row 13 28/7/2021 Tank 3
Row 14 28/7/2022 Tank 3
Row 15 28/7/2023 Tank 3

Now the first record is "Tank 3" and and the Address would be "$B$8" and the last record is in the cell "$B$15"

I need to get the same incase it was RowWise Data...

Warm Regards
e4excel

<SCRIPT LANGUAGE="JavaScript">
window.MenuNB = new Menu("MenuNB");

MenuNB.addMenuItem("NewQuote/Application","../servlet/com.viking.autolink.servlets.AgentLoginServlet?modInd=QQV&applicationInd=QQ&requestStateCode=");

MenuNB.addMenuItem("Retrieve Saved Quote/Application","../servlet/com.viking.autolink.servlets.AgentLoginServlet?modInd=RSQV&applicationInd=QR&requestStateCode=");

</SCRIPT>

I have this above java script in a .jsp file. Usually i used to get the elements of html by using getElementsbyTagName() mehtod. Since the above Menu(MenuNB) is a javascript, i was not able to use it.
In the above script there are two menu items and their corresponding url's. I am doing an automation framework to control the webflow from Excel macro(VBA) . So i need to click this menu item 1 from VBA. Kindly advise how it can be done? How can i access the Menu of javascript from VBA and how can i click that Menu item?

Thanks in Advance.

How to get the Font Color, Fill Color of cells with Conditional Formatting for more than 3 criterias using VBA?

Hello Board,

Is it possible to get the Color Codes like Color Nos for cells which have conditional formatting..
When we try using the .Font.Color or .Font.ColorIndex it gives the number only for the color which is applied manually and not for any color via conditional formatting.

If i have more than three criterions in my conditional format, then is it possible to get the Font Color, Fill Color i.e. Interior Color and if possible other aspects such as Font.Italics or Font.Bold too using VBA?

Will the code be able to read the Font Color Code and Fill Color Code no from individual cell with conditional formatting rather than looking at a Range, In case, if we were to provide a cell range for example A2 where the Font Color is red and the Fill Color is Yellow and in cell A3 Font Color is Blue and the Fill Color is Orange then is it possible to just to give the Range as A2 or A3 and get both the details..as well as if the other aspects such as the Font Bold and Italics.

Hi all,
I am using C# to extract data from Excel 2003. I want to know how to get
the column number and row number of cells with real data in the current
sheet.
Thanks
E.g
A B C D E F G H
1 1 1 1
2 1 1 1
3
4
5

Column Number:3
Row Number:2

zlf

How to get the co-ordinates for placing an AutoShape Dynamically in a Sheet?

Dear Forum,

I intend to place a Dynamic AutoShape of a Right & Left Pointing Arrows in a Sheet which will containg Hyperlinks to move between sheets.

I have been able to manage this dynamically as these Sheets are also created Dynamically, however I want them to be at a particular position in a particular cell but in the code I need to provide the co-ordinates which I dont know..

I also want to know how to change the Font, Font Colour ,Font Size of the Text in the AutoShape.

Please find the code below:

Sub NewShape()
Dim i As Integer
i = 33  ' Where 33 is for the Right Side Pointing Arrow shape..

   Set shp = ActiveSheet.Shapes.AddShape(i, 48, t, 94, 42)
   shp.TextFrame.Characters.Text = "Ledger-View"
   'shp.TextFrame.Characters.FontSize = 12
   shp.Name = "Right Arrow"

    With ActiveSheet
        .Hyperlinks.Add Anchor:=.Shapes("Right Arrow"), Address:="C:Documents and SettingsVarunDesktopITR
Saral 2009-10.xls", SubAddress:="Sheet1!A1"
    End With

End Sub

I want this Autoshape to be place in the Cell E2 and the Column Widths of the
Column A-- 3.29
Column B-- 26
Column C--14.57
Column D--43

So how do I mange that and also I have something as "t" in the code marked in red..I dont know whats that?

Set shp = ActiveSheet.Shapes.AddShape(i, 48, t, 94, 42) So please guide me after normally placing an Autoshape where do we get to see the co-ordinates?

Warm Regards
e4excel

A access Form Called an SqlServer stored Procedure
The stored procedure returns a recordset and a return value, How to get the recordset and the returned value.
The code like follows just get the RecordCount = -1 all the time.

	VB:
	
 test() 
    Dim cn2 As New ADODB.Connection 
    Dim cmd     As New ADODB.Command 
    Dim rs3 As New ADODB.Recordset 
     
    Set cn2 = New ADODB.Connection 
    Set rs3 = New ADODB.Recordset 
    Set cmd = New ADODB.Command 
     
    constr = "provider=sqloledb;server=shaxappxa;database=abcde_Test;uid=abcdef_Test;pwd=Passwordaa" 
    cn2.Open constr 
     
    cmd.ActiveConnection = cn2 
    cmd.CommandType = adCmdStoredProc 
    cmd.CommandText = "usp_transfer_fromTempCustomer_2012" 
    cmd.Parameters.Refresh 
    cmd.Parameters("@User") = "AccessTo" 
    cmd.Parameters("@theYear") = selectYear 
    cmd.Parameters("@thePeriod") = selectMonth 
    cmd.Parameters("@type") = ImportD 
    Set rs3 = cmd.Execute() 
    Debug.Print rs3.RecordCount 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
When The Import=0 the stored procedure return value 0,The Import=1 the stored procedure return value -1 and a datatable,How can I get all the recordset ??
And there is anyother porblem, many times I run the Vba,It takes the '3704' error "Operation is not allowed when the object is closed"......what's the problem?

this code to limit all range column:
Range(A:A)
my queston is :
How to limit the range of column from the A6 to last A column.

hi
i have a document which is added with leading Zero's in some of the
columns

for eg., in the ZIPCODE column my value is 09281 but in the display i am
getting it

as 9281. But i want it with the original value. Can anyone tell me how to
get the

original values

Does anyone have any suggestions on how to determine the top 3 occurrences
from a list of numbers? For example,

1,1,1,1,1,2,2,2,3,3,3,3,3,3,4,4,5,5,5,5,5,6,6,6,6, 7,7,7,7,7 under column A
The occurrence of 1 is 5 times
The occurrence of 2 is 3 times
The occurrence of 3 is 6 times
The occurrence of 4 is 2 times
The occurrence of 5 is 5 times
The occurrence of 6 is 4 times
The occurrence of 7 is 5 times

It should return 3 in cell B1, 1 in cell B2, 5 in cell B3,
Since there are 3 numbers having 5 occurrences - 1,5,7, I only need to
select the rest of 2 numbers, then I prefer the smaller one 1, 5, and
ignoring 7.

Does anyone have any suggestions on how to do it in excel?
Thanks ver much in advance for any suggestions
Eric

Hi ,
how to get the row index in excel sheet when changes done in a any cell/any number of cells of a specific row using macros.

Hi,
How to get the row index in excel sheet when changes done in a any cell/any number of cells of a specific row using macros.
Its urgent plz help me

(sorry about my bad English)

This code help me in typing the date and time in any cell of column (B) automatically just in case I enter somthing in any adjacent cell of column (A)

Private Sub Worksheet_Change(ByVal Target As
Range)
    If Not Intersect(Target, Range("A:A")) Is Nothing Then
         Target.Offset(0, 1).Value = Now
    End If
End Sub

I want to know how to get the result of average or sum in a limited period of time. For example after completing(24 hours or 7 days).

if I enter number in (A1) then the time and date put automatically in (B1)
and I enter number in (A2) then the time and date put automatically in (B2)
and I enter number in (A3) then the time and date put automatically in (B3)
and I enter number in (A4) then the time and date put automatically in (B4)

where for example
(A1) = 10
(A2) = 10
(A3) = 10
(A4) = 10

in cell (C1) the result of (average) of numbers that enterd in 24 hours (after completing 24 hours)

After that move automatically to cell (C2) to get automatically the result of average of the next 24 hours

After that move automatically to cell (C3) to get automatically the result of average of the next 24 hours

and so on

I want to get the (sum) and (average) with periods of (24 hours) and (7days) by any way or methode using excel 2007

Hi,

May i know how to get the number of days in a year?So that i can differtiate between a leap year and a normal year.Thanks!!!

Does anyone know how to get the code behind the standard excel form?

Hi
I am in way over my head but seem to be getting on ok!!
I have a form to add new employees it works perfectly!

however i now want to amend colleagues that are already entered...

for example i want to click "amend" and the top colleagues details are shown in the textbox's i then want to click a "next" button until i find the person i want to amend. Once i have found that person i want the option to click "delete" and all employees below move up a space to fill the gap, or to enter updated information and click "ok" to add to spreadsheet!!

thanks for any help you guys can give!!!

How to get the Cursor always SET on the extreme left in a ComboBox?

I am using a ComboBox in my UserForm to enter Dates in the Form , however I am using partial dates in the ComboBox List in the format as shown below:

1. -Feb-2011
2. -Mar-2011
3. -Apr-2011
4. -May-2011
5. -Jun-2011

As can be seen that these are just partial dates and the Date (i.e Numeric Date) is added manually this is just to save time for the user..

Now, everythings working fine however what happens is that once the selection is done the user still needs to use the Mouse to select the left side to enter the numeric date is there any way of getting to the extreme left-most character in the ComboBox..

I am aware of the SetFocus but that's not what I am after as I want to go to a particular location in the ComboBox..

I am using this Modus Operandi of Partial Dates in several such Combo-Boxes so it would be a good time-saver, if thats possible..

Warm Regards
e4excel

hi,

How to get the next empty row in a half filled Wroksheet?

in vba what command is used to determine if a particular cell on a
particular sheet changed? some kind of event? how to get the old and new
value of the cell?

how to get the number of days between two date formatted fields in vba for
excel?

Dear Friends,

How to get the total number of Cells (which contatins data) in a specified cell?

Please help.

Thanks.

Working fine. Thanks.

Hi,

I am a newbie to excel/macro development.

I wanted to know how to get the alphabetical reference of a column based on
a cell. For example, the formula =Column(C2) gives me the value of 3. But i
need to get 'C' instead of 3. May I know how to go about getting 'C' using an
excel formula?

Thanks

How to get the Sum in a Single SUMPRODUCT maintaining the Different Criterions? Part-II

Dear Forum,

I am using the ingenious SUMPRODUCT to get the SUM through a Range of Columns and have done it succesfully courtesy the Excel Forum's help..
However, I feel like I have hit a wall now that the there is one more criteria which has come-up..
Finding the Stock received in particualr Months..

The example provided is a representation of the actual file as that cannot be uploaded due to sensitive nature of the data..

Basically, Countering Multiple Column Criterion Addition using SUMPRODUCT...what it means is I have columnar data and I need to add the Fruit Stock Received for a particular client in a specific month..

Now each Client has several Fruit Columns with each Fruit Date, but in actuality we are going to add only those Fruit stocks which are mentioned in the drop-down next to the Vendor Name.

I think the file would give a better picture..

Warm Regards
e4excel

Hi

I would like to know how to get the complete list of the worksheet functions
seen in the wizard Functions dialog box and list them in a worksheet or a
file !

Thank's

Philippe


No luck finding an answer? You could always try Google.