Results 1 to 12 of 12

Thread: Question on Excel with VB 6.

  1. #1

    Thread Starter
    Junior Member
    Join Date
    Jul 2013
    Posts
    25

    Question on Excel with VB 6.

    Hello All,

    I am here looking for some information on Excel integration with VB6.

    Actually the question is quite generic and I would need you assistance in starting the coding.

    Now, the scenario is that I have 2 tables in a database. 1. Users 2. LoginDetails.

    The Users table contains the userid, passwd, empid and EmpName fields.
    The LoginDetails table contains login, logout, shift(calculated automatically as 1,2 or 3 according to the login time), overtime, empid, EmpName fields.

    I am not using any primary secondary key relationship. Here is what I intend to do.

    I have an excel sheet in which I would like to print the names of the employees from the users table and their corresponding shift and overtime details from the LoginDetails table.


    Below is an example as to how it should look.

    1/1/2014 1/2/2014 1/3/2014
    EmpID EmpName Shift Ovt Shift Ovt Shift Ovt
    1 xyz 1 yes 1 yes 2 no
    2 abc 2 no 2 no 1 yes

    I am able to print the employee details from the Users table but I am getting stuck with the LoginDetails table.

    Any help on this would be greatly appreciated.

    Thank you in advance.

  2. #2
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,083

    Re: Question on Excel with VB 6.

    probably best to do a join query on the database tables, then can easily insert the required information into excel workbook
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  3. #3

    Thread Starter
    Junior Member
    Join Date
    Jul 2013
    Posts
    25

    Re: Question on Excel with VB 6.

    Any other ideas guys?

  4. #4

    Thread Starter
    Junior Member
    Join Date
    Jul 2013
    Posts
    25

    Re: Question on Excel with VB 6.

    Thanks westconn1.

    I will surely try it.

  5. #5
    Frenzied Member
    Join Date
    Nov 2010
    Posts
    1,464

    Re: Question on Excel with VB 6.

    data does not belong in excel

    data belongs in access

    vb in office is vba not vb6

    apart from that

    if you can get stuff from 1 table what is your problem exactly with the other?

    here to help

  6. #6

    Thread Starter
    Junior Member
    Join Date
    Jul 2013
    Posts
    25

    Re: Question on Excel with VB 6.

    Hello Incidentals,

    Thank you for the response.

    I have a constraint that I have to use only Excel for this reporting.

    Now, let me try to explain the issue I am facing.

    I have 2 tables table1 and table2 in my db and I would like to generate a few reports from them.

    The fields of interest are:
    table1: Name, Manager, Team
    table2: Name, Date, Shift, Overtime

    1. From table1, I want to extract the data from the 3 fields mentioned above and populate the first 3 columns of the excel report. - I am able to do this.
    2. From table2, I want to extract the corresponding data for each Name already in the excel column and would like to print their shift and overtime details for all the days in the month. If there are no records for a particular day of the month, I would like a "-" in the shift field.

    I am unable to figure out a way to do the step 2 above.

    Please help me out with this.

  7. #7
    Frenzied Member
    Join Date
    Nov 2010
    Posts
    1,464

    Re: Question on Excel with VB 6.

    aargh...

    just finished a hospital doctors rota thing like this huge and really complex...

    i will have a look at this tomorrow for you and get back with some suggestions

    its late here and the boss has had too many night interupted by me typing so i am on my last warning!

    but at first glance...

    i would sort the rows by the name columns of both tables

    and in the second table consider sorting by name and then date

    you now have lumps of name data in date order... much easier to manipulate

    you can walk down the table and add in a person who has no data for the period

    you can walk down the table and add any missing dates filling them with "-" as required

    you can then use the transform function to rotate the data

    giving you a name deep date long table of shift and overtime

    just a thought for now

    here to help

    night night

  8. #8

    Thread Starter
    Junior Member
    Join Date
    Jul 2013
    Posts
    25

    Re: Question on Excel with VB 6.

    Hello Incidentals,

    Thank you for the reply.

    I was able to follow your recommendation and start writing the code.

    But, the code I have written works only on consistent data. i.e only if the user has clocked logins for all days of the month.

    I am unable to get the logic to put a "-" to the date where the user has no login records.

    I have copied my code below and also attached a screenshot of the excel sheet report.

    Please let me know if there is anything else I can try by modifying my code.


    ===================
    Code
    ===================
    rs.Open "Select * from Users where ManagerName='" & empname & "'", cn, adOpenKeyset, adLockOptimistic
    xl.Workbooks.Open "C:\Shift Allowance.xlsx"
    xl.Workbooks.Application.EnableEvents = False
    Do While Not rs.EOF
    smonthName = cmbMonth.Text
    imonthno = Month(CDate("01, " & smonthName & " 2013"))
    xl.ActiveWorkbook.Sheets(2).Cells(3, 4) = imonthno
    xl.ActiveWorkbook.Sheets(2).Cells(2, 2) = "CSS"
    xl.ActiveWorkbook.Sheets(2).Cells(2, 4) = Year(Now)
    For i = 0 To rs.RecordCount - 1
    xl.ActiveWorkbook.Sheets(2).Cells(9 + i, 2) = rs.Fields(0) - Name
    xl.ActiveWorkbook.Sheets(2).Cells(9 + i, 3) = rs.Fields(4) - EmpID
    xl.ActiveWorkbook.Sheets(2).Cells(9 + i, 4) = rs.Fields(6) - ManagerName
    rs1.Open "Select * from LoginRecords where EmpID='" & xl.ActiveWorkbook.Sheets(2).Cells(9 + i, 2) & "' and MonthNo='" & imonthno & "' order by SDate asc", cn, adOpenKeyset, adLockOptimistic
    If rs1.EOF = True Then
    MsgBox "No login records found for the selected month. Please choose a different month.", vbCritical + vbOKOnly
    xl.ActiveWorkbook.Saved = True
    xl.ActiveWorkbook.Close
    xl.Workbooks.Application.EnableEvents = True
    xl.Workbooks.Close
    xl.Quit
    Set rs1 = Nothing
    Exit Sub
    Else
    For J = 0 To 61 Step 2
    If rs1.EOF = True Then
    xl.ActiveWorkbook.Sheets(2).Cells(9 + i, 5 + J) = "-"
    Else
    xl.ActiveWorkbook.Sheets(2).Cells(9 + i, 5 + J) = rs1.Fields(5) - Shift
    xl.ActiveWorkbook.Sheets(2).Cells(9 + i, 6 + J) = rs1.Fields(15) - Overtime
    rs1.MoveNext
    End If
    Next J
    Set rs1 = Nothing
    If rs.EOF = True Then
    GoTo jump
    Else
    rs.MoveNext
    End If
    Next i
    Loop
    Set rs = Nothing
    jump:
    xl.ActiveWorkbook.SaveAs ("C:\" & empname & " Shift Allowance " & cmbMonth.Text & " " & Year(Now) & ".xlsx")
    xl.Workbooks.Application.EnableEvents = True
    xl.ActiveWorkbook.Close
    xl.Workbooks.Close
    xl.Quit
    MsgBox "Report Successfully generated for " & cmbMonth.Text & ". File Path is: C:\" & empname & " Shift Allowance " & cmbMonth.Text & " " & Year(Now) & ".xlsx", vbOKOnly + vbInformation
    ===================
    End Of Code
    ===================

  9. #9

    Thread Starter
    Junior Member
    Join Date
    Jul 2013
    Posts
    25

    Re: Question on Excel with VB 6.

    Scrrenshot...Name:  ScreenShot032.bmp
Views: 99
Size:  471.1 KB

  10. #10

    Thread Starter
    Junior Member
    Join Date
    Jul 2013
    Posts
    25

    Re: Question on Excel with VB 6.

    Hi,

    Could someone please help me with this request.

    Thanks in advance.

  11. #11
    PowerPoster
    Join Date
    Jun 2013
    Posts
    5,019

    Re: Question on Excel with VB 6.

    In #6 it becomes obvious, that this is not an XL-related or "programming-problem" - but a DB (or SQL)-problem.

    What you need, is to google or read about the SQL-Join-syntax - as westconn1 already suggested...
    Here's something "visual": http://www.codeproject.com/Articles/...n-of-SQL-Joins

    The above tries to explain, how the Join-syntax treats "non-existing" records in the different
    tables (or sub-queries) which are about to be joined - also useful would be, when you brush up
    your SQL about the handling of Null-Values (for the DB-Engine you are using - and what is used
    there with regards to NULL-checks ... IfNull, IsNull or Is Null syntax etc.).

    Try to come up with the right SQL first, which hands out the correct SET you want - in one single query -
    and that can be done without any XL- or VBA-coding - just use a query-designer and re-evaluate your
    SQL, until you're satisfied with the results.

    After that it should be easy to copy the correct Rs-values into the correct positions on a given XL-Sheet.

    Olaf

  12. #12
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,083

    Re: Question on Excel with VB 6.

    Could someone please help me with this request.
    have you even searched on using sql queries on excel workbooks?
    http://support.microsoft.com/kb/257819
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

Posting Permissions

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



Click Here to Expand Forum to Full Width