A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Using Forms
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Cannot get code to work for API Save Dialog Box



 
 
Thread Tools Display Modes
  #51  
Old February 23rd, 2006, 03:04 PM posted to microsoft.public.access.forms
external usenet poster
 
Posts: n/a
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Visio Shortcuts [email protected] Visio 1 December 29th, 2006 12:28 AM
Save work automatically Ramon Niese General Discussions 2 November 7th, 2005 05:59 PM
Make Change Case in Excel a format rather than formula Kevin Worksheet Functions 1 March 18th, 2005 09: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


All times are GMT +1. The time now is 04:06 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.