Consulting

Results 1 to 3 of 3

Thread: Need assist with sheet naming and probably other issues as well

  1. #1
    VBAX Newbie
    Joined
    Feb 2016
    Posts
    1
    Location

    Need assist with sheet naming and probably other issues as well

    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

  2. #2
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,116
    Location
    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")
    CodeName.jpg

    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)
    Last edited by SamT; 02-12-2016 at 02:38 PM.
    I always expect the student to do their homework and find all the errrors I leeve in.

    Please take the time to read the Forum FAQ

  3. #3
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,116
    Location
    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.
    I always expect the student to do their homework and find all the errrors I leeve in.

    Please take the time to read the Forum FAQ

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •