zimmer9
asked on
Do you know how I can prevent the Run time error '3010' table 'FA_ledger_report' already exists ? Actually the 'FA_ledger_report' is a Union query.
Do you know how I can prevent the Run time error '3010' table 'FA_ledger_report' already exists
FA_ledger_report is actually the following Union query:
SELECT COSTCENTER AS [COST CENTER],
SAR_CATEGORY AS [SAR],
ASSET_NUMBER AS [ASSET NUMBER],
ASSET_DESC AS [ASSET DESCRIPTION],
VENDOR_NAME AS [VENDOR NAME],
INVOICE_NUMBER AS [INVOICE NUMBER],
Format(BOOK_COST, "#,##0.00;(#,##0.00)") AS [BOOK COST],
Format(BOOK_ACCUM_DEPR, "#,##0.00;(#,##0.00)") AS [BOOK ACCUM DEPR],
Format(CURR_DEPR, "#,##0.00;(#,##0.00)") AS [CURR DEPR],
Format(YTD_DEPR, "#,##0.00;(#,##0.00)")AS [YTD DEPR],
Format(NET_BOOK_VALUE, "#,##0.00;(#,##0.00)") AS [NET BOOK VALUE],
Format(BEGIN_DEPR, "mm/yyyy") AS [BEGIN DEPR],
Format(DEPR_LIFE, "yy/mm") AS [DEPR LIFE],
LOCATION_ID, DESCRIPTION, ADDRESS2, CITY,STATE, ZIP, REPORT_RUN_DATE,
"" AS [Remarks/Comments]
from tblFaLedgerBranch
UNION SELECT "", "", "", "","","",
"___________________",
"___________________",
"___________________",
"___________________",
"___________________",
"", "", "", "", "", "", "", "", "",""
from tblFaLedgerBranch
group by COSTCENTER
UNION SELECT "", "", "", "","","",
Format(sum(BOOK_COST), "#,##0.00;(#,##0.00)"),
Format(sum(BOOK_ACCUM_DEPR ), "#,##0.00;(#,##0.00)"),
Format(sum(CURR_DEPR), "#,##0.00;(#,##0.00)"),
Format(sum(YTD_DEPR), "#,##0.00;(#,##0.00)"),
Format(sum(NET_BOOK_VALUE) , "#,##0.00;(#,##0.00)"),
"", "", "", "", "", "", "", "", "",""
from tblFaLedgerBranch
group by COSTCENTER
UNION select "","",
"", INSTRUCTIONS,"", "", "", "","","", "", "", "", "", "", "", "", "", "","",""
from tblInstuctions
GROUP BY INSTRUCTIONS
ORDER BY 1 DESC , 2;
-------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----
Public Function prtFaLedgerExcel()
Dim rs As ADODB.Recordset
Dim cn As ADODB.Connection
Dim fname As String
Dim sql As String
Dim fpath As String
Set cn = CurrentProject.Connection
Set rs = New ADODB.Recordset
fpath = "C:\FALEDGER\REPORTS\OUTPU TS\"
MsgBox ("The process of conversion FA Ledger Report to excel format is started. Please wait ... ")
'1. Open cost center table and pass cost center as a parameter
rs.Open "SELECT tblCostCenter.[COSTCENTER] FROM tblCostCenter GROUP BY tblCostCenter.[COSTCENTER] ;", cn
Do Until rs.EOF
fname = fpath & "fa_ledger_report" & "_" & rs("COSTCENTER") & ".xls"
'2. insert records to tblFaLedgerBranch only for selected cost center
sql = "INSERT INTO tblFaLedgerBranch ( COSTCENTER, SAR_CATEGORY, ASSET_NUMBER, ASSET_DESC, VENDOR_NAME, INVOICE_NUMBER, BOOK_COST, BOOK_ACCUM_DEPR, CURR_DEPR, YTD_DEPR, NET_BOOK_VALUE, BEGIN_DEPR, DEPR_METHOD_CODE, DEPR_LIFE, LOCATION_ID, DESCRIPTION, ADDRESS2, CITY, STATE, ZIP,REPORT_RUN_DATE,COMMEN TS )" & _
"SELECT COSTCENTER, SAR_CATEGORY, ASSET_NUMBER, ASSET_DESC, VENDOR_NAME, INVOICE_NUMBER, BOOK_COST, BOOK_ACCUM_DEPR, CURR_DEPR, YTD_DEPR, NET_BOOK_VALUE, BEGIN_DEPR, DEPR_METHOD_CODE, DEPR_LIFE, " & _
"LOCATION_ID , Description, ADDRESS2, CITY, State, ZIP, REPORT_RUN_DATE, '' " & _
"FROM dbo_FA_LEDGER_LOCATION " & _
"WHERE (dbo_FA_LEDGER_LOCATION.CO STCENTER = '" & rs(0) & "')"
DoCmd.SetWarnings False
DoCmd.RunSQL sql
'3. Convert query to excel format
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Fa_ledger_report", fname, True <--- the compiler stops here on this hightlighted line in yellow
If isFileExist(fname) Then StartDocLN fname
'4. Drop temp table
sql = "DELETE tblFaLedgerBranch FROM tblFaLedgerBranch"
DoCmd.RunSQL sql
sql = ""
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
Set cn = Nothing
MsgBox ("The process of conversion FA Ledger Report to excel format is completed.")
'If isFileExist(fname) Then StartDocLN fname
DoCmd.SetWarnings True
End Function
Private Sub StartDocLN(filename)
Dim xlApp As Excel.Application
Dim xlWB As Excel.Workbook
Dim xlWS As Excel.Worksheet
'open excel template
Set xlApp = New Excel.Application
'xlApp.Visible = True
Set xlWB = xlApp.Workbooks.Open(filen ame)
Set xlWS = xlWB.Worksheets(1)
xlWS.Columns.AutoFit
xlWS.Range("G2:K16635").Nu mberFormat = "#,##0.00"
xlWS.Range("G2:K16635").Va lue = xlWS.Range("G2:K16635").Va lue
xlApp.ScreenUpdating = True
End Sub
FA_ledger_report is actually the following Union query:
SELECT COSTCENTER AS [COST CENTER],
SAR_CATEGORY AS [SAR],
ASSET_NUMBER AS [ASSET NUMBER],
ASSET_DESC AS [ASSET DESCRIPTION],
VENDOR_NAME AS [VENDOR NAME],
INVOICE_NUMBER AS [INVOICE NUMBER],
Format(BOOK_COST, "#,##0.00;(#,##0.00)") AS [BOOK COST],
Format(BOOK_ACCUM_DEPR, "#,##0.00;(#,##0.00)") AS [BOOK ACCUM DEPR],
Format(CURR_DEPR, "#,##0.00;(#,##0.00)") AS [CURR DEPR],
Format(YTD_DEPR, "#,##0.00;(#,##0.00)")AS [YTD DEPR],
Format(NET_BOOK_VALUE, "#,##0.00;(#,##0.00)") AS [NET BOOK VALUE],
Format(BEGIN_DEPR, "mm/yyyy") AS [BEGIN DEPR],
Format(DEPR_LIFE, "yy/mm") AS [DEPR LIFE],
LOCATION_ID, DESCRIPTION, ADDRESS2, CITY,STATE, ZIP, REPORT_RUN_DATE,
"" AS [Remarks/Comments]
from tblFaLedgerBranch
UNION SELECT "", "", "", "","","",
"___________________",
"___________________",
"___________________",
"___________________",
"___________________",
"", "", "", "", "", "", "", "", "",""
from tblFaLedgerBranch
group by COSTCENTER
UNION SELECT "", "", "", "","","",
Format(sum(BOOK_COST), "#,##0.00;(#,##0.00)"),
Format(sum(BOOK_ACCUM_DEPR
Format(sum(CURR_DEPR), "#,##0.00;(#,##0.00)"),
Format(sum(YTD_DEPR), "#,##0.00;(#,##0.00)"),
Format(sum(NET_BOOK_VALUE)
"", "", "", "", "", "", "", "", "",""
from tblFaLedgerBranch
group by COSTCENTER
UNION select "","",
"", INSTRUCTIONS,"", "", "", "","","", "", "", "", "", "", "", "", "", "","",""
from tblInstuctions
GROUP BY INSTRUCTIONS
ORDER BY 1 DESC , 2;
--------------------------
Public Function prtFaLedgerExcel()
Dim rs As ADODB.Recordset
Dim cn As ADODB.Connection
Dim fname As String
Dim sql As String
Dim fpath As String
Set cn = CurrentProject.Connection
Set rs = New ADODB.Recordset
fpath = "C:\FALEDGER\REPORTS\OUTPU
MsgBox ("The process of conversion FA Ledger Report to excel format is started. Please wait ... ")
'1. Open cost center table and pass cost center as a parameter
rs.Open "SELECT tblCostCenter.[COSTCENTER]
Do Until rs.EOF
fname = fpath & "fa_ledger_report" & "_" & rs("COSTCENTER") & ".xls"
'2. insert records to tblFaLedgerBranch only for selected cost center
sql = "INSERT INTO tblFaLedgerBranch ( COSTCENTER, SAR_CATEGORY, ASSET_NUMBER, ASSET_DESC, VENDOR_NAME, INVOICE_NUMBER, BOOK_COST, BOOK_ACCUM_DEPR, CURR_DEPR, YTD_DEPR, NET_BOOK_VALUE, BEGIN_DEPR, DEPR_METHOD_CODE, DEPR_LIFE, LOCATION_ID, DESCRIPTION, ADDRESS2, CITY, STATE, ZIP,REPORT_RUN_DATE,COMMEN
"SELECT COSTCENTER, SAR_CATEGORY, ASSET_NUMBER, ASSET_DESC, VENDOR_NAME, INVOICE_NUMBER, BOOK_COST, BOOK_ACCUM_DEPR, CURR_DEPR, YTD_DEPR, NET_BOOK_VALUE, BEGIN_DEPR, DEPR_METHOD_CODE, DEPR_LIFE, " & _
"LOCATION_ID , Description, ADDRESS2, CITY, State, ZIP, REPORT_RUN_DATE, '' " & _
"FROM dbo_FA_LEDGER_LOCATION " & _
"WHERE (dbo_FA_LEDGER_LOCATION.CO
DoCmd.SetWarnings False
DoCmd.RunSQL sql
'3. Convert query to excel format
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Fa_ledger_report", fname, True <--- the compiler stops here on this hightlighted line in yellow
If isFileExist(fname) Then StartDocLN fname
'4. Drop temp table
sql = "DELETE tblFaLedgerBranch FROM tblFaLedgerBranch"
DoCmd.RunSQL sql
sql = ""
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
Set cn = Nothing
MsgBox ("The process of conversion FA Ledger Report to excel format is completed.")
'If isFileExist(fname) Then StartDocLN fname
DoCmd.SetWarnings True
End Function
Private Sub StartDocLN(filename)
Dim xlApp As Excel.Application
Dim xlWB As Excel.Workbook
Dim xlWS As Excel.Worksheet
'open excel template
Set xlApp = New Excel.Application
'xlApp.Visible = True
Set xlWB = xlApp.Workbooks.Open(filen
Set xlWS = xlWB.Worksheets(1)
xlWS.Columns.AutoFit
xlWS.Range("G2:K16635").Nu
xlWS.Range("G2:K16635").Va
xlApp.ScreenUpdating = True
End Sub
ASKER
The first approach:
Try with:
kill fpath & "fa_ledger_report" & "_" & rs("COSTCENTER") & ".xls"
fname = fpath & "fa_ledger_report" & "_" & rs("COSTCENTER") & ".xls"
This results in Run time error '70':
Permission denied
-------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ------
The second approach:
to make sure the .xls doesn't exist before execution or perhaps better use:
fname = fpath & "fa_ledger_report" & "_" & rs("COSTCENTER") & format(Now(),"yyyy/mm/dd hh:nn:ss") & ".xls"
This results in Runtime error '3044':
C:\FALEDGER\REPORTS\OUTPUT S\fa_ledge r_report_1 001002006\ 04\:15:57: 38.xls is not a valid path. Make sure that the pathname is spelled correctly and that your are connected to the server on which the file resides.
Try with:
kill fpath & "fa_ledger_report" & "_" & rs("COSTCENTER") & ".xls"
fname = fpath & "fa_ledger_report" & "_" & rs("COSTCENTER") & ".xls"
This results in Run time error '70':
Permission denied
--------------------------
The second approach:
to make sure the .xls doesn't exist before execution or perhaps better use:
fname = fpath & "fa_ledger_report" & "_" & rs("COSTCENTER") & format(Now(),"yyyy/mm/dd hh:nn:ss") & ".xls"
This results in Runtime error '3044':
C:\FALEDGER\REPORTS\OUTPUT
Hmm, sorry try:
fname = fpath & "fa_ledger_report" & "_" & rs("COSTCENTER") & format(Now(),"yyyymmdd_hhn nss") & ".xls"
Nic;o)
fname = fpath & "fa_ledger_report" & "_" & rs("COSTCENTER") & format(Now(),"yyyymmdd_hhn
Nic;o)
ASKER
How 'bout if the compiler now stops at the following line ?
Set xlApp = New Excel.Application
with a Run time error '429':
ActiveX component can't create object
I already went into Tools --> References and checked off the following boxes:
Microsoft ActiveX Data Objects 2.5 Library
Microsoft Excel 11.0 Object Library
Set xlApp = New Excel.Application
with a Run time error '429':
ActiveX component can't create object
I already went into Tools --> References and checked off the following boxes:
Microsoft ActiveX Data Objects 2.5 Library
Microsoft Excel 11.0 Object Library
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
kill fpath & "fa_ledger_report" & "_" & rs("COSTCENTER") & ".xls"
fname = fpath & "fa_ledger_report" & "_" & rs("COSTCENTER") & ".xls"
to make sure the .xls doesn't exist before execution or perhaps better use:
fname = fpath & "fa_ledger_report" & "_" & rs("COSTCENTER") & format(Now(),"yyyy/mm/dd hh:nn:ss") & ".xls"
Nic;o)