Posts by hatman555

Re: How to put a value in a cell by clicking a specified range fo cells

Well that's going on the facebook right now. Brilliant! lol

Cheers,
Hat

Re: How to put a value in a cell by clicking a specified range fo cells

Code works perfectly to fit the description of his Request. Still unsure as to what use it will have. O.o

Cheers,
Hat

Re: How to put a value in a cell by clicking a specified range fo cells

Hows it going zelle132007.

This is my first post back after a long time away, but I would like to get back to answering questions and following up with these forums.

Down to business.

Rule #2
"Always use a relevant keyword thread title that titles what you are trying to do! NEVER use thread titles for what you THINK you want. You are posting BECAUSE you don't know."

What are you trying to do here?

I understand that you want to place certain values in certain cells quickly. Sounds like this might be a job schedual or time table of some sort, but if your asking the question it also may seem that your dealing with many, many fields to fill in.

Can you give us some more information about the project?

My first thoughts are these:
1.) If excel did have the functionality to do what you are saying, wouldn't this translate into alot of clicking for you?

2.) Would selecting the value you want, and the simple letting your hands do some work (AKA Crtl+C --> Ctrl+P) after clicking the cell be too much work?

Cheers,
Hat

Re: Import Text File From Specific Folder

Importing a Text file is the main goal yes, but targeting a folder was the main quandary and can be used for many more uses too. I know one should never ask to what he thinks the answer is, but i thought this would work and be a pretty cool trick to learn.

Dave

Re: Find Common Numbers Between 2 Columns

Would you be able to attach a small section of the spread sheet?

And just so we get the scope clear again. You have 50,000 Rows by 30 Columns, and each cell in that area contains a list of comma separated numbers?

Also, your question is making me think of something I did in the past, Locating the duplicates is often never the end result. What will you need to do with these numbers once they are found? Maybe your final result can be reach with out having to show you the duplicates.

Dave

Re: Import Text File From Specific Folder

I have read both those guides many times. I read them while I was posting my first post because like many other people on this forum I have seen how much of an enforcer you can be. Its great, having a strong admin keeps the spammers off the boards and the forums functioning better.

Would you explain in more words why your title "Import Text File From Specific Folder" is better than my original of "Targeting A Folder". If I understand why you thought mine was selfish I can take more care in future postings.

BTW, Before I post I do search the forums heavly for my answer. It was the automatic search after I had posted that made me stuble apon the "http://www.ozgrid.com/forum/showthread.php?t=68703"" tread, through which i was able to code my own answer.

Cheers,
DAVE

I have an Excel workbook with 2 worksheets in it. One of the worksheets imports a txt file and then the other worksheet displays data that formulas I have written have collected.

I usually import the txt file by running through the many steps with the "import data" option. This is good but I want it to be faster.

I used the macro recorder and ended up with the following code

With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;\\Desktop\Import Folder\Test.txt" _
        , Destination:=Range("A1"))
        .Name = "Test"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlOverwriteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = True
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = True
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = True
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With

This code is great because it has all the settings that I always use when importing the txt file. The only thing that will need to change is the target file often and sometimes even use this excel workbook on different computers.

"TEXT;\\Desktop\Import Folder\test.txt" _

Would there be a way to write a macro that when run would prompt the user to select the folder where the txt file will be located? Maybe assign the folders location to a variable that could then be inserted into the previously mentioned code?

Cheers,
Dave[hr]*[/hr] Auto Merged Post Until 24 Hrs Passes;[dl]*[/dl]I searched the forums a bit more to try and peice some other codes together.

I found this tread that takes about a modual that creats a function that graps the name of the target folder
http://www.ozgrid.com/forum/showthread.php?t=68703

Pulling some things from there i was able to make the following code.

Private Sub CommandButton2_Click()
    'Finds the location of the folder when the Get_Folder() module is used
    Dim wbkSrc As Workbook
    Dim wsSrc As Worksheet
    Dim fso, f, g
     
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set f = fso.getfolder(Get_Folder())
    Dim Target As String
   'I used these lings here to check that i have the right file location
    Target = f & "\Test.txt"
    
    MsgBox Target
  'It works, I get back in a string "FOLDER\File.txt"
  
  'Now I just need to implament it in here--->
  
    With ActiveSheet.QueryTables.Add(Connection:=Target _
        , Destination:=Range("A1"))
        'Just putting Target in there doesn't work
        .Name = "Test"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlOverwriteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = True
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = True
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = True
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=Flase
    End With
End Sub

So Any VB people out there that can help me use the right syntax to get that "target" string variable into that script.

Thanks,
Dave

SOLUTION FOUND!!!!!

After some more searching i found this Post,
http://www.ozgrid.com/forum/showthread.php?t=55830

Norie has comented on the correct syntax.

With ActiveSheet.QueryTables.Add(Connection:= _ 
    "TEXT;" & Target _

Thanks,
Dave

This might be sort of a Windows Office question, but I’m sure there is someone that knows the answer out there.

Using Excel 2003
When you go to the Tool-> Options menu of excel you get a window that displays various tabs with various options.

I’m looking for the location of the file or files that keep these settings.

Why? I work for a company that controls its thousands of computers by rolling out the same software with the same settings over its network. Nothing can be installed with out be set up on the master server. Every time you restart your machine anything new gets taken away and settings reapplied to the settings stored on the default roll out server.

Today for the first time they changed something with MS office. All my options in Excel got played around with. Random stuff got switch on, like R1C1 referencing and even the 1904 date system. Other stuff got switched off.

What I’m trying to do is to located the file that is save locally on my machine and copy it after I have set up the options the way I want them. If there is ever another roll out from our companies master server I can just do a roll out of my own with the old file and everything back to the way I had it.

Any information on where to look to find these files would be appreciated.

Thanks,
Dave

Re: Highlight Formula Reference On Another Sheet

Crtl-Z?

Honestly mate, I can't help but laugh a bit at this thread. Like we agreed on before, we both use formulas pretty heavily, but maybe you should think about restructuring your excel sheet to use formulas in a different way. If all your formulas that you need to check are located on one worksheet then you could use the key commands to get a visual really quick.

Long story short, more efficient design will lead to a more efficient debugging.

Good luck,
Dave

Re: Enter Date Without Separators

After looking over the formula I posted, I thought of a way to both simplify it and keep the date format in the output cell.

Here is the new formula:
=DATE(MID(A1,5,2),MID(A1,1,2),MID(A1,3,2))
A1 still contains the date in text format "032404"
B1 now contains the date value "24-Mar-04"

Now you can use column B as your "date field"

Good luck,
Dave

Re: Formula Highlighting On Different Sheets

Well,

I totally understand where you coming from with the not being able to list an example. But if you think of a way you could but it in to a different format with out the actual data then someone might be able to suggest something.

Then again...if the nature of your formulas gives away chaos theory or something....then you might not be able to.

Good luck!
Dave

Re: Enter Date Without Dashes?

Ok....Here is a brute force approach using formulas. Please, please, please, Excel masters don't scoff at me. It works, its just messy

If your Number value is in ddmmyy format in A1 here is an output in B1
=CONCATENATE((LEFT(A1,2)),"/",(RIGHT(LEFT(A1,4),2)),"/",(RIGHT(LEFT(A1,6),2)))
Make sure that A1 has a text format applied to it. That way the 0 in "032404" won't be chopped off.

Of course you'll need two use two cells for this...but hey, if you really need it...

Dave

Re: Update worksheet from Master workbook

Cool!

Let me know how it works out! I just made an excel spreadsheet that uses "import external data" too. I learned a lot about it, but not all of it I'm sure. So give a post if you discover anything groundbreaking!

Dave

Re: Enter Date Without Dashes?

TRY SOMETHING SIMPLE FIRST!!!!

Try this. Enter all your dates and then format the cells the way you want them. Its really easy, just select the cells you want to format and then go to Format -> Cells. In the Format cells window click on "custom" in the category field. Then change the type in the type field. Excel recognizes m as month and d as day and y as year. So you want to enter "mmddyyyy" the example that they use is mm/dd/yyyy...but you don't want those slashes right....so just take them out.

I hope this works for you.
Good luck,
Dave

Re: Update worksheet from Master workbook

This is how I get to it in Microsoft excel 2003.
Step by step
Start on your blank worksheet
Go to Import data (Data -> Import external data -> Import data)
A window opens that asks you to select the data source.
Navigated to your network file and select it. Click Open
Text Import wizard – step 1 of 3 Opens
Select the way you would like to separate out your data. Then click Next
Text Import wizard – step 2 of 3 Opens
Specify how you want to separate your data. Then click Next
Text Import wizard – step 3 of 3 Opens
Select the format of your data. Then click Finish
Import Data window Opens
Select where to Import your data AND HERE IT IS!!!!
Before clicking ok click on the Properties button
External Data Range Properties window opens
In here there are three different sections. Each section has a header in small blue font
Refresh Control is the one I was talking about
First check mark will enable and disable the Red exclamation mark prompting you to find the file
Then next two boxes I have not played with…but you might be able to have some fun with them. Play with the time settings. Change it from 60 minutes to like 1 minute and then modify data in the original file and see if it changes or not. Refresh data on file open could be cool too. I think this option makes it so that the Excel presses the red exclamation marks for you when you open your 7 worksheet file. I have no idea what the sub question mark under that will do though.

You’ll need to modify these properties in all 7 of your worksheets. But once its done…you might never need to do anything again.

So like I said…make yourself a local copy and play around…that way if anything goes wrong at least your data won’t be lost!

Good luck, and post back if your successful with anything. I would love to know if it works or not.

Dave

Re: Formula Highlighting On Different Sheets

I'm stubborn and by George I want to help. So would it be possible for you to type out an example of what your doing?

Because I know very little about VB I have been forced to do a lot of things with formulas. I have gotten quite good at using only formulas to do different things. I have used formulas to reference formulas to make locations for other formulas to reference. Really... If its possible to do with formulas in excel I think I have done it.

SO! Type out a little example of what you are trying to do and I might just be able to give you a solution to do it all on one worksheet with out needing to make so many formulas.

<|:-)
Dave

