If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#22
|
|||
|
|||
Cannot get code to work for API Save Dialog Box
I do wish you good luck. I am confident you will suceed. You have done
remarkably well with a very complex problem. You coding style is good and your problem solving skills are excellent. I would be proud to have you on my development team. "Elleve" wrote: I got it working to format correctly now. My code was a little bit messed up that was all. As to the otherissue with saving the amount the user edits, I will try out a new approach. First I will create a new table with unique keys and then use that table in my other queries and union query. Hopefully this will allow me to save the information, without messing something up with the other tables. Any more ideas you might think of is of course highly appreciated. Wish me good luck! (I'll need it...) "Klatuu" wrote: hhhmmm, How is it stored in your table? Excel does not usually assume anything is text. If you want explicit data types, you can format the cells during your automation process. You might also try using VBA type conversion functions (this is only an idea, I have not done it before). So, instead of .Cells(86,4) = rst![Dollars] Try .Cells(86,4) = Ccur(rst![Dollars]) That will make it currency. "Elleve" wrote: Klatuu, As to the currency value, it is actually "number stored as text". How can I get this error to disappear? And how to format the rest? Thank you so much for all your help. I have accomplished so much because of you. You're AWESOME!!! "Klatuu" wrote: Sure Excel can calculate currency values. Try it. If it could not, then it would be pretty useless. The problem with not being able to update the extra field has to do with the structure of the query. I don't know all the rules, but some queries are not updateable because, for example, it creates multiple rows based on the values in a table. In this case, you can't update. I am not sure whether it has to do with a query from more than one table or not. Here, I am getting lost with you, but we have made a lot of progress and I compliment you on your coding style. "Elleve" wrote: That's it!!! I had to change the True value to False and it saved the correct spreadsheet to the right place. GREAT!! This is so awesome. Now I just need the formatting to work. My coding has the formatting in it, but it does not seem to work. I realized that when the first amount column shows the value in currency, while excel cannot calculate currency values. Any suggestions to make this work? One step closer... I also need to figure out how allow the user to edit values in the textbox and save the values. The first step is to make the field editable, which they are currently not. Any suggestions here? "Klatuu" wrote: You code looks fine. The only thing I can think of is are you using the dialog box to navigate to the correct folder before you click Save? That would cause it to go to your default directory as you are seeing. One other thing: Set xlBook = xlApp.Workbooks.Open(varGetFileName, 0, True) This line has True in it which, I think, opens the workbook in read only mode. Are you having any trouble writing to the workbook? didn't you once say you aren't seeing the formulas? "Elleve" wrote: While I still need help on that other issue, I fixed the totals problem I had. The code is actually working by giving me the totals I need. But, it puts it in a different location than I asked. The code actually creates two spreadsheets and places them at two different locations with the same name of the sheet: 1. The first spreadsheet placed where I told it to be contains the incorrect worksheet with no totals. 2. The second spreadsheet placed at My Documents contains the correct worksheet with totals. Obviously my coding is a little bit twisted and I need some help in figuring out how to make it save the correct spreadsheet only under the correct place. Here is my code: Private Sub cmdExportSupportSchedule_Click() Dim strFilter As String Dim lngFlags As Long Dim strDefaultDir As String Dim varGetFileName As Variant 'Set filter to show only Excel spreadsheets strFilter = ahtAddFilterItem(strFilter, "Excel Files (*.xls)") 'Hides the Read Only Check Box on the Dialog box lngFlags = ahtOFN_HIDEREADONLY 'Get the File Name To Save strDefaultDir = "c:\" varGetFileName = ahtCommonFileOpenSave( _ OpenFile:=False, _ InitialDir:=strDefaultDir, _ Filter:=strFilter, _ FileName:=strDefaultFileName, _ Flags:=lngFlags, _ DialogTitle:="Save Report") Me.Repaint If varGetFileName "" Then DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qrySupportScheduleUnionqry1and2", varGetFileName, True End If 'Open Excel On Error Resume Next ' Defer error trapping. Set xlApp = GetObject(, "Excel.Application") If Err.Number 0 Then blnExcelWasNotRunning = True Set xlApp = CreateObject("excel.application") Else DetectExcel End If Err.Clear ' Clear Err object in case error occurred. 'On Error GoTo LoadAdjustedActuals_Err DoEvents 'Puts the correct worksheet to My Docs. Should not be removed, but put to diff place Set xlBook = xlApp.Workbooks.Open(varGetFileName, 0, True) xlSheet.Name = "SupportSchedule" xlBook.Worksheets("Support Schedule").Activate 'Activate the sheet for totals xlBook.Worksheets(1).Activate Set xlSheet = xlBook.ActiveSheet xlSheet.Name = "Support Schedule Total" ' Format output With xlSheet For intX = 2 To lngItmCount + 1 strLeftRange = "C" & Trim(Str(intX)) strRightRange = "S" & Trim(Str(intX)) For Each cell In xlSheet.Range(strLeftRange, strRightRange) cell.Font.Size = 10 cell.Font.Name = "Arial" cell.Font.Bold = True cell.NumberFormat = "##,###,##0_);[Red](##,###,##0)" Next Next intX End With 'Formulas With xlSheet .Cells(25, 6).formula = "=sum(F2:F24)" .Cells(25, 7).formula = "=sum(G2:G24)" .Cells(25, 8).formula = "=sum(H2:H24)" .Cells(25, 9).formula = "=sum(I2:I24)" End With 'Done and save xlBook.Save xlBook.Close If blnExcelWasNotRunning = True Then xlApp.Quit Else xlApp.DisplayAlerts = True xlApp.Interactive = True xlApp.ScreenUpdating = True End If Set xlSheet = Nothing Set xlBook = Nothing Set xlApp = Nothing End Sub "Klatuu" wrote: If the record source for your form is a table, you will need to add the field to the table and put a control on your form bound to the field. If it is a query, you will have to add the field to your query. If the field will not be stored in a table, but only used in the query, you can add a field to a query that does not exist in any table by giving it a name and an intial value in your query. For example, if your EndingBalance is based on the sum of field one and field two, then in your query builder where you would put a table field, BalanceEnding: [fieldone] + [fieldtwo] If you want to create a field that has no initial value, you ca do the same thing with whatever value you need to match the data that will be entered. To start with zero: SomeField: 0 To start with Null SomeField: Null To start with an empty string: SomeField: "" Now, to get it to the Spreadsheet is another matter. Since the query will rerun when you export it, whether you use the CopyFromRecordset or TransferSpreadsheet, the values entered will be lost unless saved in a table. You could create a table that is the image you want to send to Excel. Then for your form you would need to use a query as the recordset that would include those fields. The you could use the table as the record source for your export. Have I managed to thoroughly confuse you yet? "Elleve" wrote: Here is some information I believe you might find useful. There are total four fields to be shown in my form. 1. Field one called 'Beginning Balance' (created in qry, based on total) 2. Field two called 'Activity' (from table) 3. Field three called 'Ending Balance' (calculated sum of Field one and Field two) 4. Field four called 'Approved' (created field where user can edit information) The information used for this comes from the same table under one field 'Activity'. The table contains entries for the entire year and I select the months I want in my queries. However, the amount calculated under 'Beginning Balance' is based on a running sum of prior months of 'activity'. This works perfect. Then I have a union query that is based on two other queries. Not sure if this affects anything, but I thought it might be helpful. |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Visio Shortcuts | [email protected] | Visio | 1 | December 28th, 2006 11:28 PM |
Save work automatically | Ramon Niese | General Discussions | 2 | November 7th, 2005 04:59 PM |
Make Change Case in Excel a format rather than formula | Kevin | Worksheet Functions | 1 | March 18th, 2005 08:53 PM |
Open File and Save As don't work | David Evans | Powerpoint | 8 | June 4th, 2004 04:25 PM |
Two versions again-language issue | Otto | Setup, Installing & Configuration | 3 | May 28th, 2004 04:57 AM |