I have an Excel spreadsheet that I have developed into an over the top flight calculator/scheduling tool. I am using Excel because developing this prduct into a stand-alone program would be absolutely murder to get approved for use on my network.
Since Excel and Access has already been approved, I began coding this project there.
Now that my calculator functions are nearly complete, I am beginning to develop a database of airfields and other misc. flight data that I and others on my network can see and manipulate. To make my calculator funtion at 100% effectiveness, I will need to use VBA to open the Access DB and pull out specific data. I will also need it to open and display some Access pop-up forms that I have just developed. Does anyone know what code I would use for excel to display and retrieve Access' information?
I've just suceeded writing some code that collects data from one spreadsheet and places them in another sheet from where it is transferred to an Access 2007 table. It all works fine apart from one little thing: I can't find a way to close down the empty Access window after the data have been transferred.
I am at a point where I am running out of RAM for the 2BG allowed in 2007. Does anyone know if Microsoft has a later version of Excel that will allow me to grab onto 3GB? My file sizes are close to .5GB. It seems that I am always pushing the limit of what Microsoft can provide.
I have a CSV that I have a excel macro created to do some formatting and then export the data into Access. that part works. I also have it checking for duplicate entries and skipping them. The problem that i run into is that not all my duplicates are truly duplicate, there have been updates and now i need to update Access. I have 2 fields, my primary key which is what I'm primarily matching on and then I'm checking if its been updated by checking another field "Last Changed" If this field doesn't match then I need to update the record.
At this point I'm trying to use a SQL UPDATE and I now believe that i have it formatted correct but when it gets to a record that needs to be updated I get an error: Run-time error '-2147217887 (80040e21)': The changes you requested to the table were not successful because they would create duplicate values in the index, primary key or relationship. I'm not sure what I'm doing wrong. the only other thing i could think to do would be to check each field to see if it has changed and just run the query against that but I feel like i would run into the same issue. This is how I'm connecting to Access:
Set cn = New ADODB.Connection cn.Open strConnect Set rs = New ADODB.Recordset rs.Open strTableName, cn, adOpenKeyset, adLockOptimistic, adCmdTable And this is the Query I'm using:
The SQL code sits in a loop with an IF so it shouldn't execute every time and I plan on once i get this working to revisit this code and try to optimize it but that is not a priority currently.
I want to write some code that runs everytime the workbook is initialized. I found out that the code should be placed in the following "Private Sub":
Private Sub Workbook_Open() 'My code End Sub
I know that this code should be placed in the Private Module of the Workbook! I just can not find where is the Private Module of the Workbook in Excel 2007... =/
I need to create a new query that pulls data from a 2007 Access database into Excel 2007. When I invoke the 'From Microsoft Query' option after 'Data/From Other Sources', and then select 'MS Access Database*', the only file type presented is '*.mdb' . However, I need to pull from an '*.accdb' file type. I have both Office 2003 and Office 2007 installed.
When adding functions to the quick access toolbars in Excel 2007 sometime they do not have an associated icon with the function. The icon shows as a green circle. How to change the icons and also to have a selection of icons like you have had in previous versions of Excel?
In Excel 2007, I have a tables linked from Access 2007. I frequently delete and import a new set of data into Access, then refresh the Excel file. Most of the time this works, but I've noticed sometimes one column doesn't refresh (even though I can see it in Access). If I modify the field name in the Access query, then refresh the table in Excel the newly named field is added in the in the last column of the table with current data. The column that wasn't refreshing stays the same.
When I originally created the link in Excel, I added various columns with formulas, but do not edit the columns that are imported from Access.
I am attempting to create a template that will utilize data from an Access table, and allow the user to select the parameters in the Excel file. The amount of records in this table is slowing down the Excel file, and I would like to lessen the amount of data that is imported based on the user selection.
The Access table will have three columns:
Part Number|Line|Catalog Code
I would like to import all three columns, but allow the user to select the records imported based on the catalog code.
The users do not have read or write access to the sql tables, so I run an Access query at the end of each day to create an Access table that they can access.
I am familiar how to perform the task of bringing in an access table into excel, then using a data validation filter to control the data set in Excel 2003.
I am Generating Excel file with Macro using my asp.net (c#) application.
I am able to generate Excel file in development environment, but in Production it gives following error: "Programmatic access to Visual Basic Project is not trusted Line: Microsoft Office Excel"
I did googling a bit and found that I have to open Excel file physically make few security related changes in macro as below.
1. Open the Office application in question. On the Tools menu, click Macro, and then click Security to open the Macro Security dialog box. 2. On the Trusted Sources tab, click to select the Trust access to Visual Basic Project check box to turn on access. 3. Click OK to apply the setting. You may need to restart the application for the code to run properly if you automate from a Component Object Model (COM) add-in or template.
Can i do above changes at runtime (using some code)?
I have an Excel file ( named "Classes.xsl"), that has a worksheet (named "RawData") that is layed out something like:
Name Dept Class John 0547 Class 1 Jane 0368 Class 1 Jim 0368 Class 2 Sue 1235 Class 2
I am trying to get an Access-Like report without utilizing Access. Is there a way to generate a report similar to below using Excel as the data source (could be mail merge, perhaps a macro with a printout) I am not sure which way to try and was hoping someone could point me in the right direction...and possibly provide an example.
Class EnrollmentClass 1John 0547 Jane 0368Class 2Jim 0368 Sue 1235
I need a front worksheet with either buttons or tick boxes that will list different options for a machine
Once a tick or push button is activated a hidden block of text related to that specific tick box needs to be selected and placed onto a final print out sheet (allocation)
when futher boxes have been ticked I would like all the information blocks to build up on the final print out sheet.
I'm trying to build a query which matches two tables which say has a number like A#### , I want the query to give me the A number's which do not belong in the second table but which do in the first table. i believe i need an SQL query to do this?
The size of the table I'm importing will change, so i would like for the code to not matter on size. Also its, gonna be large too. The sheet will always be the same and the column headers will match for excel and access.
I need to compare data on a spreadsheet to data that resides in SQL database. I have never done this before so before I start I wanted to ask a general question, what is the cleanest, simplest, way to go about this. I read that ADO is a good way to access SQL. I understand that ADO is one of the main components of MS universal data access specifications so it sounds like the right approach (and that ADO is replacing DAO).
In terms of application, all I want to do for now is pull data from SQL and write it to a worksheet in the Excel workbook. Other macro's will then operate on this data. Later I will want to write data back to SQL but I want to focus on the "get" part first. Are the statements SELECT; INSERT; UPDATE; DELETE examples of ADO coding?
Conceptually, the SQL team provide nightly tables of data; ADO retrieves some of that data by selecting it; The selected data is written to a worksheet within the workbook. Is this a good approach?
I want to create a log of everyone who opens a particular workbook. I'm using Excel 2003. I found this macro, and created a worksheet called "Log", but I've opened the workbook several times and nothing appears on the Log sheet.
For sales tax reporting we need to know the state, city, county district amounts. This would be simple if we could just sort by county/city, however we cannot because the state tax piece only only shows the word "UTAH" it does not give a city or county name. So we must sort by invoice #. This would be ok if we only had a few invoices in each city such as Salt Lake but we have have 40 or 50 invoice numbers in salt lake.
I need to find a way to keep all of the tax pieces that belong together, and subtotal by city/county. I thought if I had a formula that could change the state name "UTAH" to reflect the city for that invoice. This is the case for all states. Each state is 10,000 or more lines. I need to somehow grouping the information I need. I used the pivot table approach. However, since I must sort by invoice I still have 10,000 lines. I want to sort by invoice to get all the pieces then sort by county city. Any ideas of how I can do this? something in Access perhaps? a formual in excel perhaps?
i have an access database and is using excel to get the required data from the access database. I am basing on department to filter out the required data into excel. Below is the VBA code i use
deptClause = Worksheets("Records").range("C1").Value If Not deptClause = "All" Or deptClause = "" Then requestClause = " WHERE Department='" & deptClause & "'" End If
Queryline = "Select * from FailureQuery" & requestClause
the problem is i got one selection "All" in worksheet "Records" in cell "C1" and i cannot get this function to display all the data from the access. however when i choose a particular department it will show fine.
There are many examples and aspects to compare these 2 products but I just want to point one little difference which is quite crucial and interesting.
Generally if you use small amount of data - 1 Worksheet / 5000 rows / 20 columns you can use Excel without bothering about the execution time, queries and work fast and convenient with it.
The point on Excel is that in 1 Column/Row you can differently Format the data(cells). For example - format as Number or Hour the cells in Column B depending on the data in other columns. That saves you from making 2 Columns - one for Numbers and another one for Hours. This helps you to save 1 of the columns when the data structure in other column is the same.
In Access (and generally all SQL DBs) this is not possible.
I have the following code set up and every works except for PaperSize and Margins. Is there a standard VBA code reference available somewhere that addresses Access to Excel operations? Thanks
I was wondering if there was any code that would 'lookup' a value in a MS Access database in the same way that a Vlookup formula looks up values in tables in Excel.
Even better, if there was a fuction already written to do this.
I am trying help protect a project from all my colleagues that have a version of password breaker that is actually an add-in. I have already managed to disable or grey-out all of the other commandbar options that I don't want them to have access to during their use of the model but can't figure out how to disallow them from accessing the "Add-ins" option from the "Tools" menu. I can, of course, disable the entire tools menu but don't want to do that yet.
I would really like to be able to have a function in a workbook that can run methods (for example Show or Hide) on a UserForm inside an add-in like this:
Public Sub Test_Addin() frmTest.Show False End Sub
where frmTest is a UserForm inside the addin. After the add-in has been added as a reference, I am able to access all of its functions/subs in this way. When I try to access a form inside the add-in from outside the add-in, I simply get an object not defined error. Looking more closely, if I type in "eRFTEAddIn." (which is the name of the add-in) and look at the possible options, the forms do not show up...although the modules and sheets do.
I have created few excel files from "X" login account and when i try to open it from the "Y" login account, it opens it in the read only mode. do you know how to access it from Y account in the edit mode or the write mode.
I have a userform that employees use to enter production data every day. Originally I had this storing the data in excel, but now I would like it to put the data in tables in access.
Here is some code I am trying, but it's giving me an error: "Run-time Error '3251' Current Recordset does not support updating. This may be a limitation of the provider, or of the selected locktype."
VB: ' exports data from the active worksheet to a table in an Access database ' this procedure must be edited before use Dim cnt As New ADODB.Connection
