469,271 Members | 1,787 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,271 developers. It's quick & easy.

Using Access and Excel together

I have a from that searches my database to see how many printer cartridges there are in storage, how many were ordered, and what date they were ordered. What I need help with is whenever a user says they ordered 5 of one cartridge I need that number to go into an Excel spreadsheet, and have the spreadsheet recalculate the total, so I can keep track of how many were used that year. Is this even possible, I just have no clue about how to do it.
Feb 5 '07 #1
14 7994
ADezii
8,800 Expert 8TB
I have a from that searches my database to see how many printer cartridges there are in storage, how many were ordered, and what date they were ordered. What I need help with is whenever a user says they ordered 5 of one cartridge I need that number to go into an Excel spreadsheet, and have the spreadsheet recalculate the total, so I can keep track of how many were used that year. Is this even possible, I just have no clue about how to do it.
This will involve a process called OLE Automation - I will have an answer for you at my earliest convenience.
Feb 5 '07 #2
Ok thanks, that would be great.

I did find this:

Expand|Select|Wrap|Line Numbers
  1. Dim appxl
  2. Dim Book As Excel.Workbooks
  3. Dim Wsheet As Excel.Worksheet
  4. 'Dim RngWSheet As Excel.Range
  5. FileName = "c:\temp\new.xls"
  6. Set appxl = CreateObject("Excel.Application")
  7. Set Book = appxl.Workbooks
  8. Set Wsheet = Book.Add.Worksheets(1)
  9. 'Set rngWSheet = Wsheet.Cells
  10. appxl.Visible = True
  11.  
  12. Wsheet.Cells(1, 1) = combo.Text/text1.text
  13.  
  14. Wsheet.SaveAs (FileName)
  15. Book.Close
Is that along the lines of something I need. I understand how that works and all, its just every year a new table needs to be created to resemble that year and there are other variables that affect where the data is placed in the spreadsheet.
Feb 5 '07 #3
Rabbit
12,516 Expert Mod 8TB
Does it have to be excel? You could very well just keep it in Access.
Feb 5 '07 #4
Yeah it needs to be Excel. I have gotten it to put the data in the excel sheet and work and everything. But I am still having a couple of problems.


The main problem is I have 12 columns being summed up and put into a different column just through a function on the excel sheet. Is there a way that I can define this function in VB though? I need to be able to define it through VB because whenever the current year changes it makes a new excel sheet corresponding to that year and I don't want the user having to go into the excel sheet and manually having to set this.

Also the other thing is when it finishes putting data in the excel sheet it saves it and closes the excel sheet, but it always does save as and brings up the "are you sure you want to replace box the existing data box". This is not a big problem but it would be nice to be able to just have it auto save and not open that box up.

Thanks all!!
Feb 5 '07 #5
ADezii
8,800 Expert 8TB
I have a from that searches my database to see how many printer cartridges there are in storage, how many were ordered, and what date they were ordered. What I need help with is whenever a user says they ordered 5 of one cartridge I need that number to go into an Excel spreadsheet, and have the spreadsheet recalculate the total, so I can keep track of how many were used that year. Is this even possible, I just have no clue about how to do it.
NOTE: Before you do anything, you must set a Reference to the Microsoft Excel X.X Object Library.

Here is a Code Template that you can easily adapt to your own specific needs. It was kept as generic as possible. The code will:
__1 Open an instance of Microsoft Excel.
__2 Wite values to Cells A1, A2, and A3.
__3 Enter a Formula into Cell A4 which will SUM the values from A1:A3.
__4 Set the Top Border of Cell A4 to thick to demo simple Formatting as well as a summation indicator.
__5 Save the Filename as the previously defined Local Variable strFileName.
__6 Quit Excel and set its Object Reference to Nothing.
__7 P.S. - There was no need to make the Excel Application visible through this process, although we could have.
__8 You must determine what values are to be placed into which Cells and where the Formula(s) are to be entered. Hope all this helps.
__9 The code has been thoroughly tested and is fully operational. Any questions, fell free to ask.
Expand|Select|Wrap|Line Numbers
  1. Dim MyObject As Object      'Hold a Reference to Microsoft Excel
  2. Dim MyWorkbook As Excel.Workbook
  3. Dim MyRange As Excel.Range, strFileName As String
  4.  
  5. strFileName = "C:\OLEAutoTest.xls"
  6.  
  7. On Error Resume Next    'Defer Error Trapping
  8.  
  9. 'Is there a copy of Excel already running? If it isn't, an Error occurrs!
  10. Set MyObject = GetObject(, "Excel.Application")
  11.  
  12. If Err.Number <> 0 Then     'Excel was not running, so run it!
  13.   'Set the Object Variable to reference Excel
  14.   Set MyObject = CreateObject("Excel.Application")
  15. End If
  16. Err.Clear                   'Clear the Error in case a real Error occurs
  17.  
  18. On Error GoTo 0             'Resume Normal Error processing
  19.  
  20. Set MyWorkbook = MyObject.Workbooks.Add
  21.  
  22. '==============================================
  23. With MyObject.ActiveWorkbook.Worksheets(1)      ' OR
  24. 'With MyObject.ActiveWorkbook.ActiveSheet
  25.   .Range("A1") = "20"
  26.   .Range("A2") = "30"
  27.   .Range("A3") = "50"
  28.   .Range("A4").Borders(xlEdgeTop).Weight = xlThick
  29.   .Range("A4").Formula = "=SUM(A1:A3)"
  30. End With
  31. '==============================================
  32.  
  33. MyObject.DisplayAlerts = False
  34. MyWorkbook.SaveAs strFileName
  35.  
  36. MyObject.Quit
  37. Set MyObject = Nothing
Feb 5 '07 #6
Ok thanks that looks like it will solve all my problems, one quick question though.


Expand|Select|Wrap|Line Numbers
  1. MyObject.DisplayAlerts = False
Is that what keeps it from popping up the message saying, "The file already exists, are you sure you want to replace?"
Feb 5 '07 #7
maxamis4
295 Expert 100+
ADezii,

Very nice! You beat me to the punch this is actually the best way
Feb 5 '07 #8
ADezii
8,800 Expert 8TB
ADezii,

Very nice! You beat me to the punch this is actually the best way
Thanks for the compliment, maxamis4.
Feb 6 '07 #9
OK so I tried the code you provided and it is not working for me. It seems like it is always creating a new excel sheet, so whenever it saves the only thing it is saving is the newest value that has been updated. In other words its not opening the existing excel sheet and editing it, it is simply overwriting it. How do I just open the excel sheet to edit, I tried the GetObject method, thinking that would open the existing sheet, but it gives em the error: Active X component can not create object.

Here is kinda of the code I am using

Expand|Select|Wrap|Line Numbers
  1. Dim appxl As Object
  2.     Dim Book As Excel.Workbooks
  3.     Dim Wsheet As Excel.Worksheet
  4.     varCurrentYear = Year(Now)
  5.     FileName = "C:\Documents and Settings\<userName>\My Documents\PrintCart" & Val(varCurrentYear) & ".xls"
  6.  
  7.  
  8.     Set appxl = GetObject(, "Excel.Application")
  9.     Set Book = appxl.Workbooks
  10.     Set Wsheet = Book.Add.Worksheets(1)
  11.     appxl.Visible = False
  12.  
  13.     NumOrdTotal = Wsheet.Cells(Y, X) + Val(varNumOrd)
  14.     Wsheet.Cells(Y, X) = Val(NumOrdTotal)
  15.  
  16.  
  17.     appxl.DisplayAlerts = False
  18.     Wsheet.SaveAs (FileName)
  19.     Book.Close
To me it seems that there is nothing there to Open the Excel sheet. If I have the excel sheet open and run the program then it stops at the
Expand|Select|Wrap|Line Numbers
  1. Wsheet.SaveAs (FileName)
And says Method 'SaveAs' of object '_Worksheet' failed.
Feb 6 '07 #10
OK I just added in
Expand|Select|Wrap|Line Numbers
  1. Workbooks.Open (FileName)
That took care of the first problem.

Now it still is giving me the same error at the save as line. Is there a command just for a regular save and not save as, I believe that will make it work.
Feb 6 '07 #11
ADezii
8,800 Expert 8TB
OK so I tried the code you provided and it is not working for me. It seems like it is always creating a new excel sheet, so whenever it saves the only thing it is saving is the newest value that has been updated. In other words its not opening the existing excel sheet and editing it, it is simply overwriting it. How do I just open the excel sheet to edit, I tried the GetObject method, thinking that would open the existing sheet, but it gives em the error: Active X component can not create object.

Here is kinda of the code I am using

Expand|Select|Wrap|Line Numbers
  1. Dim appxl As Object
  2.     Dim Book As Excel.Workbooks
  3.     Dim Wsheet As Excel.Worksheet
  4.     varCurrentYear = Year(Now)
  5.     FileName = "C:\Documents and Settings\<userName>\My Documents\PrintCart" & Val(varCurrentYear) & ".xls"
  6.  
  7.  
  8.     Set appxl = GetObject(, "Excel.Application")
  9.     Set Book = appxl.Workbooks
  10.     Set Wsheet = Book.Add.Worksheets(1)
  11.     appxl.Visible = False
  12.  
  13.     NumOrdTotal = Wsheet.Cells(Y, X) + Val(varNumOrd)
  14.     Wsheet.Cells(Y, X) = Val(NumOrdTotal)
  15.  
  16.  
  17.     appxl.DisplayAlerts = False
  18.     Wsheet.SaveAs (FileName)
  19.     Book.Close
To me it seems that there is nothing there to Open the Excel sheet. If I have the excel sheet open and run the program then it stops at the
Expand|Select|Wrap|Line Numbers
  1. Wsheet.SaveAs (FileName)
And says Method 'SaveAs' of object '_Worksheet' failed.
In that case:
Expand|Select|Wrap|Line Numbers
  1. Dim appExcel As Excel.Application      'Hold a Reference to Microsoft Excel
  2. Dim wbExcel As Excel.Workbook
  3.  
  4. strFileName = "C:\OLEAutoTest.xls"
  5.  
  6. Set appExcel = CreateObject("Excel.Application")
  7. 'appExcel.Visible = True
  8.  
  9. Set wbExcel = appExcel.Workbooks.Open(strFileName)
  10.  
  11. appExcel.Sheets("Sheet1").Select
  12.  
  13. appExcel.Cells(1, 1) = "1"
  14. appExcel.Cells(2, 1) = "534"
  15. appExcel.Cells(3, 1) = "35"
  16. appExcel.Cells(4, 1).Formula = "=SUM(A1:A3)"
  17. appExcel.Cells(4, 1).Borders(xlEdgeTop).Weight = xlThick
  18.  
  19. appExcel.DisplayAlerts = False
  20. wbExcel.SaveAs strFileName
  21.  
  22. Set wbExcel = Nothing
  23. appExcel.Quit
Feb 7 '07 #12
This is really weird. It will work if the cell in the excel sheet I am changing data in is blank, but if it has data in it, it will not save and gives the same error message as above.

Expand|Select|Wrap|Line Numbers
  1. Dim appxl As Object
  2.     Dim Book As Excel.Workbook
  3.     Dim Wsheet As Excel.Worksheet
  4.     varCurrentYear = Year(Now)
  5.     FileName = "C:\Documents and Settings\c9972442\My Documents\PrintCart" & Val(varCurrentYear) & ".xls"
  6.  
  7.  
  8.     Set appxl = CreateObject("Excel.Application")
  9.     Set Book = appxl.Workbooks.Open(FileName)
  10.     'Set Wsheet = Book.Add.ActiveSheet
  11.     With appxl.ActiveWorkbook.Worksheets(1)
  12.         Set Wsheet = ActiveSheet
  13.         appxl.Visible = False
  14.         Wsheet.Cells(1, 2) = "January"
  15.         Wsheet.Cells(1, 3) = "Feburary"
  16.         Wsheet.Cells(1, 4) = "March"
  17.         Wsheet.Cells(1, 5) = "April"
  18.         Wsheet.Cells(1, 6) = "May"
  19.  
  20.         NumOrdTotal = Wsheet.Cells(Y, X) + Val(varNumOrd)
  21.         Wsheet.Cells(Y, X) = Val(NumOrdTotal)
  22.     End With
  23.  
  24.  
  25.  
  26.     Book.Save
  27.     Set Book = Nothing
  28.     appxl.Quit
Feb 9 '07 #13
little bump
Feb 12 '07 #14
Ok I believe I have narrowed down the problem to EXCEL.EXE not closing. Everytime I run the program for the first time it works then after that it will say, method "save" has failed. Then I can go into task manager > processes and see excel.exe is still running. If I end it then run the program it works fine. I have book.close to close the workbook and appxl.quit, appxl is my excel applicaiton object.
Feb 12 '07 #15

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

38 posts views Thread by Remco Groot Beumer | last post: by
12 posts views Thread by Steve Elliott | last post: by
3 posts views Thread by kenduron | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.