I have this code below which opens a closed workbook and copies from relevant cell.
But what I would like to do is when the file path gets to the actual book name, this name would be found In A1. So if A1=WK1 it will open the file WK1.
Is this possible?
Please Login or Register to view this content.
Replace "book1.xls" with a reference to A1.
Please Login or Register to view this content.
If posting code please use code tags, see here.
Yes worked a treat!!!!!!!
How would I go about the same with the sheet name but I know what the file name is?
Also I've got myself confused on this now. It says: Path,File,Sheet,Address, But this line is in the order of Path,Sheet,file,address???
Its been a long day!!!!!
You've got me confused too.
Why are you confused about the order of the arguments?
The "Sheets(Summary)" was thrown in there before the file location, I've taken it out, and works still . That summary sheet is where the macro button actually is.
On which worksheet does A1 have the filename?
I assumed it was Summary so I included a reference for that worksheet.
If it's not Summary you should add a reference to which worksheet it is.
Bugger I didnt save it and forgot what I done!!!!! lol. Basically the file path is there. THEN it should be A1 value then "Sheet1","E5"
The summary sheet is where the data is pasted in, the active work book
This just returns the value, ie the filename, from A1 of Summary.
It has nothing to do with which sheet to take the data from in the closed workbook.Please Login or Register to view this content.
Yeah I see it, i'm really not with it this evening, But I did delete something and I can remember but it still worked? god knows what lol.
If I was trying to do the same for a SPECIFIC file E.G "Book1" but wanted a ceratian tab which the name would be in A2 would I simply paste that part into the sheet part and change it to A2?
It the workbook name and worksheet name are in A1 and A2 on Summary this should work.
Please Login or Register to view this content.
Hi, I got it to work by putting the actual file name in then changing the sheet like described ( I actually used B1) but it will return a Zero. Is there something else in the code that would need changing? Theres something near the end that says
X1C1R1?????
Please Login or Register to view this content.
Was it not working when you were taking the file and sheet names from cells?
As for the code returning nothing, I'm not 100% sure how the code works.
One thing I did notice was this:
What range is that meant to refer to?Please Login or Register to view this content.
If Address was, say, E5 that would refer to F5.
Everything worked when everything was kept in A1, the file location, then i changed it so A1 held the TAB name, absolutely fine, But I'm going to need SEVERAL of these on the same sheet, so I'm going to have around 4 cells which will have dates, week numbers, shifts, and these will tie into the file names on the system.
So when I used B1 and changed everything I seen that contained A1 to B1 I got the return value of 0. I got this online, so I really don't totally understand it, but I understand that line we have been working on nowI need to walk before running on this I think! But local library has no books on this type of thing!!!!
If I find out, I will let you know what this means!
But what you have done so far is pukker
Why don't you just open the workbook?
Basically, Some pleb in work thinks its a bright idea to duplicate work for us, the spread sheet we have contains info about all targets figures etc, and these are spread across loads of different workbooks, so this is just scratching the surface! So in the end I will have across the top rows, DATES,SHIFTS,WEEKNUM etc etc, this will be all the info I need, as all the files will contain these types of words, and they change daily.
So in the morning, I want to simply hit a button or two, and it will fetch all the data required rather than spending half an hour in the morning opening up all these workbooks.
So one example would be, file name is WK1 so A3 would have a WEEKNUM formula, and the cell that requires this figure will have the filepath plus this A3 value to open up the current weeks data. Some files hold a date as a name which would be held in A1 and so on.
PLUS I kind of get to learn which is harder than anticipated for the time being.
I was meaning use code to open the workbook, grab the relevant data then clos it.
How would that work?
Something like this perhaps.
Might seem a bit much for one value from one workbook but I assume there are more values/workbooks.Please Login or Register to view this content.
If there is additional code can be added to handle them efficiently.
Well it's been some time again since I last looked at all this, and I started up again last week with many changes. Soooo I currently have the code below working for workbooks,filenames and ranges which are KNOWN.
Now what I would like to do is openup a KNOWN file location, a KNOWN book, and a sheet which will START with "3 days" PLUS A1 value which is the date as the sheet is called "3 days 25.1.13" the date will change weekly hence the A1 value
This is what I have currently:
Now I would like to add a line in which looks likePlease Login or Register to view this content.
The highlighted part is the bit i'm having trouble withPlease Login or Register to view this content.
but so far its been working well!
You can try this.
I've used Text to get the value in A1 as it's formatted on the sheet. Actually that remnds me, you should add a worksheet reference for A1.Please Login or Register to view this content.
Worksheet reference for A1? Whats that sorry
If you want to reference A1 on worksheet 'Summary' you would add a worksheet reference like this.
If you don't have a worksheet reference Range("A1") will refer to A1 on the active worksheet, which may not be the right one.Please Login or Register to view this content.
Well this is how it stands now, and what I get now, is the file window pop up to look for the file. I typed it all in properly so I know it WOULD work, but its combining the two together which is playing up.
Please Login or Register to view this content.
ignore the 3rd line '3days + A1 ....... thats something I wrote in there to remind me
Like I said you should add a worksheet reference to ensure the code picks up the value from A1 on the correct worksheet.
Like this?
Please Login or Register to view this content.
No.
You need it here.
Please Login or Register to view this content.
Thats what I have written above highlighted in blue don't I?
Please Login or Register to view this content.
It should be this.
It's the highlighted text that needs the worksheet reference.Please Login or Register to view this content.
Same thing unfortunately, it opens up the window to select the file
But I can see where I went wrong originally, I didnt add the "sheets (data)" part
If that's happening it means that something is wrong with the path, the workbook name or the worksheet.
I thought (hoped) the problem would just be because you were looking at the wrong worksheet, so picking up the wrong worksheet name.
Have you checked there's a workbook called 'book1.xlsx' in "C:\Users\Duane\Documents\Work"?
What happens when you run this code?
Do you get a blank message box or one with the file name?Please Login or Register to view this content.
Last edited by Norie; 01-27-2013 at 04:44 AM.
Ill have to have another look again tonight when I get back in, it works when I write the ACTUAL file path.
Like in comment #23
It works (highlighted in red), but when I combine it (3 days + A1 value) not working . I've even formatted A1 to give the right type of date format to look EXACT.
I'll post a sample of both books this evening. This is the only thing left to pull the last 3 bit of data(excel documents anyway)
Going to need a crash course in extracting data from applications if this is even feasable!
I'll take a look at the workbook when you upload it.
I probably should have suggested attaching it earlier but I wasn't sure it would help because there were other workbooks involved.
Book1.xlsData Collection.xls
Here they are, I made up a sample to give an idea. I had thought of another way round which works mind! I now have a formula:
Formula:
Please Login or Register to view this content.
Which gives me the "sheet name" required!
So from the advice earlier you gave me, I can now name the sheet, as A1 value!!!!!!! As its all there.
But this would be very handy to crack for future projects
Ignore the "Main Sheet" that has a second macro which has been deleted which may have been confusing which I was also looking at.
What was in that cell before?
When I run the code I get an open file dialog which is apparently looking for a file called 'Work[book1.xls]3 Days 25.1.13'.
Now, I'm not sure but that doesn't seem right.
I think the problem could have been down to a missing \ in the path name.
When I run this code it only looks for a workbook called Book1.xls.
Please Login or Register to view this content.
Last edited by Norie; 01-29-2013 at 09:55 AM.
Data Collection1.xls
Try this
But obviously change file path to where you've saved it.
There are two macro. One that says working, which is fine if you write the actual file path to test its there and would work
and the other which don't
Thanks
Haha, yeah I see the silly mistake, Maybe that could have been messing me around most of the time?
now it returns #REF
I can't test the code.
By the way, have you considered just opening the file(s), grabbing the value(s) and then closing?
The code isn't complicated and if you made any mistakes in it, eg incorrect filenames, VBA wouldn't be slow in telling you.
By the way, I've just had a quick shifty at some earlier posts and guess what, the missing \ was included.
Last edited by Norie; 01-29-2013 at 11:36 AM.
At the moment this has grabbed everything pretty fine, and now that I have that formula with the text to combine its grabbing what I need.
But anything else would be handy for me to learn.
Reason I've been asking to do it this way, is because most files and sheets are named as Weeks and Start dates, so by Having these dates and weeks in certain cells, I was hoping to combine like I have here to open what sheets are required. I'm looking to extract 114 bits of data from doing this, which Is why I want it as a one button push and using cell values to grab the right books and sheets.
But again, any other ways are welcome
This code doesn't open any workbooks/sheets/whatever.
It creates formulas that refer to closed workbooks.
You could use the exact same data as you have to actually open workbooks etc.
That could be done with a one button push, or a no button push if you wanted it to happen when the workbook opened.
No, But all I want is the data from the cells which Its doing.
I currently have this which Is running fine:
Work book open sounds even better
What do you have in mind?
Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks