Modules & VBA :: UDF In Excel Template - Turning Letters Into Numbers

Jun 12, 2015

I have this UDF in my excel template that changes a set of numbers in to letters corresponding a code.

For example the code is "EUCHARISTO" it would simply mean

E=1, U=2, C=3, H=4, A=5, R=6, I=7, S=8, T=9, O=0 and by default the tenths position (".0")=X the hundredths position (.0"0") = Y and If a number repeats it becomes G.

Examples
12.50 = EUAY
123.00 = EUCXY
12.25 = EUGA
99.00 = TGXY
99.50 = TGAY
999.00 = TGTXY
999.99= TGTGT

Here's the UDF:

Code:

Function LetterCode(ByVal Numbers As String, Letters As String) As String
Dim X As Long
Numbers = Format(Numbers, "0.00") * 100
Letters = UCase(Right(Letters, 1) & Left(Letters, Len(Letters) - 1))
If Numbers Like "*0" Then Mid(Numbers, Len(Numbers)) = "Y"

[Code] ...

Also, where best to put this code, In a query or in a module.

View Replies


ADVERTISEMENT

Modules & VBA :: How To Auto Fill Excel Template

Jun 19, 2013

I have info on my access form which is located here....

Code : Forms![Front Page]![Site 2 Owner]

and here

Code : Forms![Front Page]![Postcode S2]

I would like to export this information into cells B2 & C2( individually and respectively) on the excel spreadsheet which I have saved as a template here...

Code : C:UsersmedesktopAutoExcel Auto

Is this easily achievable? To be honest I will be using it to fill in about 12 cells but how it would be done for the first two i can just modify it as necessary.

View 6 Replies View Related

Modules & VBA :: Paste Query To Excel Template

Jul 20, 2014

I created a form with lots of conditional formatting that did pretty much everything I wanted it to do. The only problem is that it takes about 4 full minutes for the form to open.

As an experiment I am reluctantly now trying to display the results in Excel. I have created a template xls sheet and all I want to do is, on the press of a button, copy the results from my query and paste them in to cell a1 of my spreadsheet.

I found the following code online which I am trying to adapt.

Code:
Private Sub update_tracker_Click()
Dim XL As Excel.Application
Dim wbTarget As Workbook
Dim qdfResults As QueryDef
Dim rsResults As Recordset
'Set up refernce to the query to export

[Code] ....

My limited knowledge however results in a couple of errors.

The first error User defined type not defined error appears at the very first line of my code

Code:
Dim XL As Excel.Application

The next error occurs as I am not sure how to reference the query to export. The query is called 2014 Resources and outputs also to a form called 2014 Resources.

View 11 Replies View Related

Modules & VBA :: Exporting Data To Excel Template But Saves Under Different Name

Apr 29, 2014

I am able to use DoCmd.TransferSpreadsheet to export data from Access to Excel, however, I want to be able transfer data into a specific sheet within an Excel template (e.g. Tasking.xls), that will then save under a different name (e.g. Tasking 20140429.xls). The other sheets within the Excel template contain pivots etc. so they will need updating during this process. The Excel template should just close down and remain in its original format.

This process will occur once a week so the dates will have to change accordingly.

View 7 Replies View Related

Modules & VBA :: Exporting To Excel Template And Saving As New File

Jun 25, 2014

Any definitive way of exporting a query to an Excel file and then saving it as a new file without saving over the original.

I've tried to remove any confidential info from the code below so it's not exactly the same.

Code:
Dim XLApp As Excel.Application
Dim XLSheet As Excel.Worksheet
Dim tmpRS As DAO.Recordset
Dim strFolder as String
strFolder = ("C:Profiles"& [Name] & "")

[Code] ....

The error seems to be with the SQL statement although that may just be the first error that it got to. I read that you can't refer to a Query if it has a criteria and that you have to write the SQL directly into the code.

View 13 Replies View Related

Modules & VBA :: Export And Import Particular Cells From Excel Template

Jul 20, 2015

I have a few problems and I want them to be able to be done from switchboard:

1. Is there a way for me to export a particular report (after selecting it) to a closed excel template, that is formatted? It would open the excel template (that has a logo and column headings), export data to below the column headings, then save the file with a unique name?

2. Also, a way to import data from an excel file, after allowing the user to select file? Only data below the column headings mentioned above. Same data will be appended to existing table.

View 1 Replies View Related

Modules & VBA :: Export To Specific Sheet In Template Excel File

Feb 11, 2015

So I press a button on my Form1 and my tbl_customers table is exported onto a specific sheet in a templated Excel file "customer-template" that I have created.

This file has formulas on another sheet that based on the imported data.

The file is then saved to a specific location C:AccessCustomersHistory with the file name based on a date that was criteria from my original form E.g. "customers 11-02-15"

View 3 Replies View Related

Modules & VBA :: Filling Excel Template Cells / Save Then Send On Outlook

Jul 13, 2013

I have the code below which takes information from a form on access and sends it over to the correct place on an excel spreadsheet template. This works fine but I then need it to save and send on outlook.

The issue I am having is that the saved document is not attaching to the e-mail. The subject etc all work fine but the excel spreadsheet just doesn't attach. When I go into the folder I have specified for the document to be saved in it isn't there either. :0(

The code for the e-mail "callmail" function works perfectly for word documents but I don't know if it is different for an excel file.

Code:

Private Sub Command154_Click()
On Error Resume Next
Dim appExcel As Excel.Application
Dim wbook As Excel.Workbook
Dim wsheet As Excel.Worksheet
Set appExcel = New Excel.Application

[Code] .....

View 4 Replies View Related

Modules & VBA :: Copy Access Data Into Excel Template In Read And Write Mode

Jun 20, 2015

How to open a pre filled excel template in read and write mode from msaccess vba and insert data from msaccess tables into specific columns in excel.

Rename the tabs in excel sheet based on a specific column in the access data.

Each row in the table will go to a seperate tab in excel.

Save the excel template after populating the necessary data into different tabs.

View 3 Replies View Related

Modules & VBA :: Modify Working Code - Export Query And Update Worksheets In Excel Template

Mar 12, 2014

What I want to do instead is open an existing .XLSM wokrbook delete or update the 7 sheets it creates and replace them with the new query results from access.

I love this code below because it works really well but now I have a new requirement. I have a workbook that has a "dashboard" sheet that looks at the sheets from acccess and summerizes the data. So, I'd like Access to open that "template" excel workbook and delete the old sheets and put in the new ones..The required sheets to keep are called "Metrics", "Validation" and "Mara"

What I was trying to do for the past few hours was another work around which was to have Access run this code, then excel run some code to import the "dashboard" formulas but I can't get it to copy to another workbook because it links to the OLD workbook..Here is the working code that needs modding:

Code:

Option Compare Database
Public Function ExportAdvanced()
Dim strWorksheet As String
Dim strWorkSheetPath As String
Dim appExcel As Excel.Application
Dim sht As Excel.Worksheet
Dim wkb As Excel.Workbook
Dim Rng As Excel.Range
Dim strTable As String
Dim strRange As String
Dim strSaveName As String
Dim strPrompt As String
Dim strTitle As String
Dim strDefault As String

[code]...

View 3 Replies View Related

Remove Letters From Numbers

Dec 15, 2005

Hello again.

I’m trying to find a way in a query to remove any data except for numbers

Examples


NET 90
NET 10
NET 10 DAYS
NET 110
NET 15


Any help would be greatly appreciated

Corey

View 5 Replies View Related

Criteria For All Numbers Or Letters

Jan 18, 2008

Hello, I am a new member...thanks for any help anyone can provide.
I have a table with a column in which the fields will come in with various lengths and combinations of characters. I need to look up certain values from this field, and assign a value to another field based on that. For example if field1 begins with an "H*", then field2 will equal "INS".

My problem is that I need to write a statement saying something like if field1 starts with "H" and is followed by only numbers for any length, then assign field2 "blank"

so I cant use "H*" because that includes letters...and Ive tried criteria like "H[!a-z]*" but cant get anything to work(i know the last example only looks at the space after H) Does anyone have any ideas?

View 4 Replies View Related

How To Show Letters And Not Numbers

Sep 16, 2015

In the attachment. I want to show the letters and not the numbers that is stated in step 4. How will I do this....

View 1 Replies View Related

Queries :: Order By Letters And Then Numbers

Dec 31, 2013

I have a column containing an id that consists of the first two letters of a weekday followed by an incrementing number. For example, for Monday, I have "MoA1" "MoA2" "MoA3" ... "MoA11".

The problem is that when I sort my list, it is ordering it: "MoA1" "MoA10" "MoA11" "MoA2" "MoA3" etc. Currently, my order by property is set to

MID(TABLENAME.SORTFIELDNAME, 3, LEN(TABLENAME.SORTFIELDNAME))

View 3 Replies View Related

Setting Field Criterias (numbers And Letters)

Feb 13, 2008

The data that is to be entered in the field "Rank", is either RK1, RK2 or RK3

How would I make the validation in a table make the user enter the format RK(Number), with only numbers from 1-3?

Thanks!

View 4 Replies View Related

Identifying Words Spelt Incorrectly With Numbers Instead Of Letters

Nov 15, 2007

Hello

I have some data I need to clean.
It is in an address field the majority of the data is fine but there are some records with words that have been spelt with numbers:

2 9RAMBLEGATE
2 Forest V1EW
2 KEM8LE Close
2 BELLEMOT4TE Road

Is it possible using a query to find instances where this has happened so I can flag these?

Any help would be greatly appreciated

Tim
:confused:

View 7 Replies View Related

Push Data From Current Record In Form Into A New Excel File, Using Excel Template

Sep 10, 2007

I searched the archive and didn't find quite what I was looking for, so..

I have an Excel 2003 spreadsheet work-in-progress being used as a template (developed by others) to prepare project cost estimates in a complex regulatory environment. We are 'modelling on the fly' for a number of projects until we are comfortable with the estimate model, after which time I intend to incorporate our 'stable' estimate methodology into Access. Meanwhile, I am 'stuck' with the Excel spreadsheet.

I have a project tracking database (Access 2003), and I want to be able to track my estimates. I do NOT want to embed my spreadsheets into the db, just a filelink. There can be more than 1 estimate per project.

Ideally, the user should be able to define a project in the Access db (or select one already defined) and click a 'make estimate' button, which would generate a new Excel file in a predefined directory (based on the present version of the .xlt file), give it an appropriate filename (based on the Access ProjectID and estimate sequence number for that project if there were others already), open up that workbook in Excel, and then autopopulate some cells based on information showing on the original form in Access!

A separate button for 'Open existing estimate' will eventually be required, but I think I could do that if I can get someone to walk me through the steps required above.

I am somewhat familiar with vba in Access, but am an absolute rookie when it comes to excel.

Edit: I left out that I would also add an appropriate record to a table like tblEstimate which would contain the link(s) to the estimate(s). This table will obviously contain a FK to tblProject

View 1 Replies View Related

Queries :: Turning Excel Formula To Criteria In A Query

Jan 7, 2015

=TEXT(TRIM(MID($A2,5,2) &" "&LEFT($A2,3)&" "&MID($A2,8,10)&" "&RIGHT($A2,2))+0,"dd/mm/yyyy hh:mm:ss ")

Is it possible to replicate the above formula from Excel into criteria in an Access Query.

Assume the cell reference $A2 is a text field which has customized date info in it but is not a recognized date format.

View 7 Replies View Related

Queries :: Combination Of Letters And Numbers = Data Type Mismatch In Criteria Expression

May 16, 2013

I am working on a fairly ancient manufacturing database that identifies items using a combination of letters and numbers. The usual format is to have a letter (which suggests something about the item type) followed by a sequence of numbers.

I am trying to write a query that looks up all the records beginning with a prefix or arbitrary length, strips away the text, and finds the highest number.

Code:

SELECT Right(LocalID,Len(LocalID) - 1) As IDSuffix
FROM tblItemIDCrossReference
WHERE Left(LocalID,1) = 'T' AND IsNumeric(Right(LocalID,Len(LocalID) - 1)=True)

This query produces the error given in the title of this thread, whilst the following works:

Code:

SELECT Right(LocalID,Len(LocalID) - 1) As IDSuffix
FROM tblItemIDCrossReference
WHERE Left(LocalID,1) = 'T' AND IsNumeric(Right(LocalID,5)=True)

This related query also works and shows a load of -1s and 0s correctly

Code:

SELECT Right(LocalID,Len(LocalID) - 1) As IDSuffix,
IsNumeric(Right(LocalID,Len(LocalID) - 1)=True) As Alias
FROM tblItemIDCrossReference
WHERE Left(LocalID,1) = 'T' AND

But once again shows the error message when I try to filter the field Alias to -1 or 0 only through the right-click menu.I have tried piping Len(LocalID)-1 through CLng, CInt, Int, CDbl and CSng; this changes the error to 'Invalid Use Of Null' I have also tried removing the '=True' from the IsNumeric() term.

View 2 Replies View Related

Excel Template To Import Into Access

Feb 5, 2006

Hello All,

I thought i would ask this question an how to import data from excel into access.

I am using sage to invoice clients and i can output this in to excel format then i would like the best and simplest way to pull the data though into access.

The data file has column headings at the first row ie A1 though to J1 and after this is the data, i have a table setup with the relivent table names, I had thought how easy this would be, but as always this was not the case.

The outputed excel sheet will vary in location so i think i need a diolog box to select the file and then some how link it to extract the data, is this right and if so how can it be achived.

Any thoughts

Alastair

UPDATE
**************************************Resolved Thanks to Pat Hartman *************************

View 2 Replies View Related

Modules & VBA :: Filter On Subform Not Turning Off

Feb 2, 2015

I'm filtering a sub form in datasheet using an option group frame, and the on click does exactly what I want it to do.However, Once I'm done filtering, I can either set the filter to all ...

Code:

Case 1
Forms!frmmain!frmtoplinelist.form.FilterOn= False
Or click a Button "Remove Filter"

Code:

Private Sub btnClear_Click()
Me.frmTopLineList.Form.FilterOn = False
End Sub

(I've tried both references to the subform too... "Me." and "forms!etc. However, the subform filter isn't switching off.

View 4 Replies View Related

Looking For Example Of Exporting Query Results To Excel Template

Jan 27, 2008

Hi all,

I'm not very experienced with Access. I'm looking for a very simple sample of how to export Query results to an Excel Template document. Especially to specific cell references. Would anyone have one to share. I'm trying to get my head around the concept first.

I may have to use Access 2000 and later versions to run the database, so do I have to cater for both DAO and ADO?

Thanks in advance for any help
rgs
Ginny

View 2 Replies View Related

Export Access To A Copy Of An Excel Template

Oct 11, 2005

Hello, I am here back in the forum because would like some help to define a code.

Meloncolly has already helped me but I think that I have mixed up two pieces of code and cannot find the solution.

I need to export my access data to a document in excel.
I will select the recorset of the data to export, using a combo box on a form named MENU.

The excel file is a template named MASTER. Before the recorset is copied, I need to make a copy of the template via code. The copy will be the document into which copy the data. Once the data is copies, I would like an input box asking users if they would like to save the new excel file and be able to name it with the name they will type in the input box.

My last problem is that the database is used by 20 users and what happens if are all exporting to the template? Will this automatically name itselft, MASTER1, MASTER 2, etc?

This is the code that I am using and tried to put together. It does copy the data into master 1 but leaves the template opened. It also tells me that there is something missing.

THanks:

Private Sub ExportToExcel_Click()
Dim myid
Dim obj As Object
Set obj = GetObject("C: estMaster.xls")
'Dim mypath

obj.Application.Visible = True
obj.Windows(1).Visible = True
obj.Application.ScreenUpdating = False


myid = Me.[MyCombo]
'grab the three field values from the table
Dim mySSN, myFirstname, myLname
mySSN = DLookup("[WESSN]", "[MASTER]", "[id]=" & myid)
myFirstname = DLookup("[WEFN]", "[MASTER]", "[ID]=" & myid)
myLname = DLookup("[WELN]", "[MASTER]", "[ID]=" & myid)

'open excel and the required file
Dim appXL3 As Excel.Application
Dim blnStartXL3 As Boolean

On Error Resume Next
' Check if Excel is already running
Set appXL3 = GetObject(, "Excel.Application")
If appXL3 Is Nothing Then
blnStartXL3 = True
'Else
' We have to start Excel ourselves
Set appXL3 = CreateObject("Excel.Application")
If appXL3 Is Nothing Then
MsgBox "Can't start Excel", vbExclamation
GoTo exit_handler
End If
End If
With appXL3
'.Visible = True
On Error GoTo Err_Handler

'open the excel file

'mypath = "C: estMaster.xls"
'.Workbooks.Open mypath
.ActiveWorkbook.SaveAs "c: estMaster1.xls"
.Sheets("Data").Select
'enter variable values into cells
.Range("B6") = mySSN
.Range("B3") = myFirstname
.Range("B5") = myLname

'do some other stuff

'save the workbook
.ActiveWorkbook.Save
'close it too
.ActiveWorkbook.Close
.ActiveWorkbook.Close

'exit and tidy up
exit_handler:
On Error Resume Next

If blnStartXL3 = True Then
'We must quit Excel
appXL3.Quit
End If
Set appXL3 = Nothing

Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation
DoCmd.SetWarnings True
Resume exit_handler
MsgBox "The tables have been successfully exported to " & txtExportFile & "."

Exit Sub
End With

End Sub

View 3 Replies View Related

General :: Copy Query To Excel Template?

Jun 18, 2014

i have an excel spreadsheet in my documents (H:My Documentsbreakdown.xls)

i have a query called Qry_Breakdown, a form with a button named "Update Breakdown"

How do i get the details created in the query, to appear in my breakdown sheet, starting from "A2" as i have several headers matching the query

View 9 Replies View Related

Excel Template To Access Conversion / Storage

Apr 30, 2013

What I have now is an excel template (with ~12 worksheets) that many regional offices use to enter in some lease data, from which the excel sheet creates a rental schedule and does a whole ton of calculations on that data. Some are NPV calculations, some are yields etc etc.Eventually, I would like to:

1) Enable users to fill out one of these templates, and save the data to the database (Just the inputs? All the data? My reading suggests just the inputs)
2) Use the database to produce one of these templates for any lease in our system (shouldn't be hard, from what I've read)
3) Sum up calculations from this template for many records (eg. if a tenant has many leases, what is the NPV of all of those leases, or what is the total NPV for all tenants)

I have the inputs (from Access) I will have no problem using them in the excel version, but does it make sense to use Access given that I may need to somehow be switching back and forth to get the info I want for my various reports? I am very comfortable writing macros in VBA for excel, so if that's the solution, that is no problem. I assume what I need for #1 and #2 is a macro to arrange the inputs from the excel sheet into a format that can easily be dropped into access tables and vice versa.

View 6 Replies View Related

Modules & VBA :: Turning Off (specific) Error Messages

Dec 2, 2014

I have a query that I run from VBA which basically resets a field on every row, where it is set, back to it's original default value - this works!.However I get 2 pop up windows, 1 of which is no use and the other is worth keeping.

The first Window says "you are about to run an update query that will modify data in your table" - I want to suppress this one.

The 2nd more useful one tells me I'm about to update (x) rows. I want to keep this.

Obviously if I put DoCmd.SetWarnings False in my code all warnings are switched off so how, if at all possible, do I only display the 2nd more useful message?I presume I have to trap the error, but how do I know what the error number (??) is for the first message?

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved