Pls Help !!! Assigning values to Excel cells from access database (1 Viewer)

jasmeetsingh89

New member
Local time
Yesterday, 17:12
Joined
Aug 2, 2012
Messages
8
Hi,

I am a newbie ... please help!

On a button click i am trying to open an excel sheet and assign values of all the text boxes in the form to specific cells in the excel sheets.

Now, the above part that i mentioned, I somewhat have an idea of how to do it. But, the tricky part is that I have multiple records for the same primary key and after assigning the first record associated to the primary key to the excel cells, i want to be able to check if there are other records for the same key and if yes, i want to also assign them to their corresponding excel cells.

Does anyone have an idea of how i will be able to do this ... please help .. it is urgent ... :(
 

dbay

Registered User.
Local time
Yesterday, 19:12
Joined
Jul 15, 2007
Messages
87
This is what I use to write to Excel files. I yanked this out of the middle of a bunch of other code and tried to clean it up to make it understandable. So it is untested, but it looks like everything is there. Make sure you have the Microsoft Excel 14.0 Object Library selected under Tools\References.

Code:
     Dim OBJ_XL As Excel.Application
     Dim OBJ_WKB As Excel.Workbook
     Dim OBJ_SHT As Excel.Worksheet
 
     STR_Path = "C:\FINDING_TEMPLATE_2010.xlsx"
     STR_Protect = "Test"
 
     Set OBJ_XL = New Excel.Application
     With OBJ_XL
          .Visible = False
          Set OBJ_WKB = .Workbooks.Open(STR_Path )
          Set OBJ_SHT = OBJ_WKB.Worksheets("Sheet1")
          With OBJ_SHT
                .Unprotect (STR_Protect)
                .Range("B6").Value = Me.TXT_Something_1.Value
                .Range("C6").Value = Me.TXT_Something_2.Value
                .Range("C3").Value = Me.TXT_Something_3.Value
                .Range("F3").Value = Me.TXT_Something_4.Value
                .Range("I3").Value = Me.TXT_Something_5.Value
 
                 'False Link to File
                 'True Save with Document
                 'Left 100
                 'Top 100
                 'Width 70
                 'Height 70
 
                 'For adding pictures(STR_Picture_1-_2 are File Paths)
                 .Shapes.AddPicture STR_Picture_1, False, True, 5, 350, 228, 229
                 .Shapes.AddPicture STR_Picture_2, False, True, 238, 350, 228, 229
                 .Protect (STR_Protect)
          End With
     End With
     OBJ_WKB.Close True
     OBJ_XL.Quit
 
     Set OBJ_SHT = Nothing
     Set OBJ_WKB = Nothing
     Set OBJ_XL = Nothing
 
Last edited:

jasmeetsingh89

New member
Local time
Yesterday, 17:12
Joined
Aug 2, 2012
Messages
8
Hi, Thank You for the reply.

This is correct code and even i did this in case of a single record ...
But i have a key associated wid multiple records .. so essentially, i have to check i there are more records for tht key push them in the excell too ...
 

dbay

Registered User.
Local time
Yesterday, 19:12
Joined
Jul 15, 2007
Messages
87
Hi, Thank You for the reply.

This is correct code and even i did this in case of a single record ...
But i have a key associated wid multiple records .. so essentially, i have to check i there are more records for tht key push them in the excell too ...

Maybe?

Code:
     Dim DB As DAO.Database
     Dim RS As DAO.Recordset
     Dim i As Integer
 
     STR_SQL = "SELECT FIN_IMG_Name_1 FROM TBL_Findings WHERE FIN_Finding_Index = " & Me.TXT_Finding_Index.Value & ";"
 
     Set DB = CurrentDb()
     Set RS = DB.OpenRecordset(STR_SQL, dbOpenSnapshot)
     RS.MoveFirst
     i = 5
     Do While Not RS.EOF
          OBJ_SHT.Range("C" & i).Value = RS![FIN_IMG_Name_1]
          i = i + 1
          RS.MoveNext
     Loop
 
     RS.Close
     DB.Close
 
     Set RS = Nothing
     Set DB = Nothing
 

jasmeetsingh89

New member
Local time
Yesterday, 17:12
Joined
Aug 2, 2012
Messages
8
I have a similar code that i am using .... But i on the button click i get an error saying "a problem occurred while microsoft access was communicating with the ole server or activex control"

Dim rsRec As DAO.Recordset
Dim rsRecUnfiltered As DAO.Recordset
Dim recCount As Long
Dim i As Long

Set rsRecUnfiltered = CurrentDb.OpenRecordset(Me.RecordSource, dbOpenDynaset, dbReadOnly)
rsRecUnfiltered.Filter = "[mskcc_ID] = " & Chr(34) & InputBox("Please provide the MSKCC ID of the Student:") & Chr(34)
Set rsRec = rsRecUnfiltered.OpenRecordset
If Not rsRec.EOF Then
rsRec.MoveLast
rsRec.MoveFirst
recCount = rsRec.RecordCount
Else
MsgBox "There are no records of payment by this student."
Exit Sub
End If

Dim objXL As Object
Dim objXLBook As Object
Dim objXLSheet As Object
Dim objRange As Object


Set objXL = CreateObject("Excel.Application")
Set objXLBook = objXL.Workbooks.Open("E:\BTR.xlsx")

objXLBook.Application.Visible = True

'Set objXLSheet = objXLBook.Worksheets(1)

objXLSheet.Cells.Range("K8") = rsRec![mskcc_ID]

For i = 0 To (recCount - 1)
objXLSheet.Cells.Range("A" & (14 + i)) = rsRec![_Date_]
objXLSheet.Cells.Range("B" & (14 + i)) = rsRec![Semester]
objXLSheet.Cells.Range("c" & (14 + i)) = rsRec![Description]
objXLSheet.Cells.Range("E" & (14 + i)) = rsRec![Check#]
objXLSheet.Cells.Range("F" & (14 + i)) = rsRec![Payment_Type]
objXLSheet.Cells.Range("G" & (14 + i)) = rsRec![Financial_Aid]
objXLSheet.Cells.Range("H" & (14 + i)) = rsRec![Tuitio_Payment]
objXLSheet.Cells.Range("J" & (14 + i)) = rsRec![Disbursement]
objXLSheet.Cells.Range("K" & (14 + i)) = rsRec![Tuition_Due]
rstBooks.MoveNext
Next i

Set objXL = Nothing
Set objXLBook = Nothing
Set objXLSheet = Nothing
Set objRange = Nothing
Set rstBooks = Nothing
 

dbay

Registered User.
Local time
Yesterday, 19:12
Joined
Jul 15, 2007
Messages
87
Ok, found some things. Try this and let me know.

Code:
    Dim DB As DAO.Database
    Dim RS As DAO.Recordset
    Dim RS_Filtered As DAO.Recordset
    Dim objXL As Object
    Dim objXLBook As Object
    Dim objXLSheet As Object
    Dim objRange As Object
 
    Dim STR_SQL As String
 
    Dim recCount As Long
    Dim i As Long
    Dim j As Integer
 
    STR_SQL = Me.RecordSource.Value
 
    Set RS_Filtered = DB.OpenRecordset(STR_SQL, dbOpenDynaset, dbReadOnly)
    RS_Filtered.Filter = "[mskcc_ID] = " & Chr(34) & InputBox("Please provide the MSKCC ID of the Student:") & Chr(34)
    Set RS = RS_Filtered.OpenRecordset
 
    If Not RS.EOF Then
        RS.MoveLast
        RS.MoveFirst
        recCount = RS.RecordCount
    Else
        MsgBox "There are no records of payment by this student."
        Exit Sub
    End If
 
    Set objXL = CreateObject("Excel.Application")
    Set objXLBook = objXL.Workbooks.Open("E:\BTR.xlsx")
    objXLBook.Application.Visible = True
 
    'Make sure your put the name of the sheet you want to write to in the quotes.
    Set objXLSheet = objXLBook.Worksheets("Sheet1")
    objXLSheet.Cells.Range("K8") = RS![mskcc_ID]
 
    For i = 0 To (recCount - 1)
        objXLSheet.Cells.Range("A" & (14 + i)) = RS![_Date_]
        objXLSheet.Cells.Range("B" & (14 + i)) = RS![Semester]
        objXLSheet.Cells.Range("c" & (14 + i)) = RS![Description]
        objXLSheet.Cells.Range("E" & (14 + i)) = RS![Check#]
        objXLSheet.Cells.Range("F" & (14 + i)) = RS![Payment_Type]
        objXLSheet.Cells.Range("G" & (14 + i)) = RS![Financial_Aid]
        objXLSheet.Cells.Range("H" & (14 + i)) = RS![Tuitio_Payment]
        objXLSheet.Cells.Range("J" & (14 + i)) = RS![Disbursement]
        objXLSheet.Cells.Range("K" & (14 + i)) = RS![Tuition_Due]
        'Here you had rstBooks instead of RS.MoveNext
        RS.MoveNext
    Next i
 
    'Need to Close
    RS_Filtered .close
    RS.Close
    DB.Close
    
    Set RS_Filtered  = Nothing
    Set RS = Nothing
    Set DB = Nothing
    Set objXL = Nothing
    Set objXLBook = Nothing
    Set objXLSheet = Nothing
    Set objRange = Nothing
 
Last edited:

dbay

Registered User.
Local time
Yesterday, 19:12
Joined
Jul 15, 2007
Messages
87
Sorry, found some more things.

Code:
    Dim DB As DAO.Database
    Dim RS As DAO.Recordset
    Dim RS_Filtered As DAO.Recordset
    Dim OBJ_XL As Excel.Application
    Dim OBJ_WKB As Excel.Workbook
    Dim OBJ_SHT As Excel.Worksheet
 
    Dim STR_SQL As String
    Dim recCount As Long
    Dim i As Long
    STR_SQL = Me.RecordSource.Value
    Set RS_Filtered = DB.OpenRecordset(STR_SQL, , dbOpenDynaset, dbReadOnly)
    RS_Filtered.Filter = "[mskcc_ID] = " & Chr(34) & InputBox("Please provide the MSKCC ID of the Student:") & Chr(34)
    Set RS = RS_Filtered.OpenRecordset
 
    If Not RS.EOF Then
        RS.MoveLast
        RS.MoveFirst
        recCount = RS.RecordCount
    Else
        MsgBox "There are no records of payment by this student."
        Exit Sub
    End If
 
    Set OBJ_XL = CreateObject("Excel.Application")
    Set OBJ_WKB = OBJ_XL.Workbooks.Open("E:\BTR.xlsx")
    OBJ_WKB.Application.Visible = True
 
    'Make sure your put the name of the sheet you want to write to in the quotes.
    Set OBJ_SHT = OBJ_WKB.Worksheets("Sheet1")
    OBJ_SHT.Cells.Range("K8") = RS![mskcc_ID]
 
    For i = 0 To (recCount - 1)
        OBJ_SHT.Cells.Range("A" & (14 + i)) = RS![_Date_]
        OBJ_SHT.Cells.Range("B" & (14 + i)) = RS![Semester]
        OBJ_SHT.Cells.Range("c" & (14 + i)) = RS![Description]
        OBJ_SHT.Cells.Range("E" & (14 + i)) = RS![Check#]
        OBJ_SHT.Cells.Range("F" & (14 + i)) = RS![Payment_Type]
        OBJ_SHT.Cells.Range("G" & (14 + i)) = RS![Financial_Aid]
        OBJ_SHT.Cells.Range("H" & (14 + i)) = RS![Tuitio_Payment]
        OBJ_SHT.Cells.Range("J" & (14 + i)) = RS![Disbursement]
        OBJ_SHT.Cells.Range("K" & (14 + i)) = RS![Tuition_Due]
        'Here you had rstBooks instead of RS.MoveNext
        RS.MoveNext
    Next i
 
    'Need to Close
    RS.Close
    DB.Close
 
    Set RS = Nothing
    Set DB = Nothing
    Set OBJ_XL = Nothing
    Set OBJ_WKB = Nothing
    Set OBJ_SHT = Nothing
 

jasmeetsingh89

New member
Local time
Yesterday, 17:12
Joined
Aug 2, 2012
Messages
8
thanks a lot for helping me ... the code that i originally wrote .. tht works ... it was some access glitch .. i changed the form and did everythg frm the start ... now it works ...

can u tell me how should i take the value from combo_sid.Value instead of the inputbox ?
 

Users who are viewing this thread

Top Bottom