Been hunting a round looking for some code that will output my access report to excel but more inportantly add a unique field from the report to the name of the file. example would be [FileName] & [ReferenceNo].xls.
View RepliesI would like to add the "Month" of the information queried to my file name at export if possible. I included my current code and an example of how I would like the title to read.
DoCmd.TransferSpreadsheet acExport, 10, "tblStarzIncentivePayoutExport", "C:StarzExportsIncentive Payouts" & " " & "Ran" & " " & Format(Date, "mm-dd-yyyy")
I would like the file to be named:
Incentive Payouts "Month out of the date ran in the query" Ran 06/08/2015
Is it possible to do?
I have a query that contains Invoices(usually 2 line items per invoice) Taxable and NonTaxable. I need to able to basically export it to excel on multiple worksheets when the Amount of unique invoice number reaches 15.
From all my scouring on the internet I can't find anything with this criteria.
Im trying to output my VBA code to a report.
Here is my code:
Code:
Dim strEmployeeName As String
strEmployeeName = EnUsername
DoCmd.RunSQL "SELECT StrConv(Replace([sUser],""."","" ""),3) AS Employee, TimesheetTable.Activity, TimesheetTable.Hours, TimesheetTable.Description, TimesheetTable.[Task Date], TimesheetTable.ProjectRef " & vbCrLf & _
"FROM TimesheetTable " & vbCrLf & _
"WHERE (((StrConv(Replace([sUser],""."","" ""),3)) Like """ & strEmployeeName & """) AND ((TimesheetTable.[Task Date])>=#" & [Forms]![DateSelection_Frm]![txtWeekCommencing] & "# And (TimesheetTable.[Task Date]<= #" & [Forms]![DateSelection_Frm]![txtWeekEnding] & "#)) AND ((TimesheetTable.ProjectRef) Not Like ""CENG*""));"
And my report is called TimeReport.
I currently have an invoice report that runs through my database which is e-mailed to clients (automatically through vba), and then saved to a specific file path.
I'm currently having an issue with the saving part of the process with certain clients who have illegal characters in their names - for example, "Client / Other Information" where the '/' is causing the save to fail.
I'm trying to find a way to change the file name of the save file for clients with these illegal characters in their names. I would prefer not to take only part of the name (as some clients are listed as "Town/Village of...") but if there is no other way then there is no other way.
The code I'm currently using is as follows:
Code:
Dim strFilePath as String
strFilePath="C:Users" & Environ("UserName") & "DesktopNew Invoices" & Me.Client.Value & ".pdf"
docmd.OutputTo acOutputReport, , "PDFFormat(*.pdf)", strFilePath, False, "", 0, acExportQualityPrint
I have a routine that outputs an MS Access report as a pdf file which works OK. It does not open the file which is what I want.
DoCmd.OutputTo acReport, "rptTransactionInvoiceExcVAT", acFormatPDF, "C:UsersJEFF1Documents
ptTransactionInvoiceExc VAT.pdf", False
What I would like to do is output the pdf file with a Customer name and number from the field Me.txtAmazonBuyer. I have tried:
DoCmd.OutputTo acReport, "rptTransactionInvoiceExcVAT", acFormatPDF, "C:UsersJEFF1Documents" + ([Me.txtAmazonBuyer]) + ".pdf", False
But no joy.
DoCmd.OutputTo acOutputReport, "Estimate", acFormatPDF, "c:TestMyReport.pdf"
the following code saves my report in a folder called test as a pdf file with the name MyReport. I have a string variable called RepName. How can I make the report get saved under that name.
I tried
DoCmd.OutputTo acOutputReport, "Estimate", acFormatPDF, "c:Test&MyReport&.pdf" but it dosent work.
I have a Database in which I would like to archive data once it is now longer actively being used. I have VBA code that does everything i need it to, however I would like to name the new archive table related to the dates in the data being archived. Below is the code I am using to archive and delete the information from the main table.
Code:
Dim strSQLCreate As String
Dim strSQLDelete As String
Dim strArchiveTableName As String
[Code].....
I know it will be in the "strArchiveTableName" variable, I am just not sure how to pull the dates out of the data being moved. The TestData table holds a date and time for each test, and the earliest and latest dates is what i want to append to the table name.
For example: Currently "tblTestData_A102Archive"What I want "tblTestData_A102_1/1/2012-1/1/2013"
I'm currently working with Access 2007 building a way to create PODs to clients. I have most everything laid out, one problem I can't seem to get around is naming the file the SOW #. Below is the code:
Function Run_all_PODs_01()
Dim myPath As String
Dim name As Object
DoCmd.OpenQuery "Q5 SOW bill requested data points all", acViewNormal, acEdit
DoCmd.OpenQuery "Q5 SOW bill requested All 01", acViewNormal, acEdit
[Code] ...
I can't seem to get the name to equal the SOW number.
The table that I'm trying to reference is "SOW bill requested data points" it is a basic table one row with a column header as SOW #. I'm not sure how to tell it name the file that data point in the column.
Hi
If I open a query in Access and uses the "Export to Excel" button everything works out just fine.
But when I want to make the export with a button using the code:
DoCmd.OutputTo acOutputQuery, "qryToExcel", acFormatXLS
I only get 255 chars from the PM-fields to excel.
Can anyone help me solve this problem?
I also want to make some filtering, with code string, to the query before I export it, could use some suggestions there also.
Thanks in advance
I am having a problem getting Access to output to an excel file.
I want to take a form that I have that has 20 or so records with 6 or 7 fields for each and put it into an excel file that is formated the way I need everythign to look.
I also need it to make the file name such as CCCAAAMMDDYYYY (3 letter company abbreviation,3 letter initials and then todays date) I am thinking that the company abbreviation will be pulled from a query, then initials will be a form that opens, and the date can be pulled from access' date function.
I tried using vb code and doing an output to comand but that did not work.
I am confused someone please help if possible.
I have written some code that will output to a spreadsheet in a given location:
how can I rework this code so that the excel output displays on screen rather than saving to a specified location:
Code Written:
Private Sub outputToExcel_Click()
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "C4C Period Final", "\uk.michaelpage.localdfsGroupDataNSCHElite Database ReportsGross Cash For Car.xls"
MsgBox "C4C Period Final Exported"
End Sub
I need to create a word file (it needs to be word) based on template (not a very complicated template) max 1 to 1 and 1/2 pages long.
the word doc needs to be named from the recordset but for now assume 12345.doc and the next one will be 12346.doc etc (I have a unique number system - available from tables /query .
i can either make the word doc in code or use a template (template would be better) recordset could have 20-30 in it each time i run it - but to be run every week
second half is how to email this out. i had in mind a email system
email to . test@testhotmail.xyz
file attach = this folder where the docs are store and attach 12345
in a loop
so either
create word file - email word file in a loop each time or create all the word files and then send them individual
I want to hit a button and then it scans for a file on a server and return with a message box saying if it exists or not.
I want it so when I am on a Specific record it will search for certain requirements.
All the file names are pdf files with the same naming structure
SIT yy m(m) dd Last Name Unique Identifier
Example:
SIT 14 5 21 Smith ABCD1234567
SIT 14 10 21 Smith ABCD1234567
So I hit the button it sweeps the file path and looks for documents with this naming structure.
Last Name and Unique Identifier are fields within the db.
Last Name = txtLName
Unique Identifier = txtUniqIden
So far I have the following:
Code:
Dim strDirName As String
Dim strDirPath As String
Dim strDirName As String
Dim strDirPath As String
[Code] ....
So Far it works and I used the actual name of the items an it finds them but I need to up each record.
I have no clue how to begin with the strDirName. How to get it to scan the file name in a specific format.
Windows XP Pro/Office 2000
I was able to output to excel before fine, now all of a sudden I cannot. I keep getting a message when I go Tools>Office Links>Analyze with MS Excel:
"Cannot access "xxxvac_Inv_DBQuery.xls"
It's wierd. It won't output the data, but it still creates the spreadsheet file.
Any Idea's?
Thanks to all in advance, your help is greatly appreciated! :(
Hello first of all I have learned a lot from this forum so thanks you all who post questions & solutions. Ok I have a table which I use to query info and export to excel. My question is there a way to add sum totals to a few of the columns once it is in excel. I was thinking it would be easier to do a sum statement then just add it as the bottom row while it was exporting. Or would it be easier to do it once it is in excel. Here is my code:
Private Sub Command63_Click()
Dim strSQL As String, strOrder As String, strWhere As String
Dim dbNm As Database
Dim qryDef As QueryDef
Set dbNm = CurrentDb()
strSQL = "SELECT tblCONSOLIDATED.ACCOUNT1, tblCONSOLIDATED.COMPANY_NAME, tblCONSOLIDATED.CUSTOMER_TYPE, tblCONSOLIDATED.ADDRESS1, tblCONSOLIDATED.ADDRESS2, tblCONSOLIDATED.CITY, tblCONSOLIDATED.STATE, tblCONSOLIDATED.ZIP, tblCONSOLIDATED.CONTACT_NAME, tblCONSOLIDATED.E_MAIL, tblCONSOLIDATED.TELEPHONE, tblCONSOLIDATED.FAX, tblCONSOLIDATED.REP_NUMBER, tblCONSOLIDATED.PROMOCODE, tblCONSOLIDATED.SALESCODE, tblCONSOLIDATED.CURRENT_YTD, tblCONSOLIDATED.PRIOR_YTD, tblCONSOLIDATED.PRIOR_TOTAL, tblCONSOLIDATED.YEAR2_TOTAL, tblCONSOLIDATED.YEAR3_TOTAL, tblCONSOLIDATED.YEAR4_TOTAL " & _
"FROM tblCONSOLIDATED"
strWhere = "WHERE"
strOrder = "ORDER BY CURRENT_YTD DESC"
If Not IsNull(Me.txtCSONME) Then
strWhere = strWhere & " (tblCONSOLIDATED.COMPANY_NAME) Like '*" & Me.txtCSONME & "*' AND"
End If
If Not IsNull(Me.txtCSOSLD) Then
strWhere = strWhere & " (tblCONSOLIDATED.ACCOUNT1) Like '*" & Me.txtCSOSLD & "*' AND"
End If
If Not IsNull(Me.txtCSOARN) Then
strWhere = strWhere & " (tblCONSOLIDATED.CONTACT_NAME) Like '*" & Me.txtCSOARN & "*' AND"
End If
If Not IsNull(Me.txtCSOCTY) Then
strWhere = strWhere & " (tblCONSOLIDATED.CITY) Like '*" & Me.txtCSOCTY & "*' AND"
End If
If Not IsNull(Me.txtCSOST) Then
strWhere = strWhere & " (tblCONSOLIDATED.STATE) Like '*" & Me.txtCSOST & "*' AND"
End If
If Not IsNull(Me.txtCSOZIP) Then
strWhere = strWhere & " (tblCONSOLIDATED.ZIP) Like '*" & Me.txtCSOZIP & "*' AND"
End If
If Not IsNull(Me.txtCSOSSM) Then
strWhere = strWhere & " (tblCONSOLIDATED.REP_NUMBER) Like '*" & Me.txtCSOSSM & "*' AND"
End If
If Not IsNull(Me.txtCSOM1) Then
strWhere = strWhere & " (tblCONSOLIDATED.PROMOCODE) Like '*" & Me.txtCSOM1 & "*' AND"
End If
If Not IsNull(Me.txtSLCYYD1) Then
strWhere = strWhere & " (tblCONSOLIDATED.CURRENT_YTD) BETWEEN " & Me.txtSLCYYD1 & " And " & Me.txtSLCYYD2 & " AND"
End If
If Not IsNull(Me.txtSLLYYD1) Then
strWhere = strWhere & " (tblCONSOLIDATED.PRIOR_YTD) BETWEEN " & Me.txtSLLYYD1 & " And " & Me.txtSLLYYD2 & " AND"
End If
If Not IsNull(Me.txtSLPYR11) Then
strWhere = strWhere & " (tblCONSOLIDATED.PRIOR_TOTAL) BETWEEN " & Me.txtSLPYR11 & " And " & Me.txtSLPYR12 & " AND"
End If
If Not IsNull(Me.txtSLPYR21) Then
strWhere = strWhere & " (tblCONSOLIDATED.YEAR2_TOTAL) BETWEEN " & Me.txtSLPYR21 & " And " & Me.txtSLPYR22 & " AND"
End If
If Not IsNull(Me.txtSLPYR31) Then
strWhere = strWhere & " (tblCONSOLIDATED.YEAR3_TOTAL) BETWEEN " & Me.txtSLPYR31 & " And " & Me.txtSLPYR32 & " AND"
End If
If Not IsNull(Me.txtSLPYR41) Then
strWhere = strWhere & " (tblCONSOLIDATED.YEAR4_TOTAL) BETWEEN " & Me.txtSLPYR41 & " And " & Me.txtSLPYR42 & " AND"
End If
If (Me.PROSPECTBX) = True Then
strWhere = strWhere & " (tblCONSOLIDATED.CUSTOMER_TYPE) Like 'P' AND"
End If
If Not IsNull(Me.txtSLCLS) Then
strWhere = strWhere & " (tblCONSOLIDATED.SALESCODE) Like '*" & Me.txtSLCLS & "*' AND"
End If
If strWhere = "WHERE" Then
strWhere = ""
Else
strWhere = Trim(Left(strWhere, Len(strWhere) - Len("AND")))
End If
Set qryDef = dbNm.QueryDefs("qrySALESDATA")
qryDef.SQL = strSQL & " " & strWhere & "" & strOrder
DoCmd.OutputTo acQuery, "qrysalesdata", "MicrosoftExcel(*.xls)", "QUERY RESULTS.xls", True, ""
End Sub
Sorry for adding the whole thing but I'm not sure it should be done after "Query Results.xls" is written or during the select statement.
Thanks in advance for your help.
Bryan
Hi Guys,
I have 2 tables
tblXRFResults
ResultID
SampleName
Date
SampleDate
a few others not necessary in this
and tblXRFResultsConcentration
ResultID (many records linked to 1 record in tblXRFResults)
Concentration
CompoundName
What I need to set up is a query that will allow me to generate a report that will give an excel like format, with the column headings being the SampleName, followed by the CompoundName(s) from the other table and the "rows" will be corresponding sample name and concentrations.
I am sure this is pretty easy, but I am stumped!
Any help would be greatly appreciated.
I am trying to write code in an excel worksheet to try to export excel data to a table in access.
The range in which the data is situated in excel varies. So the range address varies with the data. After a lot of searching I wrote the following code which works for a range which is fixed. The code is the following:
Dim acc As New Access.Application
Range(Range("b22"), Range("b22").End(xlDown)).Select
ActiveWorkbook.Names.Add Name:="ghazla", RefersTo:=Selection
acc.OpenCurrentDatabase "D:PayRollFactoryJasminePayroll.accdb"
acc.DoCmd.TransferSpreadsheet _
[Code] .....
As one can see from the first two lines of code I am trying to first delineate the range and then to name it. The intent is to name the range "ghazla" and so to cater for the fact that the extent of data varies.
The code works with the fixed range ="Sheet2$B140". However what I need to do is to adjust the code so that I can insert "ghazla" as the range name so that this can vary.
hi
Would be glad if somebody could help me on this issue that been nagging a set a databases that we use.
The Access databases that we use are around 200 MB in size..
Its multiuser say maybe around 40-60 users.The database is split to enhance performance.
We use DoCmd.OutputTo ...statement to output the query into Excel sheet.
Example :
DoCmd.OutputTo acOutputQuery, "qryAllPrems", acFormatXLS, , False
The problem that we are facing is that the after the execution of the statment the query ( qryAllPrems) gets wiped out....i.e the entire sql in the query gets wiped out clean ..
It's driving us crazy as to what could be the cause of the problem...and I would appreciate it if somebody could help us..in this regard.
I guess you've all heard this one before, but I'm relatively new to Access, and was wondering if there was a way to export data from a form to an already existing Excel spreadsheet via a form button. Is this even possible in MS Office 2007, or even at all?
View 2 Replies View RelatedI have another request for you. I did all the analysis. My final query looks as follows
Jan_N-1, Feb_N-1, Mar_N-1, Jan_N-2, Feb_N-2, Mar_N-2
0.98......... 0.90..... 0.88....... 0.95....... 0.88...... 0.70
but I need Access to create a excel table like output. which looks as like
.......Jan. Feb. Mar
N-1 0.98 0.90 0.88
N-2 0.95 0.88 0.70
Recently I set up a button to export a report automatically to Excel using the following code: DoCmd.outputTo acOutput Report, "Report1", acFormat.xls, "J:Insurance DeptReport1.xls", True.This works great, however I was wondering 2 things:
1. How do you set up to transfer multiple reports using the same button. EG I have reports named Report2, Report3, etc how do I add these to the code to export as well?
2. Is it possible to have them transfer to the one file but different sheets. EG Report1 would go to sheet1, Report2 to sheet 2, report3 to sheet 3 and so on?
in Access i have made a report with data and now my goal is that if i click on a button the data will be exported to a existing Excel file under the other existing data so in row A6. Is this possible true a VBA code?
View 2 Replies View RelatedI have a table with a field for gender and iit is populated with "m" for male and "f" for female.
I have created a report and would like it to display the word male or female for the m or f.
Is there a way I can do this in stead of doing a find and replace on over 10.000 records.
Thank You
I have a control button on a form that outputs the current record to disk using an on click event. The user currently has to select the format (rtf) the path and the filename and I would like to be able to automate this process.
The format should be rtf, the path will always be the same and I would like the file name to comprise the unique record number (4 digits) followed by the current date string (format ddmmyy).
I think is just a matter of setting some parameters after the DoCmd code event that saves the report to file but I have had a go but can't get the syntax right.
Any ideas how to automate this process to eliminate the risk of user errors?
Thanks
Andy
I have a report and wish to convert the output of this report into a .csv file.
Can anyone help?
Thanks
Paul