Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,comp.databases.ms-access
|
|||
|
|||
importing Data from access
I have been attempting to create a reservation planning form in excel
that imports Data from an Access database and inserts that information automaticly into the correct spreed sheet and the correct cells within that worksheet. The Excel Book is made up of 12 worksheets named Jan-Dec. Each worksheet has columns labeled as each day of that month. Column 'A' is reserved for 19 rows named "room1 - room19". The data I am importing from the data base is " start date", "end date" and "room number". I am also importing " surname" which i have intended to use as the data that is actually inserted into the cell which is hopefull pinpointed using the other information. I have had an attempt at using VBA but totally unsuccessful. I am hoping that someone could point me in the right direction. Please |
#2
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,comp.databases.ms-access
|
|||
|
|||
importing Data from access
are you working on an hotel reservation system?
|
#3
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,comp.databases.ms-access
|
|||
|
|||
importing Data from access
Hi Darren
i've just done something very similar (with heaps of help from this & other ngs) the way i approached it was 1) use a make-table query in access to extract the data that i want to use in excel to one table 2) open excel and use data / get external data / database query to return all the data from the table into one sheet (called imported from access) 3) then linked the fields in the other sheets to the correct fields in this sheet (using a combination of offset / match & lookup functions - depending on how "variable" the data is) 4) wrote code to prompt to refresh the data when the workbook is opened - when the data is refreshed it saves the workbook with a new name & unlinks the query from access this might give you a plan of attack - Cheers JulieD "Darren" wrote in message om... I have been attempting to create a reservation planning form in excel that imports Data from an Access database and inserts that information automaticly into the correct spreed sheet and the correct cells within that worksheet. The Excel Book is made up of 12 worksheets named Jan-Dec. Each worksheet has columns labeled as each day of that month. Column 'A' is reserved for 19 rows named "room1 - room19". The data I am importing from the data base is " start date", "end date" and "room number". I am also importing " surname" which i have intended to use as the data that is actually inserted into the cell which is hopefull pinpointed using the other information. I have had an attempt at using VBA but totally unsuccessful. I am hoping that someone could point me in the right direction. Please |
#4
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,comp.databases.ms-access
|
|||
|
|||
importing Data from access
"WindAndWaves" wrote in message ...
are you working on an hotel reservation system? Thats right. Any Ideas |
#5
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,comp.databases.ms-access
|
|||
|
|||
importing Data from access
"JulieD" wrote in message ...
Hi Darren i've just done something very similar (with heaps of help from this & other ngs) the way i approached it was 1) use a make-table query in access to extract the data that i want to use in excel to one table 2) open excel and use data / get external data / database query to return all the data from the table into one sheet (called imported from access) 3) then linked the fields in the other sheets to the correct fields in this sheet (using a combination of offset / match & lookup functions - depending on how "variable" the data is) 4) wrote code to prompt to refresh the data when the workbook is opened - when the data is refreshed it saves the workbook with a new name & unlinks the query from access this might give you a plan of attack - Cheers JulieD "Darren" wrote in message om... I have been attempting to create a reservation planning form in excel that imports Data from an Access database and inserts that information automaticly into the correct spreed sheet and the correct cells within that worksheet. The Excel Book is made up of 12 worksheets named Jan-Dec. Each worksheet has columns labeled as each day of that month. Column 'A' is reserved for 19 rows named "room1 - room19". The data I am importing from the data base is " start date", "end date" and "room number". I am also importing " surname" which i have intended to use as the data that is actually inserted into the cell which is hopefull pinpointed using the other information. I have had an attempt at using VBA but totally unsuccessful. I am hoping that someone could point me in the right direction. Please Hi Julie Thanks for the info. I have got as far as importing the data to a new worksheet but I am now having problems linking the Data with my original form ( basicly haven,t got a clue as to how this is done). i assume that some of the data imported i.e (start date , end date and room number) will somehow need to go into the offset formula to locate the cell the data needs to go into. Would I be correct to say that the formula should be similar to this: offset (A1, startdate, room number, 1 , total number of days) OFFSET (reference,column,row,hight,width) I can see a little light at the end of the tunnel. Darren |
#6
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,comp.databases.ms-access
|
|||
|
|||
importing Data from access
Hi Darren
glad you're getting somewhere with it .. .you don't need to use all the parameters of the offset function - the last two are optional and, in this case, i think, irrelevant for you. =offset(Sheet_Name!$A$1,match(B1,StartDate,0),matc h(C1,RoomNumber,0)) where startdate and roomnumber are the names of the ranges (startdate the rows & roomnumber the columns) where the start dates (B1) & room numbers (C1) are held - so you're asking excel to go to the imported sheet, find the appropriate start date and move across to the relevant room number and return the information at the junction of these two bits of information. hope this helps Cheers JulieD "Darren" wrote in message om... "JulieD" wrote in message ... Hi Darren i've just done something very similar (with heaps of help from this & other ngs) the way i approached it was 1) use a make-table query in access to extract the data that i want to use in excel to one table 2) open excel and use data / get external data / database query to return all the data from the table into one sheet (called imported from access) 3) then linked the fields in the other sheets to the correct fields in this sheet (using a combination of offset / match & lookup functions - depending on how "variable" the data is) 4) wrote code to prompt to refresh the data when the workbook is opened - when the data is refreshed it saves the workbook with a new name & unlinks the query from access this might give you a plan of attack - Cheers JulieD "Darren" wrote in message om... I have been attempting to create a reservation planning form in excel that imports Data from an Access database and inserts that information automaticly into the correct spreed sheet and the correct cells within that worksheet. The Excel Book is made up of 12 worksheets named Jan-Dec. Each worksheet has columns labeled as each day of that month. Column 'A' is reserved for 19 rows named "room1 - room19". The data I am importing from the data base is " start date", "end date" and "room number". I am also importing " surname" which i have intended to use as the data that is actually inserted into the cell which is hopefull pinpointed using the other information. I have had an attempt at using VBA but totally unsuccessful. I am hoping that someone could point me in the right direction. Please Hi Julie Thanks for the info. I have got as far as importing the data to a new worksheet but I am now having problems linking the Data with my original form ( basicly haven,t got a clue as to how this is done). i assume that some of the data imported i.e (start date , end date and room number) will somehow need to go into the offset formula to locate the cell the data needs to go into. Would I be correct to say that the formula should be similar to this: offset (A1, startdate, room number, 1 , total number of days) OFFSET (reference,column,row,hight,width) I can see a little light at the end of the tunnel. Darren |
#7
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,comp.databases.ms-access
|
|||
|
|||
importing Data from access
Dear Darren
I have written a large reservation system myself. If you would be keen to share any ideas then do not hesitate to contact me. Anyway, I am not a specialist for your question, but I would make an excel file and link it to Access, rather than the other way round. Having said that, why would you use Excel at all? Let me know - Nicolaas |
#8
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,comp.databases.ms-access
|
|||
|
|||
importing Data from access
Hi Julie
I have been attempting as suggested to use OFFSET and MATCH functions to a certain degree of success. I have also used the ADDRESS function. These have both located the cell or cells successfully. The OFFSET function just tells me that no value is inserted in the located cell and ADDRESS returns the cell reference. Any Idea how I can force a particular value to the located cell or cells. Thanks Darren |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Is it possible to use Access data w/o importing it? | Links and Linking in Excel | |||
Importing Data from Access | Excel Discussion (Misc queries) | |||
Importing Data from Access | Excel Discussion (Misc queries) | |||
importing data from access | Excel Discussion (Misc queries) | |||
importing data from Access... | Excel Programming |