importing Data from access #2

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
0
6/17/2004 12:32:20 PM
excel.misc 78881 articles. 5 followers. Follow

7 Replies
542 Views

Similar Articles

[PageSpeed] 28

are you working on an hotel reservation system?


0
6/17/2004 12:33:53 PM
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" <darren.faulkner@uk.easynet.net> wrote in message
news:670c7e48.0406170432.7db8d437@posting.google.com...
> 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


0
JulieD1 (2295)
6/17/2004 12:38:00 PM
"WindAndWaves" <access@ngaru.com> wrote in message news:<PdgAc.580$LT3.24942@news.xtra.co.nz>...
> are you working on an hotel reservation system?

Thats right. Any Ideas
0
6/21/2004 2:45:56 PM
"JulieD" <JulieD@hctsReMoVeThIs.net.au> wrote in message news:<ukvpXgGVEHA.3592@TK2MSFTNGP10.phx.gbl>...
> 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" <darren.faulkner@uk.easynet.net> wrote in message
> news:670c7e48.0406170432.7db8d437@posting.google.com...
> > 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
0
6/21/2004 3:04:40 PM
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),match(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" <darren.faulkner@uk.easynet.net> wrote in message
news:670c7e48.0406210704.32838ea7@posting.google.com...
> "JulieD" <JulieD@hctsReMoVeThIs.net.au> wrote in message
news:<ukvpXgGVEHA.3592@TK2MSFTNGP10.phx.gbl>...
> > 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" <darren.faulkner@uk.easynet.net> wrote in message
> > news:670c7e48.0406170432.7db8d437@posting.google.com...
> > > 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


0
JulieD1 (2295)
6/21/2004 3:12:24 PM
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


0
6/22/2004 5:10:05 AM
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
0
6/28/2004 2:55:48 PM
Reply:

Similar Artilces:

I am Facing a problem #2
Hi, I am facing a problem. I am developing a dialog based application. I loaded microsoft web browser Control in the dialog I have a javascript file and loades inside the browser. The script file has a command button. Problem!!!!! When the command button is pressed, I want a message box to be displyed from the dialog class not from the script. Please suggest!!!!!!!!!!!! Thanks Sumanta <rout.sumanta@gmail.com> wrote in message news:1172818928.375138.26740@30g2000cwc.googlegroups.com... > Hi, > I am facing a problem. > > I am developing a dialog based application. > ...

Format Cells Date (or any change) not working on imported data
Hello, I've just spent ages researching this and not come up with what I need to be able to do. I have a worksheet for some simple data that has been imported, a date, text and number column (as they display graphically to the end user). All are a "general" format when using Format > Cells. The issue I have is that the date information is in an American date format and I would like to change them into a UK date format. Format > Cells and selecting any option (including custom and special) makes no changes to the imported data. I have seen the work arounds whereby you sp...

Outlook Mobile Access Problem
Hi I've a big problem in Outlook Mobile Access. I've a server exchange standalone withoput a front-end=20 one... I've configured the OWA web form authentication and SSL. For OWA it's all right...everything is going right... The problem is OMA When I try to log on on oma I find the following error=20 message :"Unable to connect to your mailbox" To solve my problem I've found in microsoft web site the=20 following link Microsoft Knowledge Base - 817379 Cannot Access Exchange Server 2003 by Using Outlook Mobile=20 Access When the Exchange Virtual Directory Require...

Automatic Calculation #2
Why is automatic calculation keeps running even though there is no modification done in my spreadsheet? Thanks, Lucie Ch�nier What makes you think that excel is recalculating? If it's because you see Calculate in the statusbar, it could be... http://support.microsoft.com/default.aspx?scid=243495 XL: Calculate Message Remains in Status Bar If 65,536 Formula References "Lucie Ch�nier" wrote: > > Why is automatic calculation keeps running even though there is no > modification done in my spreadsheet? > > Thanks, > > Lucie Ch�nier -- Dave Peterso...

Quirky recent file list #2
Debra--- I do tend to open from Windows Explore more then File>Open. But I am saving the file. It's more that it is annoying when I close a file by accident and don't quite remember where it got saved on the server. Stacie -- SPenney ------------------------------------------------------------------------ SPenney's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1079 View this thread: http://www.excelforum.com/showthread.php?threadid=267592 Well, I guess you weren't the only one who wanted this behaviour changed. <g> If/when you upgr...

Importing Data into an Excel Pivot Table via Access
I have set up a query in Microsoft Access which is linked to our AS400 server. I have created pararmeters within Access which asks for certain fields which works. I then go into Excel and create a pivot table with the external data source that I have created in access. When I go to enter a pararmeter within Microsof Query I get a reply saying that "Parameters can not be used with this Query", what I want to do is setup a parameter on the Excel spreadsheet which then goes and gets the data i require from this parameter. I would be very grateful if someone could help me with thi...

Office:mac v.X #2
Version: v.X Operating System: Mac OS X 10.5 (Leopard) Processor: intel I have subject version of Office. I know that there must be updates to this so I can bring this up to date but I can seem to find the ones I need to start the process. Can someone get me to the beginning? Thanks!!! You can get the Office X 10.1.9 update here: http://snipurl.com/22zx4 On 3/30/08 3:09 PM, in article ee96b4c.-1@webcrossing.caR9absDaxw, "AWOLGolfer@officeformac.com" <AWOLGolfer@officeformac.com> wrote: > Version: v.X > Operating System: Mac OS X 10.5 (Leopard) > Processor: intel &g...

EXCEL TROUBLESHOOTING #2
I have an excel file (2000 format), that after I made a number of changes is causing me problems when I re-open the file. Windows task manager goes to 100% CPU activity, and i cant do anything within the excel file. However, if I set recalculation to manual before I open the file, all seems fine. Obvioulsy I have a problem. But how do i find that problem ? Thanks in advance. I have had some experience running large spreadsheets lately. Above a certain size, the recalculation time seems to climb very fast. While Excel is recalculating, you can't do anything anyway. Best in my v...

Exchange 2007 Evaluation version #2
Can I install it on 64 bit platform and run it as production? On Fri, 23 Feb 2007 09:55:13 -0800, RG <RG@discussions.microsoft.com> wrote: >Can I install it on 64 bit platform and run it as production? There isn't an evaluation version as such. You can install Exchange 2007 without a key for 120 days. ...

graphing data
What is necessary to graph the number(s) in cell(s) when the number in that cell(s) is/are generated from a formula in those cells? -- mikaman Graph them in exactly the same way that would have done if the numbers had been typed into the cells. -- David Biddulph "mikaman" <mikaman@discussions.microsoft.com> wrote in message news:7A6F240E-44F3-4636-8F2F-6DE39722D0EE@microsoft.com... > What is necessary to graph the number(s) in cell(s) when the number in > that > cell(s) is/are generated from a formula in those cells? > -- > mikaman ...

Copy filtered data
Let's say I have data in A1:Gxx. Now I use Autofilter to find all rows which has a "2" in column C. Let's say it leaves rows 1:4 and 8:10. Now I want to copy the filtered data in columns F:G and paste the values (not to an empty range which is easy) but to the same cells in colums D:E. Any help? Hans Knudsen Try Advanced Filter, excellent tutorial here from Debra Dalgeish, owner of the site, http://www.contextures.com/xladvfilter01.html#ApplyAF Regards, Alan. "Hans Knudsen" <Hans.Knudsen@mail.tele.dk> wrote in message news:%23xtVdlR8FHA.740@TK2MSFTNG...

Installing office 2000 disk 2 with publisher on upgraded windows x
I can not get disk 2 to install. It preloads then as files start to copy, it stalls then ends with error message. this is office2000 professional 2 cd set. everything else works exept publisher and its components. Please advise Thanks What is the error message? -- JoAnn Paules MVP Microsoft [Publisher] "cugolfn" <cugolfn@discussions.microsoft.com> wrote in message news:F57B8716-65FA-4619-93A4-AA24D44ABC33@microsoft.com... >I can not get disk 2 to install. It preloads then as files start to copy, >it stalls then ends with error message. this is office2000 profess...

Import Palm files
Can anyone help me by telling me how to import palm files (dat files) into outlook. I want to import contact, calendar, etc Depends what you can export to from Palm Desktop. Personally, I've found this messy. Why not just get sync software that can sync your Palm with Outlook directly? Chapura Pocketmirror or Pumatech Intellisync are two ideas....or your Palm CD may have come with this option. F. Carvalho wrote: > Can anyone help me by telling me how to import palm files > (dat files) into outlook. I want to import contact, > calendar, etc ...

copying data
Getting errors when copying data from workbook to workbook. Shutdown and restart allows without errors. ...

Outlook 2003 Contact data
Just starting using Outlook 2003. When composing an email, when go to address the note, it appears that there are 2 Contact files - 1 is "blank" and the other has lots of my contacts. It is the latter contact file that I enabled to use as my address book. Initially address book comes up the first one and I get error message. Then I toggle to the second one and all the names are accessible. How do I get rid of the one that appears blank, so only one contact file appears when I go to address an email? Les <lester123_nospam@comcast.net> wrote: > When composing an e...

how to divide a numbers in a table of data by 1,000, all at once
I've noticed in some spreadsheets that numbers are displayed as rounded...i.e. so that one isn't looking at 1,403,333 + 2,333,999 etc. but rather 1403 + 2334, etc. with a note at the top that says "000's". In one step, how can I edit all the numbers in a table so that they show to the nearest 10,000 (i.e. 1403 +2334), without dividing them one by one by 1,000? Or, to go at it from another angle, how do I divide each # by 1,000? The ROUND feature requires that I create a new table. Not exactly what I'm looking for. Enter 1000 in an unused cell. ...

Chart
From: <c@c.c> Subject: Chart - Title Widths Date: Tuesday, February 03, 2004 12:43 PM Chart - Title Widths Does anyone know how to find the width of a title/axis title? ...

Upgrading from Exchange 2000 Standard to Enterprise Cal ? #2
Hello Everyone, I know the upgrade process from exchange 2000 standard to enterprise seems pretty straight forward. However, I have a cal question. Are my standard cals still good with this upgrade. Say I have right now standard 2000 with 30 cals. If I upgrade to Exchange 2000 Enterprise with 25 cals to pass the 16mb limitation. Would I then only have the 25 cals or 55 total cals? Thanks ...

Contact not importing names of csv lists
Whenever I import a csv file into outlook 2007 it imports only the email addresses, nothing else. I've titled the csv columns with the exact same headings as the outlook contact headings. Still, it imports only the email addresses. What do I need to do to get it to import the full names as well? Is it correct to assume that because you stated that you "titled the csv columns the same as the Outlook contact headings" - that you used "auto-map" to import your data? There is absolutely no reason, benefit or value whatsoever (under any circumstances) in ...

Question Regarding Excel 2007 Formatting Corruption #2
Hi Folks, I am having a problem with Excel 2007 files losing all formatting (merged cells, colors, borders, and data formatting (99% turns into 0.99)) when I open a file on our office server make edits and then save the new file on the server. Each sheet usually has a mix of locked and unlocked cells and I unprotect the sheet to make edits. Also, something is fundamentally changed with the file as its size doubles or triples. If I reopen the corrupted file and redo any of the formatting and try to save it none of the new formatting is retained either. Has anyone else ever experienced a pr...

How to obtain the name of ODBC definition used in a spreadsheet? #2
How do I get the name of the ODBC def used in my spreadsheet? How do I migrate my spreadsheet (with the ODBC connection) to a different computer and change the name of the ODBC def to a new name, available on the new computer? best regards evds@tekton-it.be ...

Re: Counting Occurrences with 2 columns
Suppose your data is in Sheet1 in A2:C6 > Red 4 Read > Blue 5 Not Read > Green 6 Read > Yellow 2 Read > Red 3 Not Read Try this set-up in a new sheet, say Sheet2 ------------- List down in A2:A5 the names: Red, Blue, Green, Yellow Put across in B2:C2 the phrases: Read, Not Read Put in B2: =SUMPRODUCT((Sheet1!$A$2:$A$6=$A2)*(Sheet1!$C$2:$C$6=B$1)*(Sheet1!$B$2:$B$6) ) Copy B2 across to C2, then copy down to C5 [Note that the ranges used in SUMPRODUCT have to be similar and you can't use entire columns e.g.: A:A, B:B, C:C]...

IStorage/IStream #2
Hello, I am working on an application that has more than a dozen of data files. When the application closes, it uses IStorage/IStream to move them into one file (IStorage), from the user's point of view. When the application is started next time, these data files are extracted from the one file. By extraction, I mean to programmatically read from IStreams and then write to new, separate data files. The reading and writing are done chunks by chunks. The whole process works fast only if all the data files are small. The main issue to me is not disk memory space, but the speed. ...

Mailbox Cleanup Agent #2
Hi. We need to delete a few mailboxes on our Exchange 2003 Server. We've deleted the AD acounts but when we go into system nanager and try to choose purge, it's greyed out. Our retention period for deleted mailboxes is 30 days. Can we run the cleanup agent now on the live system to speed up the deletion of the mailboxes? Or is it not recommended? Thanks Matt On 3 May 2006 06:37:15 -0700, "Jim C" <matt_hather@o2.co.uk> wrote: >Hi. > >We need to delete a few mailboxes on our Exchange 2003 Server. > >We've deleted the AD acounts but when we g...

Multipage newsletter in the body of an email
I knew that Don had a website where I could find his organization's newsletter. I just sent him the results of that process I posted about the other day. I'm anxiously awaiting his comments. (Just because I can send it to myself and it looks fine doesn't mean it will to others.) :-) -- JoAnn Paules MVP Microsoft [Publisher] ...