Need assist with sheet naming and probably other issues as well

[SamBorn]

New to SQL but here is what I have:

Current tickets are in a excel sheet (Sheet3 or Current Cases depending on how you refer to it in excel objects) it is based on columns with the ticket number as the header

What I want to do:

1. When the sheet is updated compare columns to another sheet (Sheet5 or Archive depending on how you refer to it in excel objects)

2. Update any existing columns in Sheet5/Archive with the new information from Sheet3/Current Cases

3. Insert any new columns from Sheet3/Current Cases
into Sheet5/Archive

4. Not lose old columns in the archive sheet if they do not exist on the current case sheet.

I am using the following:

SELECT S1.A, Iif(IsNull(S2.A),S1.B,S2.B)
FROM [Sheet3$] as S1
LEFT OUTER JOIN [Sheet5$] as S2 ON S1.A = S2.A

and get Sheet3$ is not a valid name.

Can I get some guidance here.

T.Hanks

-Sam

[SamT]

You are using "Sheet3$" as the name of a Database Table. Are you trying to retrieve data from a Database, or from a Worksheet?



Using your various sheet "names"
Sheets Are Objects
Sheet3 and Sheet5 are the Object Names, referred to as Code Names. Object Names are implicitly declared Variables.

''By Excel
Dim Sheet1 as Object
Set Sheet1 = New Excel.WorkSheet
Sheet1.Name = "Sheet1"

"Current Cases" and "Archive" are the Sheet Names, or Tab Names as we refer to them to avoid semantic confusion. Tab names are Strings.

In Code, you have to refer to them differently

Sheets is a Collection Object, in this case a collection of all the Sheets in a Workbook.
To refer to Worksheets in the Collection, you use the Tab name

Sheets("Current Cases). Range("A2")

To refer to a Sheet Object, use the Code Name Object

Sheet3.Range("A2")

Attachment 15400

Note that Charts are (sheet) Objects too and are included in the Sheets Collection. They are referred to as Chart Objects and have Code Names and Tab Names. We call them Chart Sheets to avoid semantic confusion. ChartObject Objects are pretty colored graphics on a Chart Sheet and are referred to as Charts. Don't blame me, it was Bill Gates' idea.

Last bit of technotrivia: There are three relevant Collections: Sheets, Worksheets and Charts. They contain, respectively, WorkSheets and Charts, WorkSheets, and Charts. (No, not the pretty colored graphics)

[SamT]

I merged the Excel thread with this one and deleted duplicate and irrelevant posts. You could have left a note in the Excel forum telling about this thread. OR even... Asked a moderator to move the thread for you.

Well, now it's in the SQL forum, so be patient, this forum is not as well frequented as the Excel forum, but somebody will get here. Sooner or later.

OTOH, I believe that my previous post, which was made in the Excel Forum, has answered your question.