I was looking for some help on a VBA solution. I would like to run an on open event for a scheduling workbook. It should open a logging workbook, write the %username% to the first blank line in A:A then save and close the logging workbook, leaving the scheduling workbook open and ready for input.
The following UNC can be used as the location for the logging workbook....
I 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].....
Is there some sort of vba code that will stop excel from creating a backup copy of the export file from access?
XLApp.ActiveWorkbook.Close SaveChanges:=True
This code saves and closes the workbook, but it then creates a backup copy. I would like to enter some code so it stops making a backup file.
I've been trying to make this work for hours and hours and am finally giving up. Most of this code was found on the internet and I've attempted to make it work for my project but I keep getting errors. I use VBA with Excel quite often but never with Access before. Currently I am getting an "Object doesn't support this property or method" error at rs.Findfirst.
I have an excel worksheet that mimics the access table with five fields, an ID field, lastName, FirstName, DeptID (int), Email. I want to search the Access table for a match on the email field, and if it doesn't find the match, to add a new record using values typed into the excel sheet.
Code:
Sub UpdateDB()
Dim cn As Object
Dim rs As Object
[Code]....
I have a large text file that is generated daily and want to import into MS Access as the end result.
First I need to reformat into the row format in excel rather than the format it is in. The issue is not all the segments are the same number of lines or they may have mutli message lines. The names with colon : after them I want to be field names in the table which I wish the text file to import into. Please, I am looking for assistance with this.
I have attached sample data of the text dump, and the name of the file is as you see but different date on the end. At the very end of the text file you will see the desired output which I will then import into Access
Also every new message begins with the dotted line and the date and time at the end.
I want to track daily sales of a shop with the tenders (Cash, Master, Visa)seperated.
Everyday there will be a file ctp.dbf from a folder YYYYMMDD (previous day date) which contains sales details.
I tried to use sumif commands and everything is working fine. everytime i have to open book.xls and from it I do a files>Open to open the ctp.dbf for the calculation to be done. is there a way where by i can open 1 file and everthing i calculated properly?
Also this book.xls can only do for 1 day how can i go about having the daily sales detail of the month (look something like sales summary.xls) or even year in 1 excel file?
attached is book.xls and sales summary.xls for reference.
I own a restaurant and have a inventory list. I track the price of a specific item in column E, and want to record the date i modify column E in column D. So, I change E5, I want D5 to say the date.
View 9 Replies View RelatedHow do I track my total weight loss in excel? Here is an example of what I am trying to do.
8/1/2008 228lbs
8/2/2008 225lbs
8/3/2008 223lbs
Total 5lbs
I would like use a formula that would subtract my absolute weight loss total day by day in August. Basically, I want the total "cell" to equate to my total weight loss in August. For Example after 8/3/08 the "total cell" should equate to 5 lbs. What formula do I use to do this?
Quick rundown, I have a sales background, so other parts (accounting, inventory, etc) are what I want to improve so Im not running around when an order comes in, where is it, do I have it in stock.
I know microsoft has some templates, any worth downloading and starting with, then move the data or add functions to an inventory and accounting template to start, any other templates recommended for an online business.
At my new job the CHT/CRT that we receive is three days behind what we are actually using. It makes it hard for me to keep track of my talk time, so I thought I'd use excel to track it. I can see the time I have been on each call through the day. I had to put it in 0:0:0 format to get it to average correctly. (I was trying to get it down to just seconds but that seems a bit complicated. I just then used "average=(a1:a30)" to average them together and it seemed to work a bit.
View 2 Replies View RelatedI've created a spreadsheet it is for tracking attendance etc broken down into percentages.
What I'm looking to do is if someone doesn't attend this doesn't get added to the attendance total.
I've attached a screen shot so you can see what I mean.
excel.PNG
We were looking at a list of about 100 FedEx tracking numbers, each 30 characters in length, in an otherwise blank Excel spreadsheet. All tracking numbers in column A.
I used the Conditional Formatting - Highlight Duplicates feature to find any duplicate tracking numbers. It highlighted numerous. We sorted the column smallest to largest to look at two duplicates back to back, but found that there were actually no duplicates. Excel was highlighting values that had the *first 25* characters the same, but it didn't matter what the remaining characters were. For example:
1234567890123456789012345XXXXX and
1234567890123456789012345YYYYY
were highlighted as duplicates of each other.
Excel only looks at the first 25 characters when analyzing duplicates (at least in this specific function). That in and of itself blows my mind. Tracking numbers seem like an abundantly common type of data, and to think that Excel would fail to analyze them in such a simple manner seems impossible to me.
It gets slightly more confusing though. If you back a character off the end of the values, it NO LONGER sees them as duplicates. So...
1234567890123456789012345XXXX and
1234567890123456789012345YYYYY
are NOT highlighted as duplicates while
1234567890123456789012345XXXXX and
1234567890123456789012345YYYYY
ARE highlighted as duplicates. So there's some funny business going on about values of the same character length. Maybe Excel first looks at character length before analyzing specific characters. If length doesn't match, it doesn't even bother to analyze specific character values. That seems like a very strange programming choice though.
I have created a 'price list' database in ACCESS. Then in EXCEL I created a pivot table which retrieves data from one of the database queries (the query was saved as a .dqy file).
I emailed the file containing the pivot table to a colleague who is on the same server. He saved the excel file on he desktop & renamed it. When I update the databse file on a shared public drive on the server, he is able to 'refresh' his desktop file successfully !!
I have sheet where I need to restrict access to a select group of individuals. I have a formula in a select case statement that runs from the workbook_open event and have hard coded the names of the individuals who can access the file. If they are not on the list, they are prompted for a password. This works OK, but I have sheet that is hidden and want to maintain the list of users there and update the code to loop through the values in a named range. I have a snippet of the current code below.
Code:
Select Case function_name
Case Is = "user1"
Run "enable_access_code"
Case Is = "user2"
Run "enable_access_code"
Case Is = "user3"
Run "enable_access_code"
Case Else
' User not authorized or opened while not on company network
' Prompt for project password
form_Password.Show
End Select
Having trouble opening an access file using this code. Using the following code
View 3 Replies View RelatedI have an xl doc in which one of the sheet's column A changes value every 1 hour...
What I would like to know is.. if there is a method in which i can copy these values from column A to a text file every hour...
The range of cells containing values in Column A also varies every hour.
Also, the old values in the text file needs to get deleted before the new values are updated every hour.
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.
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?
VBA coding for automatically saving an excel file as another file using the current date as part of the file name together with "32ga" as a constant add-in. I also what this macro to run at a particular time of the day let say 00:20hrs. The excel file i want to save as is always open . It has data that changes every 24-hrs.
View 1 Replies View RelatedI 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 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 Related