Hi,
I'm planning to use a workbook as a database to store information. I need to be able to open a weekly schedule and import the Kit and Equipment that matches the row. Is there an easy way to do this? I had a macro that worked but each item had to be coded in manually. That would be a bit much to keep up with.
Please see attached files. One is the DB and the other is an example schedule.
scheduleDB.xlsx
mySchedule.xlsx
Place this into the mySchedule wb. Also be sure that both files are in the same folder. Or change this line to the path of scheduleDB.xlsx
From
to
Please Login or Register to view this content.
Please Login or Register to view this content.
Please Login or Register to view this content.
Thanks,
Mike
If you are satisfied with the solution(s) provided, please mark your thread as Solved.
Select Thread Tools-> Mark thread as Solved.
Hello Mike,
This is a great solution for what I need. The only issue is the database book needs to be stored on a network drive. It keeps coming up with the error message that the file can't be found. I did set the file path but it doesn't like it no matter where I put the file. Any ideas on this?
Last edited by smugglersblues; 12-31-2012 at 10:42 PM.
How about if you create map drive to the network and assign it a letter, Like M:\path\folderfile
Mike,
I'm using C:\path right now. I have an idea of the problem so I'll post here and give an update when if it is solved or not. Thanks again for your time here.
Most the time on a Network drive its like //192.168.1.1/path to folder
I think it's just a formatting error on my part. In the code you gave, how can I set the worksheet to active sheet? The sheet changes name each week so it won't work if I have to rename the sheet each time. The db file name doesn't change so that is ok.
Change here, from with worksheets("sheet1") to with activesheet
and herePlease Login or Register to view this content.
Please Login or Register to view this content.
I haven't been able to get the file path to work properly. I'm using the C: path now for testing. But no matter where I place the file, it says it cant find it. Are there any other options for connecting to the file that isn't as picky about the location setting? Also, some of the schedules are sent from older versions of excel. Could that be a problem with the code?
Last edited by smugglersblues; 01-01-2013 at 02:24 PM.
Post the code you are attempting to use as is.
Actually, I just figured it out. It didn't like macro-enabled workbooks. So I just saved it as a regular workbook. Now it it will connect where ever the db book is located.
Last edited by smugglersblues; 01-01-2013 at 03:35 PM.
Ah, you had saved the database workbook as .xlsm yea that's a little different connecting string.
It should work the way it is for blanks. Does it not?
If the row is blank it stops. Also, if any cell is blank it won't return a value for me.
So if I have 10 rows and row 5 is blank, it will only give me a values for rows 1-4. If any cells are blank in row 1-4 it, for example, B2, then it won't return anything for row 2.
Can you upload the workbook in question? It should not stop if there is no value in column A. But it does use Column b thru e for the where clause for the part number. so if columns b thru e is blank it will not return anything. I was assumming all that info would be in the worksheet, being Partnumbers are not unique.
Example from your uploaded wb Partnumber AA123 as different DESCRIPTION SIZE and Aisle values in the columns. So I use those values to get the kit and description. Is that not whats supposed to be.
Here is the workbook with the macro installed. Also, would you mind posting the connection for a macro-enabled workbook? I would like to have a few macros in the schedule db to help format it for use.
if columns b thru e could be blank, we can change the query but I would need to know what to look for? Will it only be column b that is blank or can b thru e all be blank?
Ah, change where the a = range("a1").currentregion to
Please Login or Register to view this content.
And heres a link that will be useful for the connection strings. Scroll down to Xlsm files.
http://www.connectionstrings.com/excel-2007
Also when you say formatting, what king of formatting? The db sheet needs to be like it is without and mergedcells or formatting. Also the Headers need to remain in column A and named exactly like you have them now or you will need to change the code for the sSQL.
The schedule that I receive will always have a part number. Any of the cells from B thru E can be blank. It's completely random on which cells and row will be blank through-out the schedule. As for the db, I just want to be able to trim the white space for the sheet with one click of a button.
Last edited by smugglersblues; 01-01-2013 at 04:31 PM.
Give this a try. Change the path to your
Please Login or Register to view this content.
This one doesn't seem to know how to handle duplicates if B-E are the same. Would it be possible to use A-E or A-G as a composite key to uniquely identify the content and then place the correct F and G for each row? Here is a schedule that represents better the format that is sent to me. I highlighted the cells that are out of order so you can see. I placed the content to the right so you can easily see what each one should be.
Will Columns E and G always have data in those cells?
Not always.
Then will either E or G always have data. It will be difficult to to get the correct value if not.Would it be possible to use A-E or A-G as a composite key to uniquely identify
I take that back, I thought we want to get the values for Columns F and G. So how are we to use Column G as a composite key to uniquely identify?
Thats right, Fand G. I was hoping maybe the cells could be used collectively if it would make it easier.
Im not sure what you mean by collectively? Theres got to be a way to identify the part numbers uniquely some how within the row the part number is on.
the problem is that there is a part number that can have multiple values assigned to it. For example:
AA123 5 gall bucket
AA123 10 gall bucket.
The only way to find a unique value would be to use the AA123 and 5 gall bucket together to create a unique identifier. But, I think the first code you posted will work fine. I'll just run a macro to fill any blank cells with a "-" so it will continue with filling in the data. I've been testing it to see how it does in the case of the above examples. It hasn't missed yet. Thanks for all your time and work on this.
Which post are you refering to?I'll just run a macro to fill any blank cells with a "-" so it will continue with filling in the data
If you change this line where a = to the code below I think it should work where there are blank cells.
you may have it like this line below and its stopping at blanks.Please Login or Register to view this content.
Please Login or Register to view this content.
If for example, the Aisle column is empty, it wont fill the data for that row. But if there is content there it will fill the data. It just doesn't like empty cells.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks