programatically move to the next column

Hello All...
I am writing to an .xls via an ActiveX script in a DTS.
I need to programatically move to the next column when
the data dictates.
After checking params, I'd like to simply say...
If I am currently in column B, I need to make the
CurCol (Current Column) = C
Is there anything that works like
CurCol = Select 'b' + 1
Thanks in advance,
bob mcclellanNot sure if I follow your requirements, but using column names directly is
the most reliable approach. If you want the "column number" for some reason,
you'd have to query the metadata, for instance like:
DECLARE @.index INT
SET @.index =1
SELECT COL_NAME( OBJECT_ID( QUOTENAME( '<tbl>' ) ), @.index )
Anith|||Hello Anith.
I simply want to increment alphabetically.
I know that I can create a table with all the columns of
a spreadsheet and then increment through the table
but I was wondering if there was a way to simply say
what comes after B and then what comes after C...
the same way that you can say
declare @.i int
Select @.i = 1
Select @.i = @.i +1
I would like to say
declare @.c char(2)
Select @.c = 'A'
Select @.c = @.c + 1
I just want to gain control over what column in a spreadsheet I
am writing to.
like... .range(@.c + "1) = myValue
Thanks in advance,
bob mcclellan
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:eYudD1KCFHA.3236@.TK2MSFTNGP15.phx.gbl...
> Not sure if I follow your requirements, but using column names directly is
> the most reliable approach. If you want the "column number" for some
> reason, you'd have to query the metadata, for instance like:
> DECLARE @.index INT
> SET @.index =1
> SELECT COL_NAME( OBJECT_ID( QUOTENAME( '<tbl>' ) ), @.index )
> --
> Anith
>|||One common approach is to use a staging table that corresponds to your
source data and then manipulate the data into your final table
structure using an INSERT...SELECT.
ActiveX does support referencing a column by position number in a
fields collection but I'm not sure that will help you very much. Maybe
if you explain the actual problem someone can help you with an
alternative. Why can't you reference columns by name?
--
David Portas
SQL Server MVP
--|||I misread your post. I though you were imporiting an Excel spreadsheet
rather than exporting it.
Is SQL Server the data source? If so, I expect it's possible to write a
SELECT statement that prepares the data in the form your spreadsheet
requires. If you want help, please post DDL for your tables, sample
data INSERTs and show your required end result.
--
David Portas
SQL Server MVP
--|||Hello David...
> ActiveX does support referencing a column by position number in a
> fields collection but I'm not sure that will help you very much.
--this is part of the code that I use to write the labels into Column A.
----
--
xlBook.Worksheets(1).Range("A1..iv500") = Null
xlBook.Save
'This puts the classes in Column A
with xlBook.Worksheets(1)
do while not rs.eof
.Range("A" &rs.fields("arc_id")) = rs.fields("Class")
rs.movenext
loop
end with
----
--
I've created a proc that builds the cross ref table I need...
To identify each Column increment so that I can dynamically
write the cell range to the column I need ...
I'm sorry David if this explanation is not Clear....
I can make it work, using the table created from the following proc...
Thanks in advance,
bob mcclellan
----
--
alter Proc AlphaIncrement_MakeTable
as
Set NoCount On
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'AlphaIncrement')
DROP TABLE AlphaIncrement
CREATE TABLE AlphaIncrement
(
Alpha_ID int
IDENTITY(1,1)
PRIMARY KEY CLUSTERED,
Letter char(2) Not Null
)
declare @.i int
Select @.i = 97
While @.i < 123
begin
Insert into AlphaIncrement (Letter)
SELECT CHAR(@.i)
Select @.i = @.i + 1
end
Select @.i = 97
While @.i < 123
begin
Insert into AlphaIncrement (Letter)
SELECT 'a'+CHAR(@.i)
Select @.i = @.i + 1
end
Select @.i = 97
While @.i < 123
begin
Insert into AlphaIncrement (Letter)
SELECT 'b'+CHAR(@.i)
Select @.i = @.i + 1
end
Select @.i = 97
While @.i < 123
begin
Insert into AlphaIncrement (Letter)
SELECT 'c'+CHAR(@.i)
Select @.i = @.i + 1
end
Select @.i = 97
While @.i < 123
begin
Insert into AlphaIncrement (Letter)
SELECT 'd'+CHAR(@.i)
Select @.i = @.i + 1
end
Select @.i = 97
While @.i < 123
begin
Insert into AlphaIncrement (Letter)
SELECT 'e'+CHAR(@.i)
Select @.i = @.i + 1
end
Select @.i = 97
While @.i < 123
begin
Insert into AlphaIncrement (Letter)
SELECT 'f'+CHAR(@.i)
Select @.i = @.i + 1
end
Select @.i = 97
While @.i < 123
begin
Insert into AlphaIncrement (Letter)
SELECT 'g'+CHAR(@.i)
Select @.i = @.i + 1
end
Select @.i = 97
While @.i < 123
begin
Insert into AlphaIncrement (Letter)
SELECT 'h'+CHAR(@.i)
Select @.i = @.i + 1
end
Select @.i = 97
While @.i < 119
begin
Insert into AlphaIncrement (Letter)
SELECT 'i'+CHAR(@.i)
Select @.i = @.i + 1
end
select * from AlphaIncrement order by alpha_ID
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1107354249.455002.35430@.l41g2000cwc.googlegroups.com...
> One common approach is to use a staging table that corresponds to your
> source data and then manipulate the data into your final table
> structure using an INSERT...SELECT.
> ActiveX does support referencing a column by position number in a
> fields collection but I'm not sure that will help you very much. Maybe
> if you explain the actual problem someone can help you with an
> alternative. Why can't you reference columns by name?
> --
> David Portas
> SQL Server MVP
> --
>|||Thanks David...
I really appreciate it.
I actually worked through this prior to reading your post.
Thanks again...
bob mcclellan
Following is the script I put together ....
'***************************************
*********************************
'D:\Common\Mailers\Batches\AvgRatesByCla
ss\Avg.Rates.By.Class.xls
'Populate Spreadsheet with Monthly Rates
'***************************************
*********************************
Function Main()
Dim xlApp
Dim xlBook
Dim i
Set xlApp = CreateObject("Excel.Application")
Set xlBook =
xlApp.Workbooks.Open(" D:\Common\Mailers\Batches\AvgRatesByClas
s\Avg.Rates.By
.Class.xls")
Dim sqlText
sqlText = "select arc_ID, Class from avgRatesClasses"
sqlColsText = "Select * from alphaIncrement"
dim con
dim rs
dim rsCols
set con = createobject("ADODB.Connection")
set rs = createobject("ADODB.recordset")
set rsCols = createobject("ADODB.recordset")
con.open = "Provider = SQLOLEDB.1; data source = (local); initial catalog
= MyWorkDB; user id = 'me'; password ='mypassword';"
rs.open sqltext, con
rsCols.open sqlColstext, con
xlBook.Worksheets(1).Range("A1..iv500") = Null
xlBook.Worksheets(1).Range("A1") = "CLASS"
xlBook.Save
rs.movenext
'This puts the classes in Column A
with xlBook.Worksheets(1)
do while not rs.eof
.Range("A" &rs.fields("arc_id")) = rs.fields("Class")
rs.movenext
loop
end with
sqlText = "Select c.arc_id, d.* from avgRatesClassesData d " _
& " inner join avgRatesClasses c on " _
& " d.class = c.class " _
& "order by yr desc, Mnth desc, d.class " _
rs.close
rs.open sqltext, con
'Move to Column B
rsCols.movenext
dim CurMnthDate
CurMnthDate = rs.fields("yrMnth")
dim CurCol
CurCol = rtrim(rsCols.fields("letter"))
with xlBook.Worksheets(1)
.Range(CurCol & 1) = rs.fields("yrMnth")
do while not rs.eof
if CurMnthDate <> rs.fields("yrMnth") then
rsCols.movenext
CurCol = rtrim(rsCols.fields("letter"))
.Range(CurCol & "1") = rs.fields("yrMnth")
CurMnthDate = rs.fields("yrMnth")
end if
.Range(CurCol &rs.fields("arc_id")) = rs.fields("avgRate")
rs.movenext
loop
end with
xlBook.Save
rs.close
set rs = nothing
rsCols.close
set rsCols = nothing
xlBook.Close
Set xlBook = Nothing
xlApp.Quit
Set xlApp = Nothing
Main = DTSTaskExecResult_Success
End Function
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1107354731.006660.43150@.g14g2000cwa.googlegroups.com...
>I misread your post. I though you were imporiting an Excel spreadsheet
> rather than exporting it.
> Is SQL Server the data source? If so, I expect it's possible to write a
> SELECT statement that prepares the data in the form your spreadsheet
> requires. If you want help, please post DDL for your tables, sample
> data INSERTs and show your required end result.
> --
> David Portas
> SQL Server MVP
> --
>