Document Stability
Mar 18, 2009
I am currently building a program in Excel, which can carry out numerious tasks. Having over 15 userforms, countless macros and several worksheets of caculations in the background, basically all geared towards capturing customer data, producing a application form and submitting the data to an ".mdb" file.
Now everything is going well so far with little in the way of problems and the document is 2.5Mb in size (users centrally access this Read Only). I'm conducting a daily Clean Project on this but I have a few questions...
1) how far can I push an Excel workbook in terms of size/content before it becomes unstable
2) are there any tips that can be offered for program stability
3) Has anyone built anything on this scale before
...the reason I ask is the program could potentially double in size over the next 6 months depending on the volume of application types it is able to produce. I want to make sure I have considered stability.
View 9 Replies
ADVERTISEMENT
Aug 27, 2008
[I have two Shared Workbook accessed by multiple users. Both workbook has multiple users adding around 100 rows everyday and each book has 3 sheets - one for Request tracker- one for Call Tracking (which has the VB code) and 3d sheet has name range for the drop down menus]
1)Does a shared workbook become unstable if it has macro /VB in it?
I have two separate shared workbooks that are accessed by multiple users. After I added the macro one of these workbooks crashed and I got a file corruption error. I recreated that workbook and for almost 1 week it’s holding without issues.
2)I am using macro to make the trackers a bit more user friendly for my hardworking team , Some people say using macro is not advisable because over a period of time all macros become Virus and the file gets corrupted- Is there any truth to this ? I don’t want my important files to become unusable.
3)Is there a limit on how many macro/ VB scripts I can have in a workbook/sheet without making it unstable?
4)On an average how many rows of information can such a workbook take without out becoming unstable (eg 1000 or 2000 rows)
5)I am planning to combine both workbook into one – which would mean the users accessing the book would be double around 10 and the entries would also be double close to 500 rows of information everyday. On both sheets. Plus I plan to have more macros to automate most fields to ease the load on my team - Is that ok or a bad idea? (I would have a auto backup enabled )
6)Finally for the same workbook I am planning to have a more friendly FORM template to further ease the load on my team – can a form template take such a load [ I plan to have the form in Sheet I and the data inputed from the FORM would go to sheet 2 & 3 – would there be any issues with that ? There would be few automation macros like static time & date.
Coming to Backup workbook option during save process
1)Can I password protect a backup workbook – would the backup work in such a case? I don’t want someone to access the backup workbook accidentally and delete or input any data there (remember multiple users)
2)Does a Backup work book needs to be in the same folder as the original workbook - can’t I save the backup WB in a different folder in the same drive?
This is the macro I am using on sheet 2 of both workbook currently
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
Target(1, 2) = Time
Target(1, 3) = Date
Columns("B:C").AutoFit
End If
End Sub
View 9 Replies
View Related
Feb 2, 2014
I am processing a fair few Invoices, which are being sent to me via e-mail as excel documents, very often they contain mistakes, a decent amount of mistakes. Usually the prices are wrong.
I keep track of every single entry on the invoice on my own document - Tracker, which I consider to be the superior/more correct document to the Invoice presented to me by my contractor.
Both of the documents have a reference number, which is a specific docket number, and horizontally, in the invoice, there is going to be a price for this docket. In my document, there is going to be a separate column for the total price.
Is it possible (I guess with VBA) to check for mistakes in the Invoice, but use the Tracker as a reference for this check.
Tracker has
columns
A - Name
B - Department
C - Date
D - Docket No.
C - Total price for the docket (calculation of E to Z)
E to Z - all smaller entries
Invoice has
columns
A - Date
B - Docket No.
C to E price for that docket, but it is spread, because departments are separated out, so each VAT account can be charged accordingly. I guess it is possible to do a separate column for the price, if it is easier to do a script that way.
Basically, I need to check if in the Invoice document, the price (C to E) for Docket No. (B) is the same as the price (C) for the Docket No. (D) in the Tracker.
I would like the wrong entries to be highlighted on the Invoice Document, so I can see straight away, that this needs attention.
Not always the price is wrong, sometimes the Docket No. is spelled incorrectly (Dyslexic contractor), hence the highlighting.
View 3 Replies
View Related
Feb 17, 2009
I need a script that will turn a excel doc into a txt doc. Thats the easy part. The hard part (at least I think it is), is I need it to be in a certain format and I'll do my best to explain that fomat below.
View 9 Replies
View Related
Aug 4, 2008
I have an excel document with a list of about 12,000 names/addresses. They are currently displayed like this:
A B C
1 <name1> <name2> <name3>
2 <address1> <address2> <address3>
3 <zip1> <zip2> <zip3>
...and I want to re-arrange them so they are displayed horizontally:
A B C
1 <name1> <address1> <zip1>
2 <name2> <address2> <zip2>
3 <name3> <address3> <zip3>
Is there any possible way of doing this quickly without copying/pasting 12,000 lines of text?
View 2 Replies
View Related
Oct 31, 2013
I am trying to sort a document alphabetically but cannot. I have two lines in the doc that previously had headers on them. I was able to remove them, but am now left with a row that has what looks like a backwards L in the last cell. The bottom line is also darker, but there is not a detectable border set. If I choose the rows that include these rows in my sort option, it is disabled. Sorting is available if I do not. I need to sort everything.
View 3 Replies
View Related
Nov 12, 2008
I have an excel document that is a form.
Can I use the data collected from this form to generate another new document?
I want the user to enter some data and hit "Create" and a new excel document with that data is created.
Also, the new document has some data in it already. So it has some data and it will get new data from the form.
View 3 Replies
View Related
Sep 11, 2009
Is there a way that I can have an excel document "autosave"?
Specificaly I would like: If a value in a specific cell's value is changed I would like the document to automatically save the document as the name of the value that is typed into that cell when the user closes the document.
I'm not sure if this is possible, but it would be great if there was some way to do this.
View 14 Replies
View Related
Jan 15, 2008
Version used: Excel 2003
I have a spreadsheet that everyone in my department uses. My boss has asked me to protect certain columns so that only I can edit them but leave others so that the rest of the depratment can use those.
I have tried locking the ones I am to use and unlocking the ones the others can use then protecting the document. This works well enough as far as it goes, however, it is combersome having to unprotect then reprotect it every time I need to work on it. Also, that method makes the drop down lists that we are using for easy sorting no longer function.
View 9 Replies
View Related
Feb 8, 2010
i need macro for my document.this macro need run on column "E" & "H" i need like this.
ex- E2 cell some text or number is there,H2 cell value should delete.E2 cell is empty H2 value no need to delete.
View 9 Replies
View Related
Mar 30, 2007
I was able to create a recordset of data and then save the recordset as an XML document. This XML document is aggregated data for a chart in Excel.
I need to hit the XML with ADO into a recordset from my select statement...
As you can see below, I think I found the Driver I need to use??? I am not sure that this is correct for Excel VBA ADO???
sSQL = "Select * From C:ADO.XML"
When I open the recordset.open sSQL, cnDB the error I get is this:
"The filename, directory name, or volumne label syntax is incorrect"
Set GetXMLDB = New ADODB.Connection
With GetXMLDB
.Open "Provider=MSDAOSP; Data Source=MSXML2.DSOControl.2.6;"
.CursorLocation = adUseClient
End With
View 9 Replies
View Related
Feb 22, 2008
I have an excel worksheet which has a lot of comments attached to individual cells. Is there a way to convert these comments into a microsft word document along with the contents of the cells to identify where they originated? Also Is it possible to convert just a selection of cells into word document rather than the whole worksheet.
View 9 Replies
View Related
Oct 22, 2007
Is there a way to list the recent changes to an excel document on the document itself?
For example, I have just 4 or 5 columns in the sheet, but out the the right on the 7th column I'll have a header that says "Recent Changes". Below that for X amount of rows, I would like it to keep a running change list something like this:
B14 was changed from '6.021' to '6.5' by username on 10/21/07 3:45 pm
A23 was changed from 'Sally' to 'Billy' by username on 10/21/07 2:30 pm
I thought this up by looking at the way the "Track Changes" functionality works on the Tools menu. That will highlight the cells in blue as they are changed and will also let someone look back through changes to let them accept or deny.
View 4 Replies
View Related
Dec 20, 2007
I am recording a macro to help me with formatting a document. I know do this manually, but realized that if I record a macro and run it the process would be faster. I have tried this and it works really well. However the problem is that although the columns are always the same, the rows increase and decrease everytime I download the raw report.
When I record the macro and then run it, it only selects the number of rows that I choose when I record the macro. I am sure there is a way to have the macro look for date and then stop after there is no more rows, but I can't find it in help.
I have tried selecting the whole document, but that slows the automated formatting down significantly.
View 9 Replies
View Related
May 15, 2008
I've got an Excel document that is generated from a third party programme (See ExcelDoc attached)
I'm trying to manipulate it so that a macro creates a CSV file like the one attached (see THK.csv)
I need to have the following columns in the CSV file populated using the Excel spreadsheet
Column A: Will need to have the NUMBER that is in cell B2 of the Excel file in this case the number is 0714 (need to keep the preceeding 0 too)
Column B: Will need the phone number
Column C: Will need the fax number
Column D: Will need to have the email address
Column E: Will need to have the date only
Column F: Will need to have the Order Number
Column G: Will need to have the Customer name
Column H: Will need to have the Customer telephone number
Column I: Will need to have the Customer alternative telephone number
**Column J: Will need to have the 1st line of the address (after First Address in xls doc)
Column K: Will need to have the 2nd line of the address
Column L: Will need to have the 3rd line of the address
Column M: Will need to have the 4th line of the address
Column N: Will need to be a blank cell
Column O: Will need to be a blank cell
Column P: Will need to be a blank cell
Column Q: Will need to be a blank cell
Column R: Will need to be a blank cell
Column S: Will need to be populated with the information that is after the field MyAddress: in Cell B11 of the xls document.
I've managed to get a macro that will take the information and transpose it into a new document and rename it as an CSV file but I am having a lot of problems trying to get the macro to create the CSV file in the format that I need.
**This cell is generated and will have commas at the end of each line of the address and will be terminated by a period (at line 4 of the address. Sometimes there will be 4 lines to an address but sometimes there may only be 2 or three. In the case where there is no information I need to have a blank (null) value in the cell
View 9 Replies
View Related
Jan 19, 2009
I'm trying to open a word document from excel using VBA. Here is my code.
View 5 Replies
View Related
Jan 20, 2009
I'm using the following code to copy columns of data in a worksheet of mine. The code once activated will open "notepad" and copy the columns of data in my excel worksheet. Here is the
View 6 Replies
View Related
Aug 9, 2009
I need a macro for printing a document, Could anyone supply me with the macro. And how to acivate it and sve in the sheet
View 5 Replies
View Related
Aug 25, 2009
I am trying to open a word document from within excel using
View 2 Replies
View Related
May 10, 2012
I am trying to get some code that will export an worksheet called 'Data' into a PDF document.
View 4 Replies
View Related
Sep 28, 2013
Amend this code? I want to save the word document, then save it as a pdf file, then delete the word document;
Code:
Sub E_W()
Dim strDate As String
Dim DirName As String
[Code]....
View 3 Replies
View Related
Aug 7, 2008
I've created a folder and spreadsheet on a shared drive. Since I am the Author of the document, Can I kick a user out of the document if I wish.
View 9 Replies
View Related
Jan 25, 2009
I need to covert a word document to excel.
View 9 Replies
View Related
May 15, 2009
I have an xls file that has many sheets in.
One of the sheets is a sumamry sheet that get's dulpicated, values pasted, then (the single sheet) exported to a new file. Upon trying to save this file, I keep getting the run time 1004 error message and that my document cannot be saved.
I've struggled for days trying to find out the source to no avail.
What is more frustrating is that when I try to save it (after exiting the macro), it seems to work ok.
I do get the compatability error message (which I don't get on the 'original/parent' workbook) there is a not about format errors, though I'm not sure that's the source of the problem.
View 9 Replies
View Related
Jul 8, 2009
I have my VBA Code set up to open a specific file and copy paste that information into this open spreadsheet in a particular sheet.
The only problem I have is that I don't know how to close the document once its opened.
I know that I can manually close it, but I want my macro to take care of it for me.
If anybody has suggestions, they would be greatly appreciated.
Here is my code so far to help out.
Sheets("Ann Ret Data").Select
Range("A1").Select
Workbooks.Open Filename:="P:InvINVpubAndrew EdgarMarket Value Projectperformance_data.xlsx"
Columns("A:G").Select
Selection.Copy
Windows("Qtr-End Market Value Project2.xlsm").Activate
Range("A1").Select
ActiveSheet.Paste
Also the file I want to close is the Performance_Data.xlsx file.
View 9 Replies
View Related
Nov 2, 2009
I have a worksheet that is pretty handy right now. I have a print button that automatically sends the doc to print with the specs I want, etc, etc. It has drop down lists, data validations, etc. What I want, and this may be impossible, is when the print button is clicked excel looks at a certain cell and based on the data in that cell, it pulls over a word document and prints that along with it.
In short, the items in the drop down list in the cell I have chosen have a different disclosure that goes with each, and I would like if possbile those to be printed from word...based on which item is picked....along with the spreadsheet when the print button is clicked.
View 9 Replies
View Related
May 18, 2006
I am working on a project where I am creating analysis using a subroutine in excel/vba and I would like to have the subroutine generate a report that is a word document. I have never used excel/vba to create and modify a word document and don't know how to approach it. I imagine that I will have a template.doc that I can access and save as another file. Does anyone have an example of creating and modify a word document in this manner.
View 2 Replies
View Related
Jun 3, 2006
When I start Excel I get a Document Recovery Window that lists a file that was lost some time ago. I don't need the file and don't want the window appearing. If I click on the listed file, or right click and select "open", "save as", or "show repairs", the window disappears and I get a blank document.
I know I could write code that would automatically close the window when I start Excel, but there has to be a better way. I DO want the window to appear when there is really a file that needs to be recovered, but I don't want the listed file and don't want the window.
View 7 Replies
View Related
Mar 29, 2007
I have a report that is imported into excel with all thick borders. Does anyone know a VBA code (or simpler way) to change all of these borders to skinny borders?
View 4 Replies
View Related
Jun 1, 2007
I've just written up an SOP for a reconciliation process. Part of it requires the reader to use some Excel UDFs that I've created. Rather than having someone look for the UDF file on the network, I tried to embed it as an Excel Worksheet Icon within my procedure document. However, when I double-click the icon to launch the add-in, I get the following error message:
"Microsoft Word can't start the application required to open this object. An error occurred and this feature is no longer functioning properly. Would you like to repair this feature now?" This does not happen when I embed a regular Excel workbook, or even a template.
View 5 Replies
View Related
