Only Execute Code On A Write-access Basis, Not Read-only

Jul 6, 2009

I have recently used a before_close event on this workbook to save a backup of the open file to another location on my system. This works fine but I was wondering if there was some more code I could add to only execute this event on a write access basis.

The file I use can be viewed by anyone on the network as read-only and only certain users with a password can edit/update with a write access password.

The backup event is use executes every time the document is closed be it read-only or write-access.

Ideally I would like to add some code to only execute this backup if the file is opened on a write-access basis.

View 3 Replies


File May Be Read-Only, Or You May Be Trying To Access A Read-Only Location

Jan 4, 2007

I'm trying to open a file on a network drive...but I'm getting the following error message when it opens: "This file may be read-only, or you may be trying to access a read-only location. Or the server the document is stored on may not be responding." Now, the file itself has no rights restrictions and is not read only. It doesn't appear to be locked.

Now, there are other Excel files in the same directory which I could open fine; however, the Excel documents having the above problem all have a little black icon "appears to be a padlock" (image attached) at the bottom left hand side of the Excel file icon. I tried the following:

- Renaming
- Converting to a different file format (didn't work, it won't let me)
- Opening in notepad...etc doesn't work.

This file is dated back in you think it's corrupt? Is there anything i can do to open or recover this?

View 2 Replies View Related

Read And Write INI Files

Sep 21, 2009

Ok I have these ini files for a access control system. We have 40 different systems. All on different databases. that look like the following: ...

View 6 Replies View Related

VBA Read/write .MP3 & .WMA Properties()

Jun 30, 2007

I have had a long-held belief that Excel/VBA methodology is very suited to solving this problem. I have investigated several possible methods noted below and picked one that gives a "quick win". I offer my results so far as a step towards the goal, as well as giving an example of how to manipulate a non-MS Office application using VBA. In this case Windows Explorer - I have used similar code on corporate applications like Oracle and SAP. My 'Write' method uses Sendkeys. Luckily I have been able to do the job without having to use code to simulate mouse functions or using API calls to simulate key presses. Ideally I would like to use something more stable, but there is the bonus that it is simple, as well as changing WMA and both versions of MP3 tag (see below) if present. Perhaps on reading this someone else may have a better method.

Being a ballroom dancer I have a large collection of CDs as well as software to rip to hard drive and enhance the sound quality. Many are quite old. A big problem has been to get a consistent view of the file properties - especially Genre, (eg. Waltz, Foxtrot .. etc.) which, from my own CD burning or external sources, is missing, or incorrect from using the now obsolete ID3v1 tag standard list. Applications such as Windows Media Player and RealPlayer allow functionality to edit tags but become very tiresome when it comes to making bulk changes - such as after burning a new CD. When viewing properties of the same file in various other applications they often show things like Title & Artist switched, and Genre not at all. I have tried software to change MP3 file tags, but find them over-complicated, confusing, and difficult to make the bulk changes I need. With Windows Explorer we can only change 8 properties - but I find these sufficient.

My method is :-

1. READ : Run one macro to put data into a worksheet.....

2. MAKE CHANGES : Make manual changes to the Excel worksheet in the normal way....

3. WRITE : Run another macro to read the worksheet and update the file properties in Explorer.

Part 1 is very simple and robust. Part 3 is difficult because when we use Sendkeys to mimic keyboard entry the code runs too fast to allow time for things to happen on screen, so we have to put Wait statements *depending on how fast the computer runs*. So Slower is better - up to a point.

Properties are added to MP3 files by using a "Tag" - additional bytes of information which form part of the file. WMA files are a Microsoft invention using a similar, but different structure. Interestingly, using my code to make changes via Window Explorer updates BOTH MP3 Tag versions as well as .WMA files. I moan about Microsoft less and less.

MP3 *ID3v1* consisting of 128 bytes always at the end of the file is now 'obsolete' - despite being still in use. This is very easy to read/write using the same code as for Text Files eg.

Open "c:myfile.mp3" For Binary As #1

etc. It is, however, limited to 4 text fields of 30 characters max, 'Year' =4 characters, and 'Genre' is a single character, the Asc() code of which is a lookup to a standard list which contains 125 items - none of which is any good to me. Could have my own lookup I suppose.

MP3 *ID3v2.3* is in the process of being superseded by ID3v2.4. The big problem here is that there are several different versions and the code required is extremely complicated - mainly due to the use of variable length fields. So we not only have to find the property, but read the field length before getting the field contents. Writing would need to change the coded field length. This is further complicated by there being the option to use an "Extended Tag" - *or not* ! Version 2 tag can be at the beginning or end of the file (before ID3v1 if it exists) - or both. The MP3 files on my computer all seem to have both versions - v2 at the beginning and v1 at the end. It is further complicated by the ability to have User Defined fields. I see some of my files have a user defined 'Genre' field, despite having the standard one 'TCON' too. The tag also needs a form of "encryption" so that the mp3 player does not treat it as audio data. Visit here for detailed information .

To view an audio file in its raw state open it in a Text Editor. I use 'TexPad' which is very fast and gives a choice of Binary (with Text 'translation' in a column) or Text view. Notepad gives just a Text view with empty space for non-text/binary characters. There is a large number of Null characters Asc(0) in proportion to the overall file length - mainly for "future development" I believe.

I would be interested to hear of any comments, suggestions and code improvements.


View 9 Replies View Related

Read & Write Mp3 File Tag Properties

May 31, 2008

The problem with the Write macro was that, although it did work, it used SendKeys which has to be slowed down considerably.

Here is a new version of the Write macro that works as normal. I originally tried to use CDDBControl.dll version which is widely available on the internet but found that I could only get it to change 1 file before crashing Excel.

I have put the READ macro in the next message.

Dim ws As Worksheet
Dim FromRow As Long
Dim LastRow As Long
Dim FilesToChange As Integer ' number of files to change
Dim FilesChanged As Integer ' number of files changed
Dim MyFilePathName As String ' full path & file name
Dim MyFileType As String ' mp3 wma etc.
Dim id3 As Object
Dim MyArtist As String
Dim MyAlbum As String
Dim MyGenre As String
Dim MyTrack As String
Dim MyTitle As String

Application.Calculation = xlCalculationManual
Set ws = ActiveSheet
Set id3 = CreateObject("CDDBControlRoxio.CddbID3Tag")
LastRow = ws.Range("A65536").End(xlUp).Row ' count worksheet rows
FilesToChange = ws.Range("A2:A" & LastRow).SpecialCells(xlCellTypeVisible).Count
If FilesToChange = 0 Then MsgBox ("No files to change."): Exit Sub
FilesChanged = 0........................

View 3 Replies View Related

Read-Write Popup - File Now Available

Apr 29, 2009

I have a tool that accesses a master sheet. Multiple people use the tool (sort of a user interface).

In some cases I need to write to the Master in which I check to make sure its not read only when the macro opens it (that works fine).

In other cases, like when just pulling information, read only is fine. But, I have not specified read only when I open the master.
Sometimes the users are getting a "File Now Available" with a read-write and cancel option. I do not want them to get this message.

My current universal call to master looks like this: ...

View 9 Replies View Related

To Read Many Text Files And Write It In The Excel File

Dec 11, 2009

I need help for reading data from text files and saving it under different columns in the excel file using vba macro.

For example: I have many text files in the following format ....

View 9 Replies View Related

Attempted To Read Or Write Protected Memory Error

Aug 20, 2009

I create and delete a conditional formatting rule programmatically. The issue I am facing is when I try to delete the conditional formatting rule that I programmatically created, I ran into the following errors:

1. Exception from HRESULT: 0x800A03EC
2. Attempted to read or write protected memory. this is often indication that other memory is corrupt.

I get these errors only when I have user created conditional formatting rules prior to deleting Ė I donít want to delete all conditional formatting rules using FormatConditions.Delete(). Further, I canít use FormatConditions[index].Delete() because I donít have a index reference for the one that was created from the application.

Note: I have checked the Trust access to the VBA project object model.

View 8 Replies View Related

Paramarray To Write Records To Access

Dec 21, 2009

I'm trying to simplify things a bit and create one sub with the appropriate parameters. My experience is limited with arrays so I thought I'd get snagged right where I did. I want to pass a paramarray, which contains two "columns", the access table field names and matching columns from the excel worksheet. This way, to add records to a table, I can simply pass the table name, source worksheet, and paramarray that provides the field/ column info. I'm getting the error "Wrong number of dimensions."

Sub test()
Dim testArray(0 To 4, 0 To 1) As Variant
'Write the field names and corresponding columns to an array. The first array "column represents the access
'table field names, the second is the corresponding column from the excel worksheet.
testArray(0, 0) = "Test1"
testArray(0, 1) = 1................

View 9 Replies View Related

Read Only Access In Excel?

Jan 16, 2012

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 Related

Direct Read Of Access Table

Jul 11, 2008

I have a RecordSet that has been built and accessed sequentially with no problems. I now want to access it directly, via it's Primary Key.

I know I can access it as follows :

With MyTable
Do Until .EOF
If ![PrimaryKey] = Work_Key Then
MyData = ![Data_To_Extract]
Exit Do
End If
End With
As a MainFrame Programmer, I would expect to be able to access that record directly, via it's Primary Key, something like :

Read MyTable Key = Work_Key
MyData = ![Data_To_Extract]
Am I thinking too much like a Mainframe Programmer (that *is* my trade, it's difficult to adjust sometimes !)? Is the With - Do/Loop method the way to go, or is there a Direct Read method I should be using?

View 9 Replies View Related

Macro To Access And Read Data From HDF Files?

Feb 16, 2013

Does Excel can be used to read data from HDF files? Specifically, what I would like to do is this: I have an Excel worksheet with some latitude data on Column A and longitude data on Column B. I would like to open a HDF file, which contains many sets of data, out of which 2 are the latitude/longitude data, and a third set contains the data that I want to extract. I will use the latitude data in Column A to search through the first set in the HDF, which will return me the row number. I will then use the longitude data in Column B to search through the second set to return the column number. With these row and column numbers, I will then extract the corresponding data in the third set, and write it to Column C in the worksheet.

how to write a simple VBA code for it? Or point me to some relevant information?

View 1 Replies View Related

Access/read Data From A Hidden WorkSheet

May 19, 2006

I'm trying to access/read data from a hidden WorkSheet in Excel 2003 using:

Application.Worksheets("Hidden Sheet").Activate
With ActiveSheet
**** Data ****
End With

The accessed **** Data **** refers to another ('unhidden') WorkSheet. How can I read the data without making the WorkSheet visible to the User?

View 4 Replies View Related

To Execute VBA Code @ 9:30:01 Exactly

Dec 27, 2008

Using Excel 2003 with WinXP. I'm trying to run macro code automatically whenever time = 9:30:01 (or whatever time I pick) for a stock market trading program, which is why the exact time matters so much. I've been able to get the time to update fine, but unless I click on the worksheet while the time condition is TRUE then my code doesn't run. The time actually is sent to me from the stock data provider and it shows up in a cell as a constantly updating value.

I've tried using the Workbook_SheetChange function and OnTime method, but without luck.

In both cases, unless I activate the sheet the code doesn't run. By activate, I mean that I have to click on the sheet when the values that trigger the code to run would be true. If I do that it works just fine, but sitting around and clicking on a worksheet defeats the purpose of automation. Since I'm trading stocks this has to be very exact, so I can't trust a macro scheduler to do this.

The code further below is what I'm trying to get to run. The time value is in cell L1. In cell L2 I have the following

View 9 Replies View Related

Read Data From Column Write To Separate Column?

May 30, 2014

I am trying to write a formula to read the cells in column H individually if cell is blank write no to corresponding cell of column I, if the cell has any sort of data write yes to corresponding cell of column I

Currently H uses this formula ='name of column from sheet 1'!P:P

View 2 Replies View Related

VBA: Can I Execute Code When I'm WITHIN A Cell (F2)

Jan 4, 2008

Assume a cell -- say A1 -- has a long formula in it. If I select A1, press F2 to "Enter" the cell, then I have a blinking 'l' indicating the cursor/pointer position WITHIN the cell.

Is there any way to control the position of the blinking 'l' (or whatever it is called)? For instance, in a long formula, I would like to write a macro which could transport the blinking 'l' to midway into the formula string.

Alternatively, can I make a partial selection WITHIN a cell and run a macro on it. For instance, if cell B1 has a heading "Dec 2007" and I highlight just the "Dec" portion of the string, I would like to execute a macro to color it red. I have a simple macro that can do it to the entire cell, but not to only part of the cell contents. Is there any way for VBA to be active when I am WITHIN a cell.

Sub Font_Red()
'will add red color to ActiveCell font
Selection.Font.ColorIndex = 3
End Sub

View 9 Replies View Related

Use String To Execute Vba Code??

Mar 30, 2007

how could I use string to execute VBA. For example

mystring1="for i =1" & " to " & CStr(x)
mystring2="msgbox i"
mystring3 ="next i"

execute mystring1 & mystring2 & mystring1

Like this, I want to combine a VBA CODE string.
And use this string to execute VBA

View 5 Replies View Related

VBA Code To Include Dos Command And Execute Same

Nov 7, 2012

I have below code to open DOS from excel. it is working fine.

I wanted to excecute some DOS command automatically after opening DOS, command is entered in the excel Sheet1 E5:E1000

Sub openDos()
Call Shell("cmd.exe " & dosCmd, vbNormalFocus)
End Sub

View 4 Replies View Related

Execute Code Many Times With Different Variables

May 10, 2007

I want to execute the following code once for each day (Mon to Sun)

Dim Day As String
Dim DeletedDept As String
Set StartRange = Cells(Range("Cashiers").Row + 1, Range(Day & "_Date").Column + 1)
Set EndRange = Cells(Range("Cashier_Totals").Row - 1, Range(Day & "_Date").Column + 3)
Set EntryRange = Range(StartRange, EndRange)
For Each cl In EntryRange
If cl.Value = DeletedDept Then cl.Value = ""

View 3 Replies View Related

Execute Code While Msgbox Displayed

Sep 19, 2007

I have some code that launches a msgbox and I would like to have the code continue to execute with the msgbox displayed rather than wait for the user to click OK/Cancel.

View 9 Replies View Related

Read Text File And Write Text

Feb 16, 2007

What if you have text that are hyperlinked to a txt file and you want to read from it and copy it into excel. What can I do then. Here is what I have been working on so far:

Sub GoToHyperLink()
Dim cell As Range
Dim link As Hyperlink
Dim Textline As String


For Each cell In Selection

Selection.Hyperlinks(1).Follow NewWindow:=False

I can not get it to write it to excel.

View 9 Replies View Related

VBA To Import And Execute Code From Text File

Jul 22, 2014

Is it possible to write a macro that can import VBA code in a text file then execute it? I need this functionality as I have produced a corporate spreadsheet template that goes out to many people and I will need to be able to update it as requirements change once it has been distributed. So my idea was to build a macro in the template that has the code in it to import the "update code" from a text file that I would send to all the folks that have the template. If the template was centralized, that would save me from this issue but it is however going to be distributed widely.

View 5 Replies View Related

Execute Code Based On Cell Entry

Oct 28, 2009

I would like to write the code or create a macro that will execute when the value of a range of cells is greater than null. The macro or code that I would like to execute will UNHIDE a group of consecutive rows.

View 14 Replies View Related

Code For Specifying Cells So That Copying And Pasting Macro Will Not Execute There

Feb 20, 2013

I've attached a sample workbook in which there are 3 macro-buttons.

The buttons will paste a shape in the active cell. So this means the buttons themselves could be deleted and replaced with a shape.

Since locking and then protecting the cells disables the macros, how can I amend the code to make sure the buttons' cells are protected from the copying and pasting macros? Or, how do I ensure that the macros only work in A1 - E5?

View 4 Replies View Related

Automatically Execute Code Based On Cell Change?

Sep 5, 2013

I need to be able to hide and unhide a given set of rows based on the value in a particular cell. My current code allows me to successfully do this, ONLY, when I select the cell being 'watched' for changes and press enter. It does not execute the code automatically.

The cell is changed by a set of filters that modifies the data. The watched cell is then a summation of the filter modified cells. If the filters change to all "0" or "-" then my 'watched' cell sum becomes "0" and thus should execute the code automatically.

Current Code:

Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.Outline.ShowLevels RowLevels:=3
If Range("I62").Value = "0" Then
Rows("63:87").Hidden = True
End If
End Sub

So, right now if I use the filter and the cells change then the sum in I62 becomes "0", I have to manually select cell I62 and press enter and which point the above code executes exactly as I intend.

I need the execution part to be automatic and not have to manual click into cell I62.

View 1 Replies View Related

How To Write UDF Code For Userform

Mar 12, 2013

Trying to write a UDF Code for a User Form. The User puts in the data for the element it is trying to find the pressure in mm Hg for.

I want a Msg Box to appear when the desired temperature is less than the lower limit temp. and the desired temp is greater than the upper limit temp.

I figured out how to get the message box to appear, but I have not figured how to get the If statement to continue with the calculation if the desired temp is within range. When I press the command button to calculate, the message box appears whether the temp is in range or not.

My code is as follows:

Private Sub CommandButton1_Click()
Dim A As Double
Dim B As Double
Dim C As Double
Dim Pvapor As Double


View 2 Replies View Related

How To Write A Code For Loop

Mar 20, 2014

i need a code that can get parameter like a date and put it in a cell in the same column but different rows .

View 8 Replies View Related

Write Code To Cal Duration

Jan 12, 2008

I have following data, data_in and date_out. I need to auto cal these 2 variable different and give the output as duration in month.

x_INV †EF1date_lndate_out205/10/195005/10/1950325/10/195025/10/1950415/11/195015/11/1950508/01/195029/10/1950618/10/195018/10/1950717/10/195017/10/1950817/10/195017/10/1950915/10/195020/12/19501013/12/195013/12/1950 Excel tables to the web >> Excel Jeanie HTML 4

And give the result in colume X as following :-

x_INV †X1stk_dur20304051060708092 Excel tables to the web >> Excel Jeanie HTML 4

View 9 Replies View Related

Change From Execute On Selections To Execute On All

Feb 28, 2014

I have the following code:

[Code] .......

What do I need to change in order to make it execute the Call statement on EVERY item in the ListBox2, not the Selections.

View 3 Replies View Related

VBA Code To Write Data To Sheet?

Mar 31, 2014

See attached my workbook, I have a user form and when the OK button is pressed I need to get the data to write to the master sheet. Sheet1 behind.

Is there also a way I can re-populate all the data back to the userform using my search URN combo box?

Then the recorded can be altered and when update is pressed on the user form the record just updates on the master and not adds another record.

Waiting time sheet Basic.xlsm‚Äé

View 1 Replies View Related

Copyrights 2005-15, All rights reserved