增加值和文本 [英] Incrementing value along with text

问题描述

Hello All，



我从Excel中获取了Excel表格，在Excel中编辑1或2列值后，我应该发回Excel表格到数据库，但它应该存储为数据库中的新表，所以原始表应该是原样。



除此之外，我需要1列自动递增具有文本+值（例如：ver1）。如果原始表格具有列Ver1，那么来自Excel的更改后的表格应该作为Ver2放置其中的所有值。



请帮忙解决这个问题。

我正在使用VBA宏。

Hello All,

I got an Excel sheet which is pulled from Database and after editing 1 or 2 column values in Excel, I should send back that Excel sheet to Database but it should get stored as New Table in Database so the original table should be as it is.

Along with this i need 1 column to be auto incremented which has text + value (eg:ver1).If original table has column Ver1 then the altered table which comes from Excel should be placed as Ver2 for all the values in it.

Please help to sort this out.
Im using VBA macros for this.

Private Sub CommandButton3_Click()
Dim adoCN As ADODB.Connection
    Dim sConnString As String
    Dim sSQL As String
    Dim lRow As Long, lCol As Long
     
    sConnString = "Provider=sqloledb;Server=;Database=;User Id=;Password="
     
    Set adoCN = CreateObject("ADODB.Connection")
     
    adoCN.Open sConnString
     
    'Assumes that you have Field1, Field2 and Field3 in columns A, B and C
    'For this example we can assume that the data exists on Sheet1, with a header on row
    '1 and data in rows 2-11
    'Also assume that the fields are defined as character (e.g. varchar or char)
    'Text values must be enclosed in apostrophes whereas numeric values should not.
     
    'adoCN.Open
    
    For lRow = 1 To ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row
'
    sSQL = "Select * INTO Ctss From Sheets(1)"

    adoCN.Execute sSQL

    sSQL = "INSERT INTO Ctss VALUES (" & "'" & Sheets(1).Cells(lRow, 4) & "', " & "'" & Sheets(1).Cells(lRow, 5) & "')"
'
   adoCN.Execute sSQL
''
'    sSQL = "UPDATE Tabs " & _
'" SET Version = 'ver6' "
'
'adoCN.Execute sSQL


'sSQL = "create function CustomerNumber (@id int)" & _
'"returns char(5)" & _
'"as begin return 'C' + right('0000' + convert(varchar(10), @id), 4)" & _
'"End"
    
    sSQL = "create function NextCustomerNumber()" & _
"returns char(5)" & _
"as begin declare @lastval char(5)" & _
"set @lastval = (select max(customerNumber) from Sheets(1) if @lastval is null set @lastval = 'C0001'" & _
"declare @i int" & _
"set @i = right(@lastval,4) + 1 return 'C' + right('000' + convert(varchar(10),@i),4)" & _
"End"

        adoCN.Execute sSQL
     
    Next lRow
     
    adoCN.Close
     
    Set adoCN = Nothing
     
     
    'On Error GoTo 0
End Sub

sSQL = "create function CustomerNumber (@id int)" & _
'"returns char(5)" & _
'"as begin return 'C' + right('0000' + convert(varchar(10), @id), 4)" & _
'"End"
    
    sSQL = "create function NextCustomerNumber()" & _
"returns char(5)" & _
"as begin declare @lastval char(5)" & _
"set @lastval = (select max(customerNumber) from Sheets(1) if @lastval is null set @lastval = 'C0001'" & _
"declare @i int" & _
"set @i = right(@lastval,4) + 1 return 'C' + right('000' + convert(varchar(10),@i),4)" & _
"End"
 
        adoCN.Execute sSQL

这两个函数我试图在VBA中增加文本+值，所以它在数据库中增加了它但不起作用。两者都是相同的类型。

these are 2 functions im trying to increment text + value in VBA so tht it shuld increment in DB but its not working. Both are same type.

推荐答案

看看在类似的问题和答案：将现有的表值复制到新的在excel`中使用vba宏的表 [ ^ ]



我希望这能帮助你理解它是如何工作的;）

Have a look at similar question and answer: copying existing table values to new table using vba macro in excel`[^]

I hope that would help you to understand how it works ;)

这篇关于增加值和文本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！