I am making an inventory system for a consultant who buys products and sells them at vendor shows. I have a simple table in Access that holds the quantity of each item the consultant has. I would like to be able to use Excel as a front-end interface for manipulating this data (through userforms). How might I be able to accomplish this? I'm fairly new to Access.
View 4 RepliesI have designed an excel user form which can be used by managers to enter the delay hours for the employees which gets saved in excel. The form works fine I need to share this form to the managers. How can I do that ? I have individual worksheets for each managers and as soon as they enter the details in the user form the data gets stored in their respective sheets.
So how can I share this such that all the managers should be able to access it.
I was thinking if this is possible through web by saving my file as Html..
Has anyone successfully linked up Excel with SQLite as a backend, some code examples would be appreciated? I'm currently using an .mdb successfully as a backend, but am running facefirst into some Access limitations. I would like to swap out SQLite for the mdb. Assume unfriendly IT department and that I won't be able to install an ODBC driver that all the users would be able to use, so I would prefer to direct connect like I do with the mdb.
View 9 Replies View RelatedWhat is the best way to protect a Data sheet, but still be able to use a FrontEnd Userform.
View 4 Replies View RelatedI 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.
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 an excel file which is shared by multiple users. I would like to make one person to have read,write or edit permissions and rest of the users to have only read only permissions.
View 1 Replies View RelatedI am trying to access another program via Excel VBA. The idea is that, we fill out out time and attendance sheets for work on excel workbooks. But, we have to go into a terminal program to enter this information so it can be uploaded to whereever it goes.
I am able to get the Shell command to work. But, it will be riddled with sendkeys commands and application.wait. I am sure that this is not the best way to program something like this. I believe I read that for this to work that it has to be COM compliant. I don't know. The program is a Java based terminal program.
This works for me
Code:
ShellExecute 1, "Open", "C:UsersPublic******lib***_core.jar"
But this does not
Code:
CreateObject "C:UsersPublic******lib***_core.jar"
It gives me a Run-time error 429, ActiveX component can't create object.
I'm running an Excel program such that the user inputs a variable. The program opens an Access database which is linked to the Excel sheet where the variable is stored. Access then runs macros containing queries based on that variable. I've noticed that the link to Excel is slowing performance and I'd like to speed things up, some code that would allow me to plug the variable into Access for use in the query without having to link to the Excel workbook.
This is a piece of the code I use in Excel which runs the Access macro:
Code:
For iter = 1 To bottom_row - 1
If IsEmpty(MyMacro(iter)) = False Then
Set AccessApp = CreateObject("Access.Application")
With AccessApp
[Code].....
Everything works well as-is; it's just slower than I'd like. Due to other constraints I'd rather not go into here, I really am forced to use this kind of Excel/Access interaction. In other words, the user must use Excel to run an Access query.
I am trying to import database records into Excel, but i keep getting an error "run-time error 424" on the code below.
It is probably something simple, but i havent tried importing from Access to Excel before.
The code below should clear the data from "Existing" and then copy the data from test.mdb into the same sheet.
The error occurs when opening "Data Source"
Code:
Private Sub Workbook_Open()
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim dbCommand As New ADODB.Command
RowCount = Worksheets("Existing").Range("A" & Rows.Count).End(xlUp).Row
Worksheets("Existing").Range("A2:V" & RowCount).ClearContents
[code]....
I am trying to open an access database from excel by clicking a button. I have assigned the following script and modified it for my own use as according to the microsoft kb.
Private Sub CommandButton2_Click()
'Opens Microsoft Access and the file nwind.mdb
Shell ("c:Program FilesMicrosoft OfficeOFFICE11MSACCESS.exe T:TSD - UKProjectsSteve's ProjectsT3FCRs.mdb")
'Initiates a DDE channel to Microsoft Access
Chan = DDEInitiate("MSACCESS", "system")
'Activates Microsoft Access
Application.ActivateMicrosoftApp xlMicrosoftAccess
'Runs the macro "Sample AutoExec" from the NWIND.MDB file
Application.DDEExecute Chan, "ImportData"...........
I use Excel 2003 and am trying to import information from an access database. The recordset I am looking for is based on Cell B3 on a worksheet named Import.
Is there a way that Excel VBA Can do this?
The recordset contains text in the standard of Memos, Will I be able to import the entire Memo?
I have an acess database where I export the data into a blank excel workbook and then copy and paste the data into a report file. My goal is to automate this process and my first thought was to add a button into the access database and have it export to an excel template, but using/editing the access database is not an option, so, that leaves me thinking about automating this process from excel. Is it possible to export data from an access database into cell A1 of a tab?
View 5 Replies View RelatedI have created one Info path solution to gather information from different locations to one hidden Access database. And in last month or two I already have more than 30 000 entries in database. On other side I have created 2 excel workbooks, one for purpose of report that has all connection done through VBA, user just opens it, clicks button and gets report and one workbook that is for other set of users so they can change 2 specific columns of data, everything else is locked. When one department fills value it should fill based on gathered information, and clicks save that cell or cells are being locked.
At first I did on worksheet change event, when user changes a cell (enters information) it triggers worksheet_change event and saves that value in database and colours cell green. But, then users start complaining that when they use copy paste or the use fill paste option (just pull value from one cell to others) it does not save value. Ok ... that is because worksheet_change event does not trigger in situation when copy paste in any form occurs.
So how to solve it ... when pulling data in excel I pull identical table (data) in other hidden worksheet. When they change what they need to change, they click button "save to database" i have created and there is a macro that makes other sheet visible, goes through the records and saves where cell value in table that is being entered is different then same cell in hidden table.
Problem is that sometimes my code works without a problem and sometimes they get an error. I cannot figure out when. When a lot of users use that excel file, for some users it is a read only file, and as I presume that is ok, because macro works in any case and data are stores in database so excel file itself does not have to be saved.
Here is code.
[Code] .....
I know that I can import data from access into excel. What I am looking for is a way to have the data linked so that any time that a change is made in access, that change will be reflected on my Excel worksheet.
View 1 Replies View RelatedI have about 180 Excel files (each one with 51 columns and around 30,000 rows) that need to be exported to an Access table.I'm using the routine below which is extremely time-consuming. I'm sure there is a better way to export an excel file to an Access table.
Sub ExportHistData()
Dim rst As Object
Dim cn As Object
Dim i As Long
Dim lstCell As Long
Application.ScreenUpdating = False
lstCell = [a65536].End(xlUp).Row
If lstCell = 1 Then Exit Sub
[code].....
I have the below code that will do a data dump of an access table into excel based on an SQL query, is there a way to do a select statement that can match against the data in column C on the spreadsheet.
like:
"Select * From Bsmart where Serial_Number = ##[EXCEL COLUMN C1:Lastrow]##
Code:
Dim bkNew As Workbook
Dim cnt As ADODB.Connection
Dim rst As ADODB.Recordset
Dim stCon As String
Dim SQl1, SQL2, SQL3, SQL4, SQL5, varSQl As String
stCon = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
[Code] .....
I am creating a Payroll Database. It is nearly complete except for the federal and state withholding tax data. I want my db to look up the data which I have saved in an excel workbook. I need the lookup to be for marital status and # of exemptions. There are a total of two variables for each table. 1. How much the gross pay is (ex. between $175-185) and # of exemptions (ex. 3 exemptions).
View 2 Replies View RelatedI currently use a function very similar to this one with a SELECT query to return a field to excel. I have modified this into an UPDATE query to update a field.
I am getting errors at this point:
Code:
If adoRS.BOF And adoRS.EOF Then
DBNoteUpdate = "Value not Found"
Else
DBNoteUpdate = "Action Confirmation"
I believe this area of code was to trap the error that occurs when the lookup field name was not found in the table. I left it in because I still want to trap if the record I am trying to update is not found in the table.
Here is the full code:
Code:
Public Function DBNoteUpdate(RecordNumber As String, _
UpdateTableName As String, _
UpdateNote As String) As Variant
Dim adoRS As ADODB.Recordset
If adoCN Is Nothing Then SetUpConnection
[code].....
The code does work, in that the record is being sucessfully updated by the SQL statement, but its erroring at the point I described above with the error:
Code:
Runtime Error '3704':
Operation is not allowed when the object is closed.
how 'adoRS' is closed at that point because the call to close is right at the end of the function?
I have an access database that runs some excel subs. If the excel sub doesn't meet a certain criteria, I want to close the excel workbook and close the access database. Most google searches yield how to close excel from access but I need closing access from excel. I was thinking that if the "detonate" criteria was met, I could pass a variable over to access and terminate that way....
If x 5 then
thisworkbook.close
myaccess.accdb.close
end if
or
if x 5 then
appAccess.application.run "Self-Detonate"
thisworkbook.close
end if
I want to transfer data from excel to access using vba, where 1) I have excel sheet with Header at top row and some data below it. 2) Once I run my macro access table will get created and My excel header row will be my access field names and data will be accordingly.
View 1 Replies View RelatedI am doing data entry spreadsheet here where the information gets manually input into a spreadsheet and then when the user clicks a button, it transfers it to a the Access database.
This was done by my predecessor but i had to modify it which has now given me a "Run-time error '3061', Too few parameters. Expected 1".
My code is below:
Private Sub SaveandExport_Click()
Dim db As Access.Application
Dim TB As DAO.Database
Dim Intro As String
Dim Tabelle, GName, VarList, TaName
Dim i As Integer
Set db = CreateObject("Access.Application") ' create Access object
[code]...
I have an excel macro that works of off data that we pull from an access database and the only part being done manually is editing the query and importing the data to excel
What I would like is something in excel VBA that pulls the data from access. my thought is in access i would have a basic query that has the fields we need and excel would have a form to filter those fields as needed.
One of the fields is Date and another Location where we filter date ranges and locations. we currently go in to the query and filter the date range by hand and the locations we want by hand.
I am not that great in access so have not created any macros to pull the data from excel before so don't know what I am doing.
I have a procedure in Access, MySub, stored in Module1.
To call it from Excel, I use the following:
Code:
Dim AccessApp As Access.Application
Set AccessApp = New Access.Application
With AccessApp
.OpenCurrentDatabase Filepath:="C:My location", Exclusive:=True
.Run Procedure:="MySub"
End With
Supposing for argument sake, there is another procedure also called MySub saved in Module2 in Access.
The above code will no longer work as it won't know which MySub to call.
How can I specify the correct one to choose? Of course I can rename the procedure in Module2 to something else.
I currently have a workbook that has VBA functions that calculates values by looping through large amounts of data in a few large excel sheets. I want to transfer the data from these sheets into Access and then bring data into the VBA code via an array from the data in Access rather than the array being populated from the data in excel as it currently is. How to create the connection. The access DB will be situated in a public directory on a server. How to create the connection string and then how I call the data from Access? I have just downloaded Office 2013.
View 9 Replies View RelatedI'm having a bit of trouble with writing a code which will import a table that i have in Access into excel.
The name of the table is "101_Yearly_Rows" and the only column that i want is the "Tax" column.
Sub ImportYearlyRows_CalculateTax()
Dim cnt As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim stDB As String
Dim wsSheet As Worksheet
Dim lnNumberOfField As Long, lnCount As Integer
Set wsSheet = ThisWorkbook.Worksheets("Sheet1")
[Code] .......
When i get to the line which begins rst.Open "SELECT Tax FROM..." i get an error message which says "Application-defined or object-defined error".
I have an Excel report that I'm wanting to know if it can be automated. Basically it starts off in Access, I have a few queries that run via an Access Macro, once the queries are done there is one query via the macro that exports a file of data to excel. I then have a 2nd excel file that is a pivot table that I refresh from the exported Excel file of data from Access. Once refershed, save it, upload it to a Sharepoint Site and send out an email via Outlook. Basically I'm wanting to know if this can be automated to were it runs at like 6am or something before I even get on my computer. I'm not a vba expert, I can do some basic stuff but what takes most people an hour or so to do would take me a day or so. Also would this be easier to just have the pivot update directly to the Access DB?
View 1 Replies View RelatedThe current code is not working.
View 1 Replies View RelatedI have a large excel file with lots of data that it takes a long time to manipulate in excel. What I would like to do, is to import it in access, do some manipulations, and export it in excel again.
All these have to be automatic, so that the user does not do anything. Is it possible to do so through some macro?
I have a macro that creates a table in Excel. The problem is the table will be MUCH bigger than 65,000 lines. So what I'd like to do is once the table gets to a certain size append the data to a Access DB. (Or whatever works)
Then continue running the macro.
Clear Table, Rinse, Repeat.
The "Output" Page of the Macro looks just like the DB would.
For example if I only wanted one instance I could run (part of) macro. Save Excel.
Open Access
Do an ImportTable