Ultimate Casino List Cleanser

Question

Post reply

Ultimate Casino List Cleanser

wbachman

Valued Member

Points: 61
More actions
November 2, 2007 at 11:40 am

#199622

Hey guys heres where Im at I basically need to create a macro that will automatically cleanse a list for me and check if the address are good or not. I have my SQL code all set and runs with an F5 but i need help with the VB side inside excel to import data from the sheet, run the cleaner and export the good address and also export the ones that were cleaned and the reason why they were. Like I said the code does that in SQl with no problem i just need help from the macro side in excel. Thoughts? Also the best answer will recieve a free buffet if they are in vegas. 🙂
December 1, 2020 at 11:45 pm

This was removed by the editor as SPAM
June 30, 2022 at 1:24 pm

This was removed by the editor as SPAM
August 30, 2022 at 2:25 pm

This was removed by the editor as SPAM
August 31, 2022 at 10:36 pm

This was removed by the editor as SPAM
September 1, 2022 at 7:58 am

This was removed by the editor as SPAM
September 5, 2022 at 2:06 am

This was removed by the editor as SPAM
June 24, 2023 at 12:03 pm

This was removed by the editor as SPAM

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323504 More actions · Answer 1

results into excel, and getting excel to run a stored procedure or query text is pretty easy.

i made this in an excel worksheet for Excel 2002.

to recreate, simply open excel, and do Tools>>Macro>>New Macro

give it the name RunProcedureFromExcel

copy and paste the code below.

change the connection string information to be the correct server and correct database

test that it works...it just gets 5 rows from sysobjects.

change the sql to be the code or "exec someprocname" for a stored procedure call.

then test that THAT works.

Sub RunProcedureFromExcel()

Dim Conn As Object

Dim Rs As Object

Dim cnString As String

Dim sql As String

Dim i As Integer

Dim j As Integer

sql = " select top 5 * from sysobjects"

Set Conn = CreateObject("ADODB.Connection")

cnString = "Provider=SQLOLEDB;Persist Security Info=False;User ID=sa;Password=;Initial Catalog=YOURDBNAME;Network Library=dbnetlib;Data Source=db1;"

Conn.ConnectionString = cnString

Conn.CommandTimeout = 300

Conn.Open

Set Rs = CreateObject("ADODB.Recordset")

Set Rs = Conn.Execute(sql)

'build the headers

For i = 0 To Rs.Fields.Count - 1

Sheet1.Cells(1, i + 1) = Rs.Fields(i).Name

Next i

j = 2 'second row, first is reserved for column names

Do While Not Rs.EOF

For i = 0 To Rs.Fields.Count - 1

Sheet1.Cells(j, i + 1) = Rs.Fields(i).Value

Next i

j = j + 1

Rs.MoveNext

Loop

I cna send you an actual excel document with teh macro embeded in it, but I would typically not use Excel for my db interface..i prefer an executable specific for the task, or to run it in QA like you are currently doing.

I'll be in Las Vegas for Thanksgiving if the promise for free food still stands 🙂

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Bill Bachman Newbie Points: 5 More actions · Answer 2

ok couple quick questions can you highlight the things i need to change because it says that it cant activex can't create object and Set Conn = CreateObject("adobe.Connection")

Sorry for being really lame about this ive never done vb scripting i onlyt know sql. Thanks again

Lowell SSC Guru Points: 323504 More actions · Answer 3

it's gotta be a translational error instead of a copy/paste error; it's not adobe, like adobe reader.

it should be

Set Conn = CreateObject("ADODB.Connection")

ADODB is the standard way I've always connected to various data resources.ActiveX Data Objects Data Base is the collection of objects Microsoft created to make data access standardized and easier to use.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Bill Bachman Newbie Points: 5 More actions · Answer 4

I think it was a reference error I dont believe I had the correct reference checked to run the ADODB connection.

Lowell SSC Guru Points: 323504 More actions · Answer 5

ADODB is part of the microsoft Data Access Components (MDAC)

here's more info and the download:

Download details: MDAC 2.8Microsoft Data Access Components (MDAC) 2.8 contains core Data Access components such as the Microsoft SQL Server™ OLE DB provider and ODBC driver.

www.microsoft.com/downloads/details.aspx?FamilyID=6c050fe3-c795-4b7d-b037-185d0506396c

it's installed by default in every windows operating system i believe, but you can follow that link and download/installt he latest version.

I assumed it was preinstalled, so i figured it must have ben a transcription error. I did test the example before posting, and it worked fine. I think by add thae MDAC you should be able to test.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!