Re: Update worksheet from Master workbook

Cool!

Then ya, play with the options in the properties and see if you can get the timed update working. I don't know if you need to have the excel sheet open for it to count the minutes or not, but it would be cool if you didn't need to even touch anything, just open and new data is there.

Oh, Watch out for file name changes, I haven’t worked with this either, but I’m guess that Microsoft won't be smart enough to adapt if someone changes the name of the file.

Good luck,
Dave

Re: Formula Highlighting On Different Sheets

Lovejoy,

Use the forum rules to your advantage. Assume you know nothing. I know you know something, because it sounds like you are doing pretty well so far with formulas and such, BUT, try posting up a new forum post with just your problem. You can tell people what you have tried, but you really should just tell people what you are trying to do.

From your last post it sounds like you are trying to use the results in one cell as a reference in another...mixed with something else.

Make a new post and just ask everyone what you want to have as a final product with the data you have. Someone may give you a new idea on how to accomplish your goal. Trust in the forum. It worked for my question. It will work for yours.

Don't forget to finish your new post with a link to this post. And it might be helpful to reply to this post with a link to your new post.

Good luck, and remember. the more general the better. that way it will help others too!
Dave

Re: Update worksheet from Master workbook

Possible solution, and other users correct me if I’m wrong. "Import external data" may continue to be your solution.

Once you have your workbook and worksheet formatted the way you want them. Click on View->toolbars->External data. On this toolbar there is a button that has a red exclamation mark. Its called "refresh data". When clicked it will open “select file” box already pointing to the file that was referenced. If this is the file you would like to continue using then just click "import". If you are pulling the files from a network folder that you have access to then you will be able to do this quite easily.

I tried it with a couple of cells with bold formats and it kept the formatting. Give it a shot and see if it works for your needs.

If it does work. you could even try playing with the advanced features of "import external data". On the last step of the process you will have a window that asked you "where do you want to put the data?" click on the properties button and look at the "refresh control" section. I believe if you remove the "prompt for file name on refresh" check box, the Red exclamation mark button will now just assume that its the same file in the same location. You can even ad options to refresh every X minutes.

Make a local copy of your files and play with it a little.

Hope that helps,
Dave

Re: Formula Highlighting On Different Sheets

When you are on a worksheet you can quickly view what cells are being used in a formula with out needing to double click the formula bar. Use these key shortcuts.

Find what cells are being used:
Click on the cell with the formula and press " Ctrl + [ "
-This will select all the cells that this formula is using.

Conversely, Find what cells are using that cell as a reference:
Click on the cell and press " Ctrl + ] "
-If you get a message that tells you "No cells were found" then you know that the cell you selected is not contributing to any formulas and thus can be edited safely.

Here’s the problem. Excel doesn't let these key commands work across worksheets. Just like excel won’t show cell references across worksheet as highlighted colored boxes.

The only thing that I know of that might be of some help to you is excel's "formula auditing" tool bar. Click View->tool bars->formula auditing. Use the "Trace Precedents" button. Its the second from the left on the toolbar. Click this button and you will now see arrows pointing from the cells that are being used to the cell where the formula is. A grey dotted line will show that there are cells in another work sheet. If you double click this grey line, the "go to" window will open. It will list the other cells being used. if you click on one of the cells in the list and then click OK, you will be taken to that cell and it will have been selected. Use this to find and edit the cells being used.

Hopefully you can find some use out of this.

Yet the real question is: Why are you looking to highlight these cells?

Maybe someone knows a code to actually apply a color code to a cells dependents.

Cheers,
Dave