Read And Write INI Files
Sep 21, 2009Ok 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
ADVERTISEMENT
To Read Many Text Files And Write It In The Excel File
Dec 11, 2009I 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 ....
VBA Read/write .MP3 & .WMA Properties()
Jun 30, 2007I 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 http://www.id3.org .
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.
READ CODE IS IN THE "REPLY" BELOW
Read & Write Mp3 File Tag Properties
May 31, 2008The 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 1.2.0.51 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
Sub WRITE_TO_EXPLORER()
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........................
Read-Write Popup - File Now Available
Apr 29, 2009I 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: ...
Only Execute Code On A Write-access Basis, Not Read-only
Jul 6, 2009I 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.
Attempted To Read Or Write Protected Memory Error
Aug 20, 2009I 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.
Files Saved To Program Files Folder, Open As Read Only
Feb 8, 2009I have an Excel application (Excel 2003) which is stored in the default Program Files folder by the Installer; for example: c:program fileszxchello.xls. The problem I am running into is this file opens as Read Only in Vista and this is interfering with the running of the application. There is no problem opening the file normally in Windows XP.
I have been able to narrow down the cause of this to the User Account Control system in Vista - if I turn OFF User Account Control, the Excel file opens normally and my application functions normally. Is there another option to open the Excel file without turning OFF User Account Control because some users may find it unacceptable to turn OFF this security feature. Ofcourse, one option is to install the application in another location, outside the Program Folder, and the file would open normally, but the Packaging Wizard that I am using to package the application does not allow me to install the application in any other location and thus, the application installs in the Program Folder and I am running into this problem of the Exel file opening as Read Only. Is there a way out of this situation where I can open the file normally (not as Read Only)?
Read Data From Column Write To Separate Column?
May 30, 2014I 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
Saved Files Read Only.?
Jan 8, 2009I took two weeks perfecting an Excel file and was writing them to disc just now to transport and use on other machines. I opened the file to add instructions and the files are now read only? Propteries show read only on the written files but not on the originals???
View 4 Replies View RelatedWrite Script Which Returns All Files And Folders?
Nov 2, 2012I need to write a script which returns all files and folders. The script should also work on a Mac pcs. The "Windows" script works without any problems but I have real problems with the Mac script:
Code:
Public Sub List_Files_Mac()
Dim sDir As String
Dim sFile As String
[Code]...
This script doesn't return the folders and long filenames are truncated.
Write Folder & File Name Of All Files In Directory
Jul 25, 2007I need some VBA code to cycle through each and every folder and file in a given root directory and write the file name string to a cell. (does not need to look at the content of sub-folders only the names of all objects sitting directly under the given root.)
View 4 Replies View RelatedRead Stuff From Closed Files
Dec 18, 2007I would like to create a single excel file, which is composed by fragments of different (closed) excel fies (it should take some data from the files).
Code Opens Files As Read Only
Apr 19, 2007why the below code opens files as read only? What this code does is open all worksheets, in four different folders, whose filenames include the desired date. For example, if i want all worksheets for the 19th, then K117819, k127819, k217819 and K226119 all open. Except they open as read-only which requires saving to a different location and then renaming - which sort of defeats the time saving reason for running the code in the first place.
Public Sub Auto_Open()
Dim sCurFile As String
Dim sPath As String
Dim mpath As String
MsgBox "This tool will print all four production lines line report total sheet for any month and day in 2007"
mpath = InputBox("Enter the month i.e. 01 for January", "print")
fpath = InputBox("Enter day of the month to print (use leading 0's)?", "print")
Application. ScreenUpdating = False
Application.DisplayAlerts = False
Application.AskToUpdateLinks = False
'Get the path
sPath = "t:2007" & mpath & "k11"...............
Read Text File And Write Text
Feb 16, 2007What 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
Range("C2:C13").Select
For Each cell In Selection
Selection.Hyperlinks(1).Follow NewWindow:=False
I can not get it to write it to excel.
Excel 2010 :: Read CSV Files Without Opening?
Oct 23, 2012Is there a way of reading .csv files and copying the data from say Sheet1 without actually opening the file itself? I have .csv's that will take some time to open due to size so dont really want to open them, but want to copy the data from all of them within a specific folder.
I am running windows 7 x64 and office 2010 x64.
Read Strings From 2 Reference Text Files?
May 28, 2014we have 2 text file one is masters and other one child. we want to read the city from child text and wants to read the country coresposding to the city from master file.
the final output should look like
country city
england Portsmouth
Macro To Access And Read Data From HDF Files?
Feb 16, 2013Does 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?
Read Specific Cell From Many Excel Files Automatically?
Mar 11, 2014Is there anyway to read a specific cell from many excel files automatically and add them to a new workbook?
I tried to link that specific cell to a new workbook manually but now I have a problem: If I change the name of any workbook that is linked to the new workbook, Excel can't update the new file name
Read And Import Multiple Text Files Into Excel And Parse Data?
Mar 27, 2014I am trying to determine a way to quickly import data from text files into Excel and place data in suitable columns (under correct headings). I am thinking I could be asked which file to read and import doing them 1 by 1, or if there is an automated way to cycle through all the files that would be more efficient (filenames are variable).
From the text files I have attached I can tell the column headers and what data should go under each. Not sure how you would describe the delimiting on these files? Are these files in a format that VBA could be used to reduce manual copy and paste approach? I have about 300 of these files I want to extract the data from.
Note: the attached files are from a public access website.
WELLS0214.TXT WELLS0106.TXT
Write Ranges To Text Files & Save Each As Cell Text
Sep 17, 2007I m trying to write a macro which could take the text from a single column row T2 to row T313 and write it to a .txt file. Have the .txt file name created by the text in T4 or I could also put the text to name the file in T1 if you think it would be easier.
Then carry on to the next named sheet and produce another .txt file in exactly the same way until all 15 sheets have been completed. It would also be helpful if prior to starting to write each text files, it could test for any text in cell A2 of the sheet. The first empty A2 cell of a sheet would determine the end of the run, if it was prior to sheet 15 being reached.
When I Enter The Password For Six Of The Files, I Receive The "Unable To Read File" Error Message
Apr 2, 2007I have about 20 files that all have separate passwords. When I enter the password for six of the files, I receive the "Unable to Read File" error message.
View 5 Replies View RelatedFile May Be Read-Only, Or You May Be Trying To Access A Read-Only Location
Jan 4, 2007I'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 2004...do you think it's corrupt? Is there anything i can do to open or recover this?
VBA - Carry Out Task On Set Of CSV Files - Return Values And Leave Files Untouched?
May 14, 2013Trying to write a script to carry out a number of tasks but is there code to enable an Excel to look in a particular file for the list of csv files - carry out the tasks and return the appropriate values in the Excel sheet whilst leaving the original csv files untouched?
View 2 Replies View RelatedTransfering Files From Lots Of Files To One Single Exel Master File.
Jun 26, 2009Need a way to update a single master file. What happens right now is that the cost manager sends out one file to a lot of different project heads and then on having them return back to him he basically has to individually add in all the data. What i am trying to do is that he somehow carries on sending out the data as there are a lottt of projects and then on returning them they automatically update.
Copy And Rename Files In VBA By Searching Multiple Folders For Files?
Jan 4, 2014how I can loop through folders to select files starting with a certain word and copy all of them to a different folder and rename them. The folder structure is given below
Company 1(parent folder)
North South East(sub folder) West(sub folder)
Jan Feb Mar.... Dec Jan Feb Mar.... Dec Jan Feb Mar.... Dec Jan Feb Mar.... Dec
In the above structure, the files are present inside each folders Jan, feb...Dec under the regions North, South East n west. note that I have to select files starting with "Sales" and copy them into a new folder(say results) and rename copied files as Sales1.xls, Sales2.xls etc. (Files are not present in the folder company1, north, south, east and east.)
Open Files In Folder- Wait 30 Seconds Then Close The Files
Jul 5, 2007I want to allow users to place files (.jpg, .tif, .pdf, .pps etc) into a network folder and then have my program open each file and display it for 30 seconds then close the file and the application before opening the next file.
1. Set up a loop to get a directory listing of the folder and write the listing to a text file.
2. Open up the text file and get the next filename in it
3. Open the file in the associated application
4. Wait for 30 seconds (or some period of time) and then close the file
5. Repeat steps 2-4
DoIt = 1
While DoIt = 1
Open "C:TempList.txt" for output as #1
Print #1, Files In Folder
Close #1
Open "C:TempList.txt" for input as #2
while not eof(2)
Line input #2, MyFile
Display MyFile on screen
Wait for 30 seconds
Close MyFile and MyApplication that opened it
wend
Close #2
I can get the directory listing just fine with no problems
I can open the files in the associated application just fine with no problems.
with closing the application after 30 seconds or some period of time.
I need some code that will allow me to easily send it a filename and it will know how to close the file and the application that opened it.
Add Another Worksheet & Continue Importing The Html Files Until There Are No More Files To Import
Jul 13, 2006I'm using the following code to import thousands of html files into my spreadsheet. The code is working fine. Since I am importing thousands of files, when there is no more space on my worksheet, the code stops with an error message. I want to make this code add another worksheet & continue importing the html files until there are no more files to import.
Sub Master_Importer()
Dim I As Long
Dim strFilename As String
Dim strPath As String
strPath = "file:///C:/Documents and Settings/c/Desktop/New Folder/"
With Application.FileSearch
.LookIn = "C:Documents and SettingscDesktopNew Folder"
.FileType = msoFileTypeAllFiles
.Execute
For I = 1 To .FoundFiles.Count
strFilename = Mid(.FoundFiles(I), InStrRev(.FoundFiles(I), "") + 1)
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;" & strPath & strFilename _ .......................
Save Ranges In Multiple Files To Text Files
Dec 21, 2007I have a excel file that I enter information into. I have code that saves the files to a certain folder with the name, date, and time stamp for the file name. At the end of the day I might have 3 to 15 excel files I have created that day and I would like to take information from certain cells (examle: L3, B6, B7, B8, G8, and so on) and create a txt file with all of the information in it.
Example:
12/20/2007
Your Name
123 Somewhere St.
Here, OH 45111
Home Visit
12/20/2007
Someone Else
345 Anywhere St.
There, OH 45211
Hospital Visit
Convert All CSV Files In A Folder To Excel Files?
Apr 8, 2014I is it possible to convert all comma separated text files in a single folder in to excel files. But the requirement is to have 2 sheets in each new file. first to be the full file - with all columns, and in the second sheet to keep only colum A B D G H K L M O P R S T V W from the first sheet. The second sheet name must be the same as the first one but without the first "wlist_" in the name.
One more thing. The third column in the second is called "COUL". there are short letters for colors in french
can they be converted with the sort in English like it goes:
NO = B
BA = W
RG = R
SO = P
JA = Y
BE = L
VE = GY
GR = G
VI = V
MA = BR
BJ = TA
OR = O
Here is a link to the both CSV and an example excel file with the end result. In this example i haven`t change the shorts for the colors. It takes me too much time with the find and replace function. And at the moment i`m really pushed from time.
[URL]