We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

UPDATE SQL FROM EXCEL

Medium Priority
446 Views
Last Modified: 2013-11-25
I think that I posted this tread in the wrong places...
So let´s see if here at VB I could find a good soul who could help me.

Attached is the spreadsheet that I´m working with, which have some macros in it to allow the user to create a New Final Tax Report according to my Country Regulations.

The RED Sheet (DATOS) is filled with the get external data tool in excel from My Data Base (CEDRO)

The formulas in the GREEN Sheet (CAMBIAR AQUI) are made in the way to determinate if there is wrong entrys in the Data Base before to Print the Final TAX report in the Blue Sheet (LIBRO DE IVA)

The original SQL that I Copy from the MSQUERY to import the data is as Follows:

SELECT cast(CONVERT(varchar(8), B.FechaE, 112) AS datetime) , B.NumeroD, B.Descrip, B.ID3, B.Monto, B.MtoTax, B.RetenIVA, A.TipoCli, A.EsGranCont, B.CodUsua, B.CodVend
FROM CEDRO.dbo.SACLIE A, CEDRO.dbo.SAFACT B
WHERE A.CodClie = B.CodClie AND ((B.FechaE Between ? And ?) AND (B.TipoFac Between ? And ?))
ORDER BY cast(CONVERT(varchar(8), B.FechaE, 112) AS datetime), B.NumeroD

What I need is to give the user the chance to update the field ID3 (Column D in DATOS Sheet) and then with a Button UPDATE the SQL  TABLES: SACLIE and SAFACT (Both Tables linked by CodClie (KEY) and the ID3 which is the field I need to Change.

Other Info:
SERVER NAME: SERVIDOR
DATA BASE: CEDRO
TABLES: SACLIE (Main Table...contains all Customer Details) and SAFACT (Contains Invoice Info)
WORKBOOK NAME: CREAR NUEVO LIBRO DE VENTAS

I´m really new in VB features...some superficial knoledge only and even when I find some similar situations here I´m not able to use it in my case...If you can help me please do it...I´ll put your picture as my default desktop
CREAR-NUEVO-LIBRO-DE-VENTAS-CORR.xls
Comment
Watch Question

Commented:
Not quite sure what you need but maybe have a user form with some text fields that a user can fill in and then use a command button to start the update. From there use your database connection and then use the sql UPDATE someTable SET someField=someInfo WHERE someField=someOtherInfo. Is this helpful?

Author

Commented:
Hi Borgunit,

That is exactly what I need.

I need the Command Button you said in the Sheet DATOS or in a new Sheet as the User Form you mention (That would be great !!!).

If you can help me to do this please send me your picture to use it as my default Desktop.  :-)

Regards,
Roberto.
Commented:
Here is the Solution:
 Stored Procedure in SQL:
USE [CEDRO]
GO
/****** Object:  StoredProcedure [dbo].[up_UpdateSpreadsheetData]    Script Date: 02/16/2009 06:07:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:            <Author,,Name>
-- Create date: <Create Date,,>
-- Description:      <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[up_UpdateSpreadsheetData] @CodClie varchar (10), @Descrip varchar(40), @ID3 varchar(15) AS
IF EXISTS(SELECT CodClie FROM SACLIE WHERE CodClie = @CodClie AND (Descrip <> @Descrip OR ID3 <> @ID3))
IF EXISTS(SELECT CodClie FROM SAFACT WHERE CodClie = @CodClie AND (Descrip <> @Descrip OR ID3 <> @ID3))
BEGIN
  UPDATE SACLIE SET Descrip = @Descrip, ID3 = @ID3
  WHERE CodClie = @CodClie
  UPDATE SAFACT SET Descrip = @Descrip, ID3 = @ID3
  WHERE CodClie = @CodClie
END

The code to make the call from VB is attached in the COde Snippet.

Regards,
Roberto.

Public Sub CommandButton1_Click()
Set cnn = New ADODB.Connection
sConnString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;" & _
    "Persist Security Info=False;" & _
    "Initial Catalog=CEDRO;" & _
    "Data Source=VAIO\SERVIDOR"
cnn.Open sConnString
 
Set Cmd = New ADODB.Command
Cmd.ActiveConnection = cnn
 
With Cmd
    .CommandType = adCmdStoredProc
    .CommandText = "up_UpdateSpreadsheetData"
End With
 
Set param = New ADODB.Parameter
With param
    .Name = "@CodClie"
    .Type = adVarChar
    .Size = 10
    .Value = Range("A2").Value
End With
Set param2 = New ADODB.Parameter
With param2
    .Name = "@Descrip"
    .Type = adVarChar
    .Size = 40
    .Value = Range("B2").Value
End With
Set param3 = New ADODB.Parameter
With param3
    .Name = "@ID3"
    .Type = adVarChar
    .Size = 15
    .Value = Range("C2").Value
End With
    
Cmd.Parameters.Append param
Cmd.Parameters.Append param2
Cmd.Parameters.Append param3
Code:
    Cmd.Execute
 
End Sub

Open in new window

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.