Trying to pull data from Access to Excel

[ffejeroni]

I have a form that we call a "check request form" in Excel. Our customer database is in Access. There are certain feilds in the Excel book that I need to automatically pull from our database. I tried building an Access query in Excel, and it worked, but not exactly how I needed it to. I need to run the query based on a date the user enters, then have an Excel sheet populate for each individual result. I need to pull a name, cash amount, address, city, state, zip, etc. and have it populate certain feilds in the Excel file. I am probably not explaining this well, but if anyone has any ideas, I would GREATLY appreciate it. Happy holidays, and thanks in advance!

Jeff

 

[oliver_penney]

hi jeff

i'd do this using SQL

build a query in Access of an example query and copy the SQL into a reply here

point out the things that are going to vary in the code...

 

[ffejeroni]

SELECT tblDFLModified.[Check Request Date], tblDFLModified.RequestNbr, tblDFLModified.Payee, tblDFLModified.[Mailing Address], tblDFLModified.City, tblDFLModified.State, tblDFLModified.[Zip Code], tblDFLModified.Amount, tblDFLModified.PMC, tblDFLModified.Ref_Type, tblDFLModified.FileNbr, tblDFLModified.[Buy/Sell Price], tblDFLModified.Program
FROM tblDFLModified
WHERE (((tblDFLModified.[Check Request Date])=[enter date]));


This is exactly what I need. It's not big, thank God, but what I need is for the results to populate certain feilds in an Excel sheet, and have a seperate Excel sheet for each record set. Thanks so much for your time and help!

Jeff

 

[ffejeroni]

This is what I have come up woth so far. Will this work?

Option Compare Database
Option Explicit

Dim Cdb As Database
Dim PathStr As String
Dim TempPath As String
Dim LogoPath As String

'****************************************************************************************************************************************
Public Sub Embed_Check_Request_InExcel(RequestNbr As String, Optional LeadNbr As String)

Dim objXLSheet As Excel.Workbook
Dim Rs As Recordset
Dim sqlStr As String
Dim PMCstr As String
Dim txtProgram As String
Dim FilePath As String
Dim PMCFName As String
Dim PMCLName As String


On Error GoTo PROC_ERR

Set Cdb = CurrentDb

sqlStr = "SELECT tblDFLModified.[Check Request Date], tblDFLModified.RequestNbr, tblDFLModified.Payee, tblDFLModified.[Mailing Address]," & _
"tblDFLModified.City , tblDFLModified.State, tblDFLModified.[Zip Code], tblDFLModified.Amount, tblDFLModified.PMC," & _
"tblDFLModified.Ref_Type, tblDFLModified.FileNbr, tblDFLModified.[Buy/Sell Price], tblDFLModified.Program'" & _
"FROM tblDFLModified WHERE (((tblDFLModified.[Check Request Date])=[enter date]));"




Set Rs = Cdb.OpenRecordset(sqlStr)

If Rs.EOF Then
MsgBox "No Record found"
Exit Sub
End If

DoCmd.SetWarnings False


'If the template file has moved or renamed, then ask the user's help to locate the file.
If Not IsNull(DLookup("[DocPath]", "DocPath")) Then
FilePath = DLookup("[DocPath]", "DocPath") & "\Templates\CheckRequest.xls"
Else
FilePath = GetOpenFile_TSB("C", "Find Excel File Template")
End If

If Not FileExists(FilePath) Then
FilePath = GetOpenFile_TSB("C", "Find Excel File Template")
End If

If IsNull(FilePath) Then
MsgBox "Couldn't find the Excel Sheet Template"
Exit Sub
End If

Set objXLSheet = GetObject(FilePath)
objXLSheet.Application.Visible = True
objXLSheet.Windows(1).Visible = True


If Len(Nz(Rs.Fields("Amount"), "")) > 0 Then
.Cells(7, 3) = Rs.Fields("Amount")
Else
.Cells(7, 3) = "---"
End If

If Len(Nz(Rs.Fields("Payee"), "")) > 0 Then
.Cells(8, 2) = Rs.Fields("Payee")
Else
.Cells(8, 2) = "---"
End If

If Len(Nz(Rs.Fields("Mailing Address"), "")) > 0 Then
.Cells(9, 2) = Rs.Fields("Mailing Address")
Else
.Cells(9, 2) = "---"
End If

If Len(Nz(Rs.Fields("City"), "")) > 0 Then
.Cells(10, 2) = Rs.Fields("City")
Else
.Cells(10, 2) = "---"
End If

If Len(Nz(Rs.Fields("State"), "")) > 0 Then
.Cells(10, 4) = Rs.Fields("State")
Else
.Cells(10, 4) = "---"
End If

If Len(Nz(Rs.Fields("Zip Code"), "")) > 0 Then
.Cells(10, 5) = Rs.Fields("Zip Code")
Else
.Cells(10, 5) = "---"
End If

If Len(Nz(Rs.Fields("RequestNbr"), "")) > 0 Then
.Cells(21, 3) = Rs.Fields("RequestNbr" & "/")
Else
.Cells(21, 3) = "---"
End If

If Len(Nz(Rs.Fields("Ref_Type"), "")) > 0 Then
.Cells(21, 5) = Rs.Fields("Ref_Type" & "/")
Else
.Cells(21, 5) = "---"
End If

If Len(Nz(Rs.Fields("Buy/Sell Price"), "")) > 0 Then
.Cells(24, 1) = Rs.Fields("Buy/Sell Price")
Else
.Cells(24, 1) = "---"
End If

If Len(Nz(Rs.Fields("Program"), "")) > 0 Then
.Cells(25, 1) = Rs.Fields("Program")
Else
.Cells(25, 1) = "---"
End If

End Sub

 

[ffejeroni]

well, that didn't work. Any suggestions?

 

[oliver_penney]

hi

two things:-

1) could you just dump the data from the database into one sheet in excel and use formulas to tidy it up? save all the fiddly code
2) where does your code go wrong?