Parse(?) data from text file
I have a large text file that has hundresds (or thousands) of compiled questionairres that I want to convert to a more usable format (excel?). Here is what it looks like: ======================== Name: Joe Jones Address: 123 main comment: varies from blank to 20+ lines may have muliple paragraphs others: 8 or 10 other fields =============================== next questionnaire ====================== A couple of details: the separator line of ====s vary in length, the individual questionnaires are not always identical. Some have a couple of extra 'fields'. I envision a separate line for each questionnaire. I just can't cut and paste 10 or 12 separate fields for several hundred questionnaires. I doubt there is a perfect way to do this given the variables involved, but perhaps there is a way to get close so I don't have too much manipulation to fix it up. Any help is greatly appreciated. thx, BDT
BDT
excel
39879 articles. 
2 followers.
9 Replies
590 Views
Each line of a questionnaire will be put on the same row, in a separate
cell :
Sub TextFileInput()
Dim myLine As Long, Enrgt As String, Col As Integer
Close #1
Open "e:\donnees\daniel\mpfe\textfile.txt" For Input As #1
Workbooks.Add 1
Do While Not EOF(1)
Line Input #1, Enrgt
If Left(Enrgt, 1) = "=" Then
myLine = myLine + 1
Col = 1
Else
Cells(myLine, Col) = Enrgt
Col = Col + 1
End If
Loop
Close #1
End Sub
Regards.
Daniel
> I have a large text file that has hundresds (or thousands) of compiled
> questionairres that I want to convert to a more usable format (excel?). Here
> is what it looks like:
>
> ========================
> Name: Joe Jones
> Address: 123 main
> comment: varies from blank to 20+ lines may have muliple paragraphs
> others: 8 or 10 other fields
> ===============================
> next questionnaire
> ======================
>
> A couple of details: the separator line of ====s vary in length, the
> individual questionnaires are not always identical. Some have a couple of
> extra 'fields'.
>
> I envision a separate line for each questionnaire. I just can't cut and
> paste 10 or 12 separate fields for several hundred questionnaires. I doubt
> there is a perfect way to do this given the variables involved, but perhaps
> there is a way to get close so I don't have too much manipulation to fix it
> up.
>
> Any help is greatly appreciated.
>
> thx, BDT
dcolardelleZZZHi Daniel, I'm in over my head here, I haven't written a macro in years. I used your macro but replaced line 4 with: Open "e:\RepMon.txt" For Input As #1 and I put the file RepMon.txt in the root of e:. I ran the macro and got an error that said ... Run time error 1004, Aplication-Defined or Object-Defined error. When I clicked the debug button, it highlighted the line: Cells(myLine, Col) = Enrgt am I doing something wrong or is there an obvious problem?? thanks, BDT "Daniel.C" wrote: > Each line of a questionnaire will be put on the same row, in a separate > cell : > > Sub TextFileInput() > Dim myLine As Long, Enrgt As String, Col As Integer > Close #1 > Open "e:\donnees\daniel\mpfe\textfile.txt" For Input As #1 > Workbooks.Add 1 > Do While Not EOF(1) > Line Input #1, Enrgt > If Left(Enrgt, 1) = "=" Then > myLine = myLine + 1 > Col = 1 > Else > Cells(myLine, Col) = Enrgt > Col = Col + 1 > End If > Loop > Close #1 > End Sub > > Regards. > Daniel > > > I have a large text file that has hundresds (or thousands) of compiled > > questionairres that I want to convert to a more usable format (excel?). Here > > is what it looks like: > > > > ======================== > > Name: Joe Jones > > Address: 123 main > > comment: varies from blank to 20+ lines may have muliple paragraphs > > others: 8 or 10 other fields > > =============================== > > next questionnaire > > ====================== > > > > A couple of details: the separator line of ====s vary in length, the > > individual questionnaires are not always identical. Some have a couple of > > extra 'fields'. > > > > I envision a separate line for each questionnaire. I just can't cut and > > paste 10 or 12 separate fields for several hundred questionnaires. I doubt > > there is a perfect way to do this given the variables involved, but perhaps > > there is a way to get close so I don't have too much manipulation to fix it > > up. > > > > Any help is greatly appreciated. > > > > thx, BDT > > >
Hi BDT > Open "e:\RepMon.txt" For Input As #1 > and I put the file RepMon.txt in the root of e:. No problem so far. > I ran the macro and got an > error that said ... Run time error 1004, Aplication-Defined or > Object-Defined > error. When I clicked the debug button, it highlighted the line: > > Cells(myLine, Col) = Enrgt > > am I doing something wrong or is there an obvious problem?? The problem lies in the code you were given. It works perfectly if the first line of the text file is a separator line (ie begins with "="). In this case, the first part of the If condition is satisfied and Col is set to 1. If this is not the case, it goes straight to the 2nd part of the condition and tries to write data to a cell in column 0, which doesn't exist. The cure is to add the line Col=1 somewhere before If Left(Enrgt, 1) = "=" Then There's another oddity in the code. Workbooks.Add 1 creates a new workbook, but I don't see the point of it as the data is written to the workbook holding the macro anyway. You can safely delete this line. Hope this helps. -- Ian -- > > thanks, BDT > > "Daniel.C" wrote: > >> Each line of a questionnaire will be put on the same row, in a separate >> cell : >> >> Sub TextFileInput() >> Dim myLine As Long, Enrgt As String, Col As Integer >> Close #1 >> Open "e:\donnees\daniel\mpfe\textfile.txt" For Input As #1 >> Workbooks.Add 1 >> Do While Not EOF(1) >> Line Input #1, Enrgt >> If Left(Enrgt, 1) = "=" Then >> myLine = myLine + 1 >> Col = 1 >> Else >> Cells(myLine, Col) = Enrgt >> Col = Col + 1 >> End If >> Loop >> Close #1 >> End Sub >> >> Regards. >> Daniel >> >> > I have a large text file that has hundresds (or thousands) of compiled >> > questionairres that I want to convert to a more usable format (excel?). >> > Here >> > is what it looks like: >> > >> > ======================== >> > Name: Joe Jones >> > Address: 123 main >> > comment: varies from blank to 20+ lines may have muliple paragraphs >> > others: 8 or 10 other fields >> > =============================== >> > next questionnaire >> > ====================== >> > >> > A couple of details: the separator line of ====s vary in length, the >> > individual questionnaires are not always identical. Some have a couple >> > of >> > extra 'fields'. >> > >> > I envision a separate line for each questionnaire. I just can't cut and >> > paste 10 or 12 separate fields for several hundred questionnaires. I >> > doubt >> > there is a perfect way to do this given the variables involved, but >> > perhaps >> > there is a way to get close so I don't have too much manipulation to >> > fix it >> > up. >> > >> > Any help is greatly appreciated. >> > >> > thx, BDT >> >> >>
me1> Hi BDT > >> Open "e:\RepMon.txt" For Input As #1 >> and I put the file RepMon.txt in the root of e:. > > No problem so far. > >> I ran the macro and got an >> error that said ... Run time error 1004, Aplication-Defined or >> Object-Defined >> error. When I clicked the debug button, it highlighted the line: >> >> Cells(myLine, Col) = Enrgt >> >> am I doing something wrong or is there an obvious problem?? > > The problem lies in the code you were given. It works perfectly if the first > line of the text file is a separator line (ie begins with "="). In this case, > the first part of the If condition is satisfied and Col is set to 1. If this > is not the case, it goes straight to the 2nd part of the condition and tries > to write data to a cell in column 0, which doesn't exist. > > The cure is to add the line Col=1 somewhere before > > If Left(Enrgt, 1) = "=" Then > > There's another oddity in the code. > > Workbooks.Add 1 creates a new workbook, but I don't see the point of it as > the data is written to the workbook holding the macro anyway. You can safely > delete this line. > Just to say that adding a workbook or a new sheet ensures that you are not overwriting any existing data. Daniel
"Daniel.C" <dcolardelleZZZ@free.fr> wrote in message news:OIEoUhFPJHA.3748@TK2MSFTNGP04.phx.gbl... >> Workbooks.Add 1 creates a new workbook, but I don't see the point of it >> as the data is written to the workbook holding the macro anyway. You can >> safely delete this line. >> > Just to say that adding a workbook or a new sheet ensures that you are not > overwriting any existing data. > Daniel That may be the intention, but the data isn't written to the new workbook -- Ian --
>> Just to say that adding a workbook or a new sheet ensures that you are not >> overwriting any existing data. >> Daniel > > That may be the intention, but the data isn't written to the new workbook > > -- > Ian Well, they did when I test it. Daniel
Did you put the code behind a worksheet or put it in a General module? It belongs under a general module. If you're new to macros: Debra Dalgleish has some notes how to implement macros here: http://www.contextures.com/xlvba01.html David McRitchie has an intro to macros: http://www.mvps.org/dmcritchie/excel/getstarted.htm Ron de Bruin's intro to macros: http://www.rondebruin.nl/code.htm (General, Regular and Standard modules all describe the same thing.) IanC wrote: > > "Daniel.C" <dcolardelleZZZ@free.fr> wrote in message > news:OIEoUhFPJHA.3748@TK2MSFTNGP04.phx.gbl... > >> Workbooks.Add 1 creates a new workbook, but I don't see the point of it > >> as the data is written to the workbook holding the macro anyway. You can > >> safely delete this line. > >> > > Just to say that adding a workbook or a new sheet ensures that you are not > > overwriting any existing data. > > Daniel > > That may be the intention, but the data isn't written to the new workbook > > -- > Ian > -- -- Dave Peterson
petersod"Daniel.C" <dcolardelleZZZ@free.fr> wrote in message news:uHsexBGPJHA.584@TK2MSFTNGP06.phx.gbl... >>> Just to say that adding a workbook or a new sheet ensures that you are >>> not overwriting any existing data. >>> Daniel >> >> That may be the intention, but the data isn't written to the new workbook >> >> -- >> Ian > > Well, they did when I test it. > Daniel > > I've just tried a couple of things and discovered that if the code is pasted in at sheet level, the data is copied into that sheet, but if it's pasted at workbook level, it copies the data to the new workbook. I've just learnt something new -- Ian --
Hi Danial, Ian and Dave, Awesome help,all of you. Instead of fooling with the macro, I just added the line of ======s that I left off of the first set of records and the macro ran fine. I only need to run this a couple of times so I can easily do it that way and it will save me many hours or work. thanks again, BDT "Dave Peterson" wrote: > Did you put the code behind a worksheet or put it in a General module? > > It belongs under a general module. > > If you're new to macros: > > Debra Dalgleish has some notes how to implement macros here: > http://www.contextures.com/xlvba01.html > > David McRitchie has an intro to macros: > http://www.mvps.org/dmcritchie/excel/getstarted.htm > > Ron de Bruin's intro to macros: > http://www.rondebruin.nl/code.htm > > (General, Regular and Standard modules all describe the same thing.) > > IanC wrote: > > > > "Daniel.C" <dcolardelleZZZ@free.fr> wrote in message > > news:OIEoUhFPJHA.3748@TK2MSFTNGP04.phx.gbl... > > >> Workbooks.Add 1 creates a new workbook, but I don't see the point of it > > >> as the data is written to the workbook holding the macro anyway. You can > > >> safely delete this line. > > >> > > > Just to say that adding a workbook or a new sheet ensures that you are not > > > overwriting any existing data. > > > Daniel > > > > That may be the intention, but the data isn't written to the new workbook > > > > -- > > Ian > > -- > > -- > > Dave Peterson >
Similar Artilces:
Pasting data from Excel
Hello everyone, I'm not sure if I should be posting this question here or in the Excel forum but here goes. Is it possible to copy data from multiple cells in Excel and then paste them into multiple lines of the criteria section of an Access query? For example, Given cells and values: A1- 1 A2- 2 A3- 3 I would like to be able to copy this data from Excel and paste it into an Access query like : Criteria: 1 or: 2 3 I am using Access 2002 SP3 and Exc...
Printing data forms
I would like to be able to print Excel data forms. Is there a way? In some event of the form (a button, a click of the form itself, whatever): Me.PrintForm "Curious" <anonymous@discussions.microsoft.com> wrote in message news:170101c47bf9$f9191420$a301280a@phx.gbl... >I would like to be able to print Excel data forms. Is > there a way? If you mean the form you get when you do Data=>Form with the form displayed, do Alt+Print Screen. then drop the form and go to a new worksheet and do Edit=>Paste This will put an image of the form on the worksheet and yo...
Free Data Process/Query Tool
If you use Excel everyday and spend lots of time on data/text processing, then you will find the this tool (UDQ Add-in) is very powerful and can speed up your daily data processing work. Currently the tool is free for everyone. Please feel free to distribute this tool to your friends/colleagues if you think it is helpful to your work. The following is a list of features/functionality of the tool: 1> Query/Import Data from Multiple External Data/Text/ASCII Files (Can useful import data from hundreds of files within minutes) 2> Query/Import Data from Multiple Closed Excel Files (Can...
Consolidation Worksheet Data Sequence
I'm consolidating 3 worksheets into one by using Excel 2007 data consolidation. The worksheets have a product number in column A and sales figures in columns B, C & D for Jan, Feb and Mar. The worksheets are in product number sequence. Not all product numbers appear on each sheet, so I consolidate by category using "labels in left column" and "create links to source data" to create a consolidation sheet in outline form. The problem is the consolidated worksheet is not in product number sequence as I need it to be. I have books on Excel 2007 and Excel 2003 and their ...
Hidden files in Ms-Query cause ODBC connect errors or Query is wac
I have a Query fetch_from_bob that moves data (collapses records,unique key search, etc...) from excel spreadsheet "bob" to excel spreadsheet "fred". Fred and bob live in the same directory" c:\123directory" on the same computer. I allow this query to execute/refresh data automatically when one of the query paramaters is changed. This works great. I also have a macro that invokes the query to refresh the data whenever the user desires. This works great as well. The macro literally has all the VB code to execute the query. But I'm just a part time progra...
Filtering on text field
I am using an on Double Click event to filter based on the current value on that field. It works when I use it on a number field: Me.Filter = "[forMthYr]=" & Me![cbMthYr] Me.FilterOn = True but when I apply the same code (below) to a text field it does not work - I get parameter boxes popping up. Me.Filter = "[ITResource_LName]=" & Me![tbITResource_LName] Me.FilterOn = True Any help is appreciated. Zag -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200712/1 It works on a numeric field because there are no d...
Limits on Exporting Outlook Contact Data
Anyone know how to get custom fields, follow-up flags, and/or the "contacts" (links between contacts) from MS Outlook 2003 to Excel, CSV, Goldmine, or Act? The standard export utility in Outlook does not offer export of these fields. Most important is the links between contacts. You'd have to write your own code to do this. A key issue would be what information from the linked contact(s) you want to include. -- Sue Mosher, Outlook MVP Author of Microsoft Outlook Programming - Jumpstart for Administrators, Power Users, and Developers http://www.outlookcode....
Outlook: Can't open JPEG files
I have Outlook 2000 an unable to open JPEG files. What setting has to be changed to open these files? ...
File Edit toolbar is missing
The File Edit toolbar in Excel is missing First thing to check: Tools|customize|toolbars tab|make sure worksheet menubar is selected. Well, maybe the first thing to check is to verify that you can see all of excel--it's not just off the display window??? stellarmellor wrote: > > The File Edit toolbar in Excel is missing -- Dave Peterson ...
8192 rows limit when opening wk1 files ?
Hello, I am opening a .wk1 file, which contains around 60K rows, but only the first 8192 rows appear in Excel. Is there any way to have all the rows in the Excel file ? Thanks, Paul "paul" wrote... >I am opening a .wk1 file, which contains around 60K rows, >but only the first 8192 rows appear in Excel. > >Is there any way to have all the rows in the Excel file ? WK1 as in Lotus 1-2-3 Release 2.x native file format? Such .WK1 files support a single worksheet of 256 columns and 8,192 rows. PERIOD. Did you generate this '.WK1' file using a program other then...
Counting Repeated text or duplicates in a list
I need a simple formula to count the total number of repeated text in a list (column). Say there is thousands of entries (confirmation numbers) both numeric, alphabetic and alpha-numeric but many of the entries are repeats. How can I calculate in one cell the total number of repeated instances in the list? And I can't sort the list, just calculate the number of repeated instances in the list just the way it is, and the list is still growing, so I need the formula to be able to count all instances in the list even as they are being added. plz help ! =COUNTA(A1:A50)-SUMPRODUCT((...
Select Rows dependant on certain field data
How can I select certain rows in a spreadsheet based upon the text foun in one column? To explain: All rows have a column that contains tex "immediate" or "ongoing" or "closed". How can I select only the row that contain the word "immediate" in that column? When I say select a mean select as in ready to 'copy' or 'cut' tha data ready to be pasted elsewhere. I know what I mean :confused -- Madd ----------------------------------------------------------------------- Maddy's Profile: http://www.excelforum.com/member.php?action=getinf...
how to select multiple text boxes in excel for formatting
I am trying to select multiple text boxes for formatting the font but seem unable to select all of them other than to click on each one individually. Is there an easy way to select all of the text boxes at once? To select multiple objects on the sheet -- Click on one object Hold the Ctrl key, and click on additional objects To select all the objects on the sheet -- Choose Edit>Go To, click Special Select Objects, click OK Or, to work with specific objects, you can add the 'Select Multiple Objects' tool to one of your toolbars: Choose Tools>Customize Select the Commands tab...
sort data without changing pattern
I have a worksheet where every other row has a 'pattern' in it to help make it easier to read across the spreadsheet. The problem I'm running into is as I add new rows at the bottom of the spreadsheet and then resort them into their correct place, the patterns go with the sort and now the patterns have to be redone again the get them back to every other row. Any suggestions? This will happen if your patterns are manually applied. It would be better to remove that manual shading and to apply background colours using conditional formatting, the condition being if th...
Customizing Contact Data
Is there a way to customize contact card/data to collect data from others that I want to use rather than the generic that Microsoft has used? -- Thank you very much. Tall Paul. "TallPaul" <TallPaul@discussions.microsoft.com> wrote in message news:1C1DDD4F-BDCB-42F8-8F99-A2D674482E8E@microsoft.com... > Is there a way to customize contact card/data to collect data from others > that I want to use rather than the generic that Microsoft has used? What type of information do you want to collect? -- Brian Tillman [MVP-Outlook] TallPaul wrote: > Is...
Data Validation Allow List
Is there a way to click on the resulting combo box and have ALL the list items show up instead of just what seems like the default of about 8--I can scroll, but would like to just point and choose? Also, is there a way to change the font of this combo box? The resulting look like they are about 6 or 8 points. Any help is appreciated. Is this the right forum doug? If you are using a combo box (from the control toolbox) as opposed to a validation list you can change visible rows but not if you use validation Debra has a solution here http://www.contextures.com/xlDataVal10.html -- R...
Obtain data from list
I want to get the data from the list on to a cell in worksheet. I have 2 sheets - On Sheet 1 - I have drop downs. Whatever is selected in the list - I want to show that value in a cell on Sheet 2. Any help is greatly appreciated. Thanks! Hi, I think if you just change the properties of your dropdown so that the linked cell refers to the cell on your other sheet, it should work fine. Right-click on the dropdown and goto properties...Linkedcell. jeff >-----Original Message----- >I want to get the data from the list on to a cell in >worksheet. > >I have 2 sheets -...
Updating large pivot source data
Hello, I'm experiencing the following problem with a very simple macro in Excel 2007 to update pivot table data source. That is, my data has more than 74000 rows and I'm trying to update the source data with the following code: Sub Macro1() Dim rng1 As Range Set rng1 = Sheets("Test_sheet").Range("A1:A74000") ActiveSheet.PivotTables("Pivot-taulukko1").ChangePivotCache ActiveWorkbook. _ PivotCaches.Create(SourceType:=xlDatabase, SourceData:=rng1, _ Version:=xlPivotTableVersion12) End Sub The problem is that the code ...
Best way to read an xml file
Hi; If I need to read an XML file - basically the SAX approach where I just need each node in order - one pass, read-only - what is the best way to do this. It looks like it's XmlReader but I want to verify that. ??? -- thanks - dave "David Thielen" <thielen@nospam.nospam> wrote in message news:BEA51BCB-B200-4104-B061-CA32FECD9DFD@microsoft.com... > If I need to read an XML file - basically the SAX approach where I just need > each node in order - one pass, read-only - what is the best way to do this. Yes, if your only requirement is a firehose forward reader,...
Large Files with Strongly Typed Dataset
I have an xsd file that I use to create a strongly typed dataset in my project. In the past, I have used the ReadXml method to load xml files into the generated class and read data in using this class. These files have now become big (order of 100MB) and this completely kills the readxml performance. I have read multiple posts that tell you not to use datasets for large files. I even tried calling BeginLoadTables before starting the read. However, I really need the strongly typed dataset functionality in my program - it forms the core of my project. Is there a way I can load this data into t...
Missing rtl70bpl file
After receiving what seemed to be a false positive from Avast free Home edition I now get (after every restart), the following... "This application has failed to start because rtl70bpl was now found. Reinstalling the application may fix the problem". Appreciate any advice on how to reinstall same. Have Googled for help but have not found the correct site. TIA. franktee Winxp Pro SP3 On Dec 3, 11:42=A0am, franktee <frank...@discussions.microsoft.com> wrote: > After receiving what seemed to be a false positive from Avast free Home > edition I now get (afte...
Cannot edit customer data in additional tab at HQ
I have created global customer with addtional data in additional tab at shop then send to HQ. At HQ, Why cant I edit customer data in additional tab? Or even I create new global customer at HQ, I cannot add data in addtional tab too, it is gray out. Is there any setting that I need to do? Thank you for any help. -- Jeed I believe, just like when editing items in HQ, you have to be in Maintenance Mode. Ed -- RMS 2.0 / HQ 2.0 Server 2003 / XP "Jeed" wrote: > I have created global customer with addtional data in additional tab at shop > then send to HQ. At HQ, Why ca...
converting data
Have made an x-y scatter chart with temperature on the y axis and time on the x axis.The data was imported and the temperature is in centigrade.How can I convert the data into degrees fahrenheit? The temperature data is in column B and contained in over 400 cells The only way I know how to do this is manually converting each cell but this will take forever.Can anyone show me how to convert the entire column with a formula perhaps? -- sghioto ------------------------------------------------------------------------ sghioto's Profile: http://www.excelforum.com/member.php?action=getinfo&am...
Format Cells Date (or any change) not working on imported data
Hello, I've just spent ages researching this and not come up with what I need to be able to do. I have a worksheet for some simple data that has been imported, a date, text and number column (as they display graphically to the end user). All are a "general" format when using Format > Cells. The issue I have is that the date information is in an American date format and I would like to change them into a UK date format. Format > Cells and selecting any option (including custom and special) makes no changes to the imported data. I have seen the work arounds whereby you sp...
Pulling/Collating Data
Hi, I have a workbook with 31 sheets (diary) A9:F9 (and other rows) is the info I want with the employee's initials in G9 There are multiple entries for the employee so he may reappear in row 13 and 15 I want to pull all the lines that relate to that employee from the 31 sheets into a separate workbook Thanks What is the name of the "separate workbook"? And in what sheet of this "separate workbook" do you want this data placed? Do you want only that data that pertains to that one employee whose initials are in G9? G9 of what sheet? Otto &qu...
Hello everyone, I'm not sure if I should be posting this question here or in the Excel forum but here goes. Is it possible to copy data from multiple cells in Excel and then paste them into multiple lines of the criteria section of an Access query? For example, Given cells and values: A1- 1 A2- 2 A3- 3 I would like to be able to copy this data from Excel and paste it into an Access query like : Criteria: 1 or: 2 3 I am using Access 2002 SP3 and Exc...
Printing data forms
I would like to be able to print Excel data forms. Is there a way? In some event of the form (a button, a click of the form itself, whatever): Me.PrintForm "Curious" <anonymous@discussions.microsoft.com> wrote in message news:170101c47bf9$f9191420$a301280a@phx.gbl... >I would like to be able to print Excel data forms. Is > there a way? If you mean the form you get when you do Data=>Form with the form displayed, do Alt+Print Screen. then drop the form and go to a new worksheet and do Edit=>Paste This will put an image of the form on the worksheet and yo...
Free Data Process/Query Tool
If you use Excel everyday and spend lots of time on data/text processing, then you will find the this tool (UDQ Add-in) is very powerful and can speed up your daily data processing work. Currently the tool is free for everyone. Please feel free to distribute this tool to your friends/colleagues if you think it is helpful to your work. The following is a list of features/functionality of the tool: 1> Query/Import Data from Multiple External Data/Text/ASCII Files (Can useful import data from hundreds of files within minutes) 2> Query/Import Data from Multiple Closed Excel Files (Can...
Consolidation Worksheet Data Sequence
I'm consolidating 3 worksheets into one by using Excel 2007 data consolidation. The worksheets have a product number in column A and sales figures in columns B, C & D for Jan, Feb and Mar. The worksheets are in product number sequence. Not all product numbers appear on each sheet, so I consolidate by category using "labels in left column" and "create links to source data" to create a consolidation sheet in outline form. The problem is the consolidated worksheet is not in product number sequence as I need it to be. I have books on Excel 2007 and Excel 2003 and their ...
Hidden files in Ms-Query cause ODBC connect errors or Query is wac
I have a Query fetch_from_bob that moves data (collapses records,unique key search, etc...) from excel spreadsheet "bob" to excel spreadsheet "fred". Fred and bob live in the same directory" c:\123directory" on the same computer. I allow this query to execute/refresh data automatically when one of the query paramaters is changed. This works great. I also have a macro that invokes the query to refresh the data whenever the user desires. This works great as well. The macro literally has all the VB code to execute the query. But I'm just a part time progra...
Filtering on text field
I am using an on Double Click event to filter based on the current value on that field. It works when I use it on a number field: Me.Filter = "[forMthYr]=" & Me![cbMthYr] Me.FilterOn = True but when I apply the same code (below) to a text field it does not work - I get parameter boxes popping up. Me.Filter = "[ITResource_LName]=" & Me![tbITResource_LName] Me.FilterOn = True Any help is appreciated. Zag -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200712/1 It works on a numeric field because there are no d...
Limits on Exporting Outlook Contact Data
Anyone know how to get custom fields, follow-up flags, and/or the "contacts" (links between contacts) from MS Outlook 2003 to Excel, CSV, Goldmine, or Act? The standard export utility in Outlook does not offer export of these fields. Most important is the links between contacts. You'd have to write your own code to do this. A key issue would be what information from the linked contact(s) you want to include. -- Sue Mosher, Outlook MVP Author of Microsoft Outlook Programming - Jumpstart for Administrators, Power Users, and Developers http://www.outlookcode....
Outlook: Can't open JPEG files
I have Outlook 2000 an unable to open JPEG files. What setting has to be changed to open these files? ...
File Edit toolbar is missing
The File Edit toolbar in Excel is missing First thing to check: Tools|customize|toolbars tab|make sure worksheet menubar is selected. Well, maybe the first thing to check is to verify that you can see all of excel--it's not just off the display window??? stellarmellor wrote: > > The File Edit toolbar in Excel is missing -- Dave Peterson ...
8192 rows limit when opening wk1 files ?
Hello, I am opening a .wk1 file, which contains around 60K rows, but only the first 8192 rows appear in Excel. Is there any way to have all the rows in the Excel file ? Thanks, Paul "paul" wrote... >I am opening a .wk1 file, which contains around 60K rows, >but only the first 8192 rows appear in Excel. > >Is there any way to have all the rows in the Excel file ? WK1 as in Lotus 1-2-3 Release 2.x native file format? Such .WK1 files support a single worksheet of 256 columns and 8,192 rows. PERIOD. Did you generate this '.WK1' file using a program other then...
Counting Repeated text or duplicates in a list
I need a simple formula to count the total number of repeated text in a list (column). Say there is thousands of entries (confirmation numbers) both numeric, alphabetic and alpha-numeric but many of the entries are repeats. How can I calculate in one cell the total number of repeated instances in the list? And I can't sort the list, just calculate the number of repeated instances in the list just the way it is, and the list is still growing, so I need the formula to be able to count all instances in the list even as they are being added. plz help ! =COUNTA(A1:A50)-SUMPRODUCT((...
Select Rows dependant on certain field data
How can I select certain rows in a spreadsheet based upon the text foun in one column? To explain: All rows have a column that contains tex "immediate" or "ongoing" or "closed". How can I select only the row that contain the word "immediate" in that column? When I say select a mean select as in ready to 'copy' or 'cut' tha data ready to be pasted elsewhere. I know what I mean :confused -- Madd ----------------------------------------------------------------------- Maddy's Profile: http://www.excelforum.com/member.php?action=getinf...
how to select multiple text boxes in excel for formatting
I am trying to select multiple text boxes for formatting the font but seem unable to select all of them other than to click on each one individually. Is there an easy way to select all of the text boxes at once? To select multiple objects on the sheet -- Click on one object Hold the Ctrl key, and click on additional objects To select all the objects on the sheet -- Choose Edit>Go To, click Special Select Objects, click OK Or, to work with specific objects, you can add the 'Select Multiple Objects' tool to one of your toolbars: Choose Tools>Customize Select the Commands tab...
sort data without changing pattern
I have a worksheet where every other row has a 'pattern' in it to help make it easier to read across the spreadsheet. The problem I'm running into is as I add new rows at the bottom of the spreadsheet and then resort them into their correct place, the patterns go with the sort and now the patterns have to be redone again the get them back to every other row. Any suggestions? This will happen if your patterns are manually applied. It would be better to remove that manual shading and to apply background colours using conditional formatting, the condition being if th...
Customizing Contact Data
Is there a way to customize contact card/data to collect data from others that I want to use rather than the generic that Microsoft has used? -- Thank you very much. Tall Paul. "TallPaul" <TallPaul@discussions.microsoft.com> wrote in message news:1C1DDD4F-BDCB-42F8-8F99-A2D674482E8E@microsoft.com... > Is there a way to customize contact card/data to collect data from others > that I want to use rather than the generic that Microsoft has used? What type of information do you want to collect? -- Brian Tillman [MVP-Outlook] TallPaul wrote: > Is...
Data Validation Allow List
Is there a way to click on the resulting combo box and have ALL the list items show up instead of just what seems like the default of about 8--I can scroll, but would like to just point and choose? Also, is there a way to change the font of this combo box? The resulting look like they are about 6 or 8 points. Any help is appreciated. Is this the right forum doug? If you are using a combo box (from the control toolbox) as opposed to a validation list you can change visible rows but not if you use validation Debra has a solution here http://www.contextures.com/xlDataVal10.html -- R...
Obtain data from list
I want to get the data from the list on to a cell in worksheet. I have 2 sheets - On Sheet 1 - I have drop downs. Whatever is selected in the list - I want to show that value in a cell on Sheet 2. Any help is greatly appreciated. Thanks! Hi, I think if you just change the properties of your dropdown so that the linked cell refers to the cell on your other sheet, it should work fine. Right-click on the dropdown and goto properties...Linkedcell. jeff >-----Original Message----- >I want to get the data from the list on to a cell in >worksheet. > >I have 2 sheets -...
Updating large pivot source data
Hello, I'm experiencing the following problem with a very simple macro in Excel 2007 to update pivot table data source. That is, my data has more than 74000 rows and I'm trying to update the source data with the following code: Sub Macro1() Dim rng1 As Range Set rng1 = Sheets("Test_sheet").Range("A1:A74000") ActiveSheet.PivotTables("Pivot-taulukko1").ChangePivotCache ActiveWorkbook. _ PivotCaches.Create(SourceType:=xlDatabase, SourceData:=rng1, _ Version:=xlPivotTableVersion12) End Sub The problem is that the code ...
Best way to read an xml file
Hi; If I need to read an XML file - basically the SAX approach where I just need each node in order - one pass, read-only - what is the best way to do this. It looks like it's XmlReader but I want to verify that. ??? -- thanks - dave "David Thielen" <thielen@nospam.nospam> wrote in message news:BEA51BCB-B200-4104-B061-CA32FECD9DFD@microsoft.com... > If I need to read an XML file - basically the SAX approach where I just need > each node in order - one pass, read-only - what is the best way to do this. Yes, if your only requirement is a firehose forward reader,...
Large Files with Strongly Typed Dataset
I have an xsd file that I use to create a strongly typed dataset in my project. In the past, I have used the ReadXml method to load xml files into the generated class and read data in using this class. These files have now become big (order of 100MB) and this completely kills the readxml performance. I have read multiple posts that tell you not to use datasets for large files. I even tried calling BeginLoadTables before starting the read. However, I really need the strongly typed dataset functionality in my program - it forms the core of my project. Is there a way I can load this data into t...
Missing rtl70bpl file
After receiving what seemed to be a false positive from Avast free Home edition I now get (after every restart), the following... "This application has failed to start because rtl70bpl was now found. Reinstalling the application may fix the problem". Appreciate any advice on how to reinstall same. Have Googled for help but have not found the correct site. TIA. franktee Winxp Pro SP3 On Dec 3, 11:42=A0am, franktee <frank...@discussions.microsoft.com> wrote: > After receiving what seemed to be a false positive from Avast free Home > edition I now get (afte...
Cannot edit customer data in additional tab at HQ
I have created global customer with addtional data in additional tab at shop then send to HQ. At HQ, Why cant I edit customer data in additional tab? Or even I create new global customer at HQ, I cannot add data in addtional tab too, it is gray out. Is there any setting that I need to do? Thank you for any help. -- Jeed I believe, just like when editing items in HQ, you have to be in Maintenance Mode. Ed -- RMS 2.0 / HQ 2.0 Server 2003 / XP "Jeed" wrote: > I have created global customer with addtional data in additional tab at shop > then send to HQ. At HQ, Why ca...
converting data
Have made an x-y scatter chart with temperature on the y axis and time on the x axis.The data was imported and the temperature is in centigrade.How can I convert the data into degrees fahrenheit? The temperature data is in column B and contained in over 400 cells The only way I know how to do this is manually converting each cell but this will take forever.Can anyone show me how to convert the entire column with a formula perhaps? -- sghioto ------------------------------------------------------------------------ sghioto's Profile: http://www.excelforum.com/member.php?action=getinfo&am...
Format Cells Date (or any change) not working on imported data
Hello, I've just spent ages researching this and not come up with what I need to be able to do. I have a worksheet for some simple data that has been imported, a date, text and number column (as they display graphically to the end user). All are a "general" format when using Format > Cells. The issue I have is that the date information is in an American date format and I would like to change them into a UK date format. Format > Cells and selecting any option (including custom and special) makes no changes to the imported data. I have seen the work arounds whereby you sp...
Pulling/Collating Data
Hi, I have a workbook with 31 sheets (diary) A9:F9 (and other rows) is the info I want with the employee's initials in G9 There are multiple entries for the employee so he may reappear in row 13 and 15 I want to pull all the lines that relate to that employee from the 31 sheets into a separate workbook Thanks What is the name of the "separate workbook"? And in what sheet of this "separate workbook" do you want this data placed? Do you want only that data that pertains to that one employee whose initials are in G9? G9 of what sheet? Otto &qu...