Posts by jronaldlee

    Re: Import text from file in all subfolders

    Thanks, vbEnthusiast - this gets me half the way there.

    I still need to cycle through all the subfolders. I imagine it's something along the lines of declaring the parent folder, then saying for each subfolder in the parent, go open the text file. I'm still stuck there.

    Thanks again!

    I have a source folder:
    C:\Sample Files\Month1\
    Month1 has several subfolders. In those subfolders there are files named PAU_1.txt and/or PAU_2.txt.

    I'd like Excel to open and import the text from each PAU_#.txt file that exists. I've done some searching, but the posts on searching subfolders that I've found have confused me more than helped.

    Thanks in advance for any help you can give me.

    I want to put some code in Private Sub Workbook_Open() that will prevent the spreadsheet from asking if the user wants to update links. Is there a way to do this?

    In other words, I've found how to not update (or forcibly update) links if I'm opening a spreadsheet via a macro, but I don't want this to be a macro driven event. I just want my users to be able to open the spreadsheet, and not have them asked if they want to update links.


    Excel 2010:

    I have a couple shared workbooks where I work, and every now and then, when I open a file, all the formatting has been changed. The "Normal" version of the text has been switched to "Time" format.

    To fix it, I have to make sure everyone exits the spreadsheet, unshare, and redo the formatting of "Normal" from time to general, correct miscellaneous formatting issues that are leftover, then reshare the file. Some of the spreadsheets are fairly complex, so this is challenging at best.

    This begs a few questions:

    • How might this be happening? None of my users are Excel savvy, and none are intentionally doing it.
    • Is there a way to figure out which machine/user is making the change?
    • How can I prevent it from happening?

    I'm combining multiple cell's data to create a single field, and whether I try to force carriage returns with vbNewLine, vbCR, or something else entirely, I keep getting a rectangular shape where all of my hard returns display. Here's the code I'm using:

    1. Sheet4.Range("ab25").Value = Sheet4.Range("j4").Value & " on " & Sheet4.Range("ae4").Value & ":" & vbNewLine & Sheet4.Range("j25").Value

    Is there something else I can use that doesn't product the rectangles in my text output?

    Re: Pivot Table Report Filters: Cycle through all?


    Thanks for the referral.

    For others, that link shows a loop like this:

    The line that reads pi.Value = pi.Value was not working for me. I found replacing it with this did the trick:

    1. Sheet1.PivotTables(1).PageFields(1).CurrentPage = pi.Value

    I'd like to use VBA to cycle through all the available options for one of the pivot table's report filters (and print between each cycle).

    The loop I envision is for i = 1 to # of entries in Report Filter: AgentName, and in each loop select the next entry and a little extra code (create a graph, print a report). The part I need help with is figuring out how to have it loop through the available entries in the Report Filter.

    Thank you!

    I'm building a table that will be populated via a macro, and need to check for the presence of existing data before adding a new row.

    I'm looking for variable AgentName in column A & variable AgentDay in column B. If the two are present in the same row, I need to return the row number so I can manipulate the data in that row. If the two are not present, I will append a new row of data at the bottom of the table.

    I know how to do it with big for/next loop:

    1. For i = 1 to 65000
    2. If Range("A1").Offset(i,0).Value = "" Then Exit Sub
    3. If Range("A1").Offset(i,0).Value = AgentName and Range("B1").Offset(i,0).Value = AgentDay Then
    4. 'Manipulate this data
    5. Exit Sub
    6. Else
    7. 'Call the code to add a row of data
    8. Exit Sub
    9. End If
    10. Next i

    ...but this code seems inefficient to me. As the table grows, that's a lot of looping. Is there a more efficient way to do this?

    I figured out the way I would name each of the elements in my chart. For example, the axis values are defined thusly:

    =OFFSET('Quarterly Results'!$C$8,100-COUNTBLANK('Quarterly Results'!$C8:$C107)-'Quarterly Results'!$R$7,0,'Quarterly Results'!$R$7,1)

    Where data can exist in the 100 cells from C8:C107, I am using a countblank because some cells have formulas that return "" under certain conditions, and R7 is a user controlled number of results to include in the chart. I can use similar definitions for the elements I wish to display in the chart.

    My goal is for the user to select a number of quarters to display by entering a number between 1 and 100 in cell R7, and the chart would expand its definitions to match that input.

    Is this possible? I did it with Spark Lines, and now I want to do it for my charts, too. :smile:

    Re: Speed Up For/next Data Retrieval


    That code goes through a loop that:

    1) checks to see if it is at the end of the list of names, and if so, aborts the for/next loop
    2) searches for the name of a company (Sheets("Model").Range("A33").Offset(i, 0).Value) in a table on another sheet, then returns seven data points from that sheet (7*500 companies = 3,500 times)
    3) calculates five more data points based on the seven it had previously retrieved and previous user inputs (5*500 companies = 2,500 iterations)

    I've been going through your golden rules for Excel/VBA, and I see more than one rule that I'm breaking throughout the code (before this snippet, during this snippet, and after). Between that and Wigi's suggestions, I decided to ditch the whole thing and start over again, trying to incorporate as many of these ideas as possible.

    I have a spreadsheet that takes known data from ~500 companies, and their percent of market share in various geographic markets, and then creates a virtual model of the geographic area based on that information to provide things like average, standard deviation, median, etc.

    The macro I'm writing sorts a pivot table to display the geographic area in question, pulls twelve points of associated data (per company) from a table, then creates the model. I seem to be running into trouble at the part where I'm pulling in data. At 500 lines and twelve data points, that's only 12,000 cells to populate. I was using VLOOKUP to accomplish this, but I need to trim the sheet down to size (6 MB) and speed up the macro (depending on user, a couple of minutes per analysis).

    I replaced the VLOOKUP with a FIND feature, but it's still pretty slow. Here's the loop in question:

    Am I terribly inefficient here? I'm sorry to offer such a broad question, but I feel like there are more efficient ways of pulling data from one worksheet to another, but I don't know how.