Forum Home
Press F1
Thread ID: 68738	2006-05-09 06:24:00	More VB coding queries	pico (4752)	Press F1

Post ID	Timestamp	Content	User
453428	2006-05-09 06:24:00	Hi all, I am designing a new database! This one is a bit more complicated than the last one . I want to enter some values into the database (for storage) but I want to generate a report from the data . Previously we were using an Excel spreadsheet which has some funky charts in it that will probably be to difficult to reproduce in Access . So I have found out how to open the excel file from within access using the shellexecute api (that was a big step! :cool: ) . I was thinking that I would assign the data to be transferred to public variables within Access and then write code in excel to reference those variables . Excel doesn't however acknowledge the variables . I read in the help that public variables should be available to all running apps as long as the statement Option Private Module isn't used . Do I need to declare the variables as public from within excel too?	pico (4752)
453429	2006-05-09 21:51:00	Ok That probably doesn't make much sense. I have written the code to open the right spreadsheet from Access. I would like to know the syntax for referring to a cell in that spreadsheet from within Access. I think it will be something like: application.workbookname.sheet.cell = blah Anyone know? (This will get around transferring data through variables)	pico (4752)
453430	2006-05-10 00:00:00	This is probably not the best forum, as it's dwelling deep into programming issues. I'd recommend getting a book from Amazon.com. Microsoft Press makes several, with titles like "Microsoft Office Visual Basic Programmer's Guid" though admittedly they stink. If I remember it's the "range" properties I think you're looking for.	kingdragonfly (309)
453431	2006-05-10 02:12:00	You are not going to be able to access variables between applications . The data can be stored in Access then queried from Excel to pull in just the data you need then make charts off that data as necessary . From Excel's menu see . . . Data | Import External Data | New Database Query which you can link to a query in Access and refresh n mins as you please . It's best to actually create the query you need in Access then just refer to that query a the source data . Alternatively you can use code to run a SQL query from Excel . In Access you can link to objects as well such as an Excel spreadsheet . You will need to look up the help for exporting data .	Parry (5696)
453432	2006-05-10 09:26:00	I want to enter some values into the database (for storage) but I want to generate a report from the data. Previously we were using an Excel spreadsheet which has some funky charts in it that will probably be to difficult to reproduce in Access. Following on from Graham, I too recommend creating the queries in Access but are you aware you can create graphs in Access? If you click on Insert > Report > select the chart wizard > that might help you get started. On another note, if you are doing a lot of the work in Excel, do you really need to use a database? Are you likely to have >64k records in any one table? Andrew	andrew93 (249)
453433	2006-05-15 03:23:00	Hi everyone, Thanks for all your posts. I have managed to find a solution to my problem. Your suggestions are probably better ideas but I wanted to know how to do it this way. I will try to explain how to do it for your interest: From Access VBA editor make sure you select the Microsoft Excel Library from the references (Tools - References) otherwise it won't recognise any of the excel objects. Then I created some public variables to contain the excel objects I needed: Public xlApp As Excel.Application Public xlBook As Workbook Public xlSheet As Worksheet Then I wrote a sub to set these variables called OpenReport: Public Sub OpenReport() ' This sets the variables related to the Excel file Set xlBook = GetObject(Application.CurrentProject.Path _ & "\WATER REPORT MODIFIED.xls") Set xlApp = xlBook.Parent Set xlSheet = xlBook.Worksheets("Data") 'This makes the excel application and workbooks visable 'They won't be visible if excel isn't already open. xlApp.Visible = True xlBook.Windows(1).Visible = True Then I created a sub to transfer the data from the open access form an example below: xlSheet.Range("Date").Value = Form_ViewForm.Date And finally some code to close the workbook and set all used variable to nothing: Public Sub CloseReport() On Error Resume Next xlBook.Close savechanges:=False 'No need to specify the filename 'xlApp.Quit Set xlBook = Nothing Set xlSheet = Nothing Set xlApp = Nothing End Sub If you have any questions on this give me a yell and I might be able to help otherwise try www.vbcity.com where I got this help.	pico (4752)
453434	2006-05-15 17:58:00	Sorry Pico, I knew how to do that but I didnt read your post 9/5 properly. You mentioned about using public variables so I thought you were trying to access a public variable in Excel from code in Access rather than wanting to control the application.	Parry (5696)
453435	2006-05-16 23:56:00	That's cool. I just wanted to know how to do that, I know its probably not the best or easiest way to do it. I might take up Andrew93's idea of doing it completely in Excel. It might be better since people are more comfortable with Excel than Access. No doubt I will hear from you again when next I have issues! :)	pico (4752)
1