[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 183
  • Last Modified:

Send Excel2000 data from different tables

I need to send data from three seperate tables. One table determines if a person is on leave, if this is false I then need to see if the person is on a shift change, if this is false then I need to get the persons original shift.  If either one of the first answers is true I need it to populate a cell in Excel.  Basically I need only the true answer to be sent to Excel where it can then be printed out.
0
Missile
Asked:
Missile
  • 2
  • 2
  • 2
  • +1
1 Solution
 
eosuCommented:
1. Where are the tables stored? Database or document?
2. If database then what type of database?
0
 
MissileAuthor Commented:
Tables are stored in Access.
0
 
dhwanilshahCommented:
from where do u want to trigger the action that implements the logic?
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
eosuCommented:
So what is the problem?
You want up to 3 select statements and link to Excel's object model.

Which bit are you having trouble with...?
0
 
rondeaujCommented:
place a ref. to excel object lib. 8.0

put this code in a project


if your excel workpaper already exsists:

Dim Myexcel as Excel.WorkSheet
Set Myexcel = getobject(filename)

if not:
Set Myexcel = createobject(Excel.worksheet.8)



0
 
dhwanilshahCommented:
On Error GoTo ErrorH

Dim xlapp As Excel.Application
Dim datasheet As Excel.Worksheet
Dim mRng As Excel.Range

Dim curdb As ADODB.Connection
Dim rsCases As New ADODB.Recordset

Dim mRow As Long

Set xlapp = GetObject(, "Excel.Application")
If xlapp Is Nothing Then
    Set xlapp = New Excel.Application
End If

xlapp.Visible = True

xlapp.Workbooks.Open ("C:\WINDOWS\Profiles\Shreyas\Desktop\Case Register\dees_Mact_Cases.xls")

Set datasheet = xlapp.ActiveWorkbook.Sheets("data sheet")

Set curdb = CurrentProject.Connection

rsCases.Open "cases", curdb, adOpenDynamic, adLockPessimistic

mRow = 2

Do While mRow <> 319

    If mRow = 153 Then
        Stop
    End If
   
    rsCases.AddNew
   
    rsCases(0) = datasheet.Range("a" & mRow)
    rsCases(1) = datasheet.Range("b" & mRow)
    rsCases(2) = datasheet.Range("c" & mRow)
    rsCases(3) = datasheet.Range("d" & mRow)
    rsCases(4) = datasheet.Range("e" & mRow)
    rsCases(5) = datasheet.Range("f" & mRow)
    rsCases(6) = datasheet.Range("g" & mRow)
    rsCases(7) = datasheet.Range("h" & mRow)
    rsCases(8) = datasheet.Range("i" & mRow)
    rsCases(9) = datasheet.Range("j" & mRow)
    rsCases(10) = datasheet.Range("k" & mRow)
    rsCases(11) = datasheet.Range("l" & mRow)
    rsCases(12) = datasheet.Range("m" & mRow)
    rsCases(13) = datasheet.Range("n" & mRow)
    rsCases(14) = datasheet.Range("o" & mRow)
    rsCases(15) = datasheet.Range("p" & mRow)
    rsCases(16) = datasheet.Range("q" & mRow)
    rsCases(17) = datasheet.Range("r" & mRow)
    rsCases(18) = datasheet.Range("s" & mRow)
    rsCases(19) = datasheet.Range("t" & mRow)
    rsCases(20) = datasheet.Range("u" & mRow)
    rsCases(21) = datasheet.Range("v" & mRow)
    rsCases(22) = datasheet.Range("w" & mRow)
    rsCases(23) = datasheet.Range("x" & mRow)
    rsCases(24) = datasheet.Range("y" & mRow)
    rsCases(25) = datasheet.Range("z" & mRow)
    rsCases(26) = datasheet.Range("aa" & mRow)
    rsCases(27) = datasheet.Range("ab" & mRow)
    rsCases(28) = datasheet.Range("ac" & mRow)
    rsCases(29) = datasheet.Range("ad" & mRow)
    rsCases(30) = datasheet.Range("ae" & mRow)
    rsCases(31) = datasheet.Range("af" & mRow)
   
    rsCases.Update
   
    Debug.Print mRow
   
    mRow = mRow + 1
   
Loop

Exit Sub

ErrorH:
    If Err.Number = 429 Then
        Resume Next
    Else
        MsgBox Err.Description, vbInformation, "Error - " & Err.Number
        Stop
        Resume
    End If

'here is an generic procedure for one time retrival of data from an excel sheet into an access table. I am not sure if this would help in your case or not perfectly but partially it would address your problem. And oh, dont forget to add an ref. to the excel library first
0
 
MissileAuthor Commented:
The program I'm writing is a scheduling program for personnel. It uses VB as the front but stores data in Access to 3 tables. When the program starts it gets the data from the 3 tables using multiple queries.  This populates a daily crewsheet which consists of 7 crews with 10 individuals working a 24 hour period. I have already designed a sign in sheet in excel, now I need to populate this sheet with daily data from the different tables as well as additional data that the daily crewsheet did'nt get from access and then print out and shut down excel.
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 2
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now