Fordraiders
asked on
Procedure in Access will not place data on excel sheet
vba 2003
access 2003, excel 2003
I'm running a procedure from access and passing variables to the procedure in access. as shown below..
Public Sub cmdCreatePrebuilt_Click()
Dim strSeg As Variant
Dim strFam As Variant
Dim strCat As Variant
strSeg = ComboBox1.Text
strFam = ComboBox2.Text
strCat = ComboBox3.Text
Dim remoteApp As Access.Application
Set remoteApp = New Access.Application
remoteApp.OpenCurrentDatab ase "C:\Program Files\Crs_iCart\Enterprise _Reporting _Backend.m db", False 'you may need to change the false into true
remoteApp.Visible = True
remoteApp.Run "CallExportSeg", strSeg, strFam, strCat
remoteApp.CloseCurrentData base
Set remoteApp = Nothing
' restore the old cursor or else you may never get it back
MsgBox "Done", vbInformation, "Export Segment:Family:Category"
End Sub
The procedure will run but no data is posted to the excel sheet
A sheet is being created but no data is placed in the sheet ?
Thanks
fordraiders
access 2003, excel 2003
I'm running a procedure from access and passing variables to the procedure in access. as shown below..
Public Sub cmdCreatePrebuilt_Click()
Dim strSeg As Variant
Dim strFam As Variant
Dim strCat As Variant
strSeg = ComboBox1.Text
strFam = ComboBox2.Text
strCat = ComboBox3.Text
Dim remoteApp As Access.Application
Set remoteApp = New Access.Application
remoteApp.OpenCurrentDatab
remoteApp.Visible = True
remoteApp.Run "CallExportSeg", strSeg, strFam, strCat
remoteApp.CloseCurrentData
Set remoteApp = Nothing
' restore the old cursor or else you may never get it back
MsgBox "Done", vbInformation, "Export Segment:Family:Category"
End Sub
The procedure will run but no data is posted to the excel sheet
A sheet is being created but no data is placed in the sheet ?
Thanks
fordraiders
procedure in access:
Public Sub ExportSegFamCat(strSeg As String, strFam As String, strCat As String)
Dim strsql As String, qd As DAO.QueryDef
Set qd = CurrentDb.QueryDefs("qrySegFamCat")
On Error GoTo ExportSegFamCat_Err
strsql = "SELECT dbo_WwgExtraChgo.material_no, dbo_WwgExtraChgo.long_description, dbo_WwgExtraChgo.short_description, dbo_WwgExtraChgo.current_catalog_page_no, "
strsql = strsql & "dbo_WwgExtraChgo.sales_status, dbo_WwgExtraChgo.condensed_mfr_model_no, dbo_BrandInformation.brand_name, "
strsql = strsql & "dbo_BrandInformation.private_label, dbo_WwgExtraChgo.green_material_flag, dbo_WwgExtraChgo.van_eligible, "
strsql = strsql & "dbo_WwgExtraChgo.segment_name, dbo_WwgExtraChgo.family_name, dbo_WwgExtraChgo.category_name"
strsql = strsql & " FROM dbo_WwgExtraChgo LEFT JOIN dbo_BrandInformation ON dbo_WwgExtraChgo.brand_no = dbo_BrandInformation.brand_no"
strsql = strsql & " WHERE dbo_WwgExtraChgo.segment_name = '" & strSeg & "' AND dbo_WwgExtraChgo.family_name = '" & strFam & "' AND dbo_WwgExtraChgo.category_name = '" & strCat & "' "
qd.SQL = strsql
DoCmd.TransferSpreadsheet acExport, 8, "qrySegFamCat", "C:\Program Files\Crs Enterprise\iFinal\Prebuilt_Export\PreBuilt_Export.xls", True, ""
qd.Close
Set qd = Nothing
ExportSegFamCat_Exit:
Exit Sub
ExportSegFamCat_Err:
If Err.Number = "3010" Then
MsgBox "The Excel Sheet is Already Open. Close The Open Workbook", vbCritical, "Error Exporting Prebuilt"
Exit Sub
End If
Resume ExportSegFamCat_Exit
End Sub
Rather than have one database open another then call a procedure to run a query
Why not just build the query in the first database to get the data from the second?
strsql = "SELECT dbo_WwgExtraChgo.* FROM Clients
IN 'C:\Program Files\Crs_iCart\Enterprise _Reporting _Backend.m db';"
Seems a lot easier than messing about calling secondary procedures
Why not just build the query in the first database to get the data from the second?
strsql = "SELECT dbo_WwgExtraChgo.* FROM Clients
IN 'C:\Program Files\Crs_iCart\Enterprise
Seems a lot easier than messing about calling secondary procedures
ASKER
moteApp.Run "CallExportSeg", strSeg, strFam, strCat <-----I get an illegal dunction call here <-------------
Ah!
Unfortunately I don't have access to ACCESS at the moment but I think I know what the problem is. Instead of using
remoteApp.Run
use
remoteApp.DoCmd.RunMacro
Sid
Unfortunately I don't have access to ACCESS at the moment but I think I know what the problem is. Instead of using
remoteApp.Run
use
remoteApp.DoCmd.RunMacro
Sid
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
THANKS
2) Debug print the variables in ExportSegFamCat and see if the values are being passed.
We will take it form there.
Sid