Office 2003 Issue (Access + Excel)

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

whschimmel

Thread Starter
Joined
Jul 7, 2008
Messages
168
Hey Guys,

Weird issue:

We are generating a report in our database system (Access 2003). This file is generated as a XLS file in the users temporary folder...

Once the report button is pressed in Access, one can see that a query is running, afterwards Excel is launched, but does not show the data of excel, see screenshot

In my case the file is saved in C:\Temp. When I browse to this location and manually open the file everything works correct.... :confused:

Anybody Ideas???? :D

Thanks in advance!
Wouter
 

Attachments

OBP

Joined
Mar 8, 2005
Messages
19,895
So the data IS saved,but NOT displayed?
What it the code for opening the Excel fiel that is generated?
 

whschimmel

Thread Starter
Joined
Jul 7, 2008
Messages
168
Yes the Excel (2003) file is saved in the temporary (user) folder.

The file is being saved to C:\Temp so it is saved, Excel tries to display the file, but that goes wrong (see screenshot in my previous post)
Excel launches and I see the menu bars and footer bar (for a lack of a better name :p) But the data fields where normally the data-records are displayed is not showing.... :eek:


This is the code that is used (on-click) event in Access 2003:

Private Sub cmdFR_Click()
'On Error GoTo myErr

Dim OutFile As String





OutFile = Environ("temp") & "\FV Overzicht ~ " & Nz(Me.cboClient.Column(2), "Alle Klanten") & " ~ " & IIf(Me.cboFrom = Me.cboTo, Me.cboFrom.Column(1), Me.cboFrom.Column(1) & " tot " & Me.cboTo.Column(1)) & ".xls"

If isFileLocked(OutFile) Then
MsgBox "File Access Denied! Please close the file - """ & Mid(OutFile, InStr(1, OutFile, "FV Over")) & """", , msgboxTitle
GoTo exitME
Else
If CheckFileExists(OutFile) Then Kill OutFile
End If

DoCmd.SetWarnings False
DoCmd.OpenQuery "qryFRepAangifteICL"
DoCmd.OpenQuery "qryFRepAangifteICLData"
DoCmd.OpenQuery "qryFRepAangifteInkomend"
DoCmd.OpenQuery "qryFRepAangifteInkomendData"
DoCmd.OpenQuery "qryFRepAangifteUitgaand"
DoCmd.OpenQuery "qryFRepAangifteUitgaandData"
DoCmd.SetWarnings True

'FixColumnWidthsOfTable ("Pending Deliveries")
'FixColumnWidthsOfTable ("Delivered")
'If DCount("[cntr_number]", "gas check") > 0 Then
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, "Inkomend", OutFile, True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, "Uitgaand", OutFile, True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, "ICL", OutFile, True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, "Inkomend_Data", OutFile, True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, "Uitgaand_Data", OutFile, True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, "ICL_Data", OutFile, True


RunExcelMagicFR OutFile

FollowHyperlink OutFile





exitME:
Exit Sub


myErr:
MsgBox "Doschedules() " & err.Number

End Sub
 

OBP

Joined
Mar 8, 2005
Messages
19,895
What is "RunExcelMagicFR OutFile"?
and is the "FollowHyperlink OutFile" the code to open the Excel file?
 

whschimmel

Thread Starter
Joined
Jul 7, 2008
Messages
168
This is the "myErr" function:
myErr:
MsgBox "Doschedules() " & err.Number

End Sub
This is "RunExcelMagicFR Outfile":
Private Function RunExcelMagicFR(ByVal sFileName As String)


'''tidy up with excel

Dim xlApp As Excel.Application
Dim xlWBk As Excel.Workbook
Dim xlWks As Excel.Worksheet
Dim xlRange As Excel.Range
Dim xlRange2 As Excel.Range
Dim xlRange3 As Excel.Range
Dim PresentFuture As Integer


Set xlApp = New Excel.Application
'Alternative to use Excel Application that is already open
'Set xlApp = GetObject(, "Excel.Application")

Set xlWBk = xlApp.Workbooks.Open(sFileName)
Set xlWks = xlWBk.Sheets(1)

xlApp.ActiveWindow.WindowState = xlMaximized
xlApp.ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
xlApp.ActiveWindow.TabRatio = 0.6




''''''''''''''''''''''''''
'change colours on first page for past and future
'''''''
''''''''first colour all data on the first page.
'''''''Set xlRange3 = xlWks.Range("a1")
'''''''Set xlRange3 = xlWks.Range(xlRange3, xlRange3.End(xlToRight))
'''''''Set xlRange3 = xlWks.Range(xlRange3, xlRange3.End(xlDown))
'''''''
'''''''xlRange3.Interior.ColorIndex = 40


'''next colour top to present
''PresentFuture = DCount("[cargo_id]", "qrySchedClientPendingCount") + 1
''Set xlRange3 = xlWks.Range("a1")
''Set xlRange3 = xlWks.Range(xlRange3, xlRange3.End(xlToRight))
''Set xlRange3 = xlWks.Range(xlRange3, xlRange3.Rows(PresentFuture))
''
''
''xlRange3.Interior.ColorIndex = 35

'sexy stuff
'''''''''''''''''''''''''


''''''''''loop through each worksheet, set formats on column heds, freeze panes

For Each xlWks In xlWBk.Sheets

'select the first row

Set xlRange = Nothing
Set xlRange = xlWks.Rows(1)
Set xlRange2 = Nothing
Set xlRange2 = xlWks.Range("a1")
Set xlRange3 = xlWks.Range(xlRange2, xlRange2.End(xlToRight))
'.SpecialCells(xlCellTypeLastCell)
'Set xlRange2 = xlRange2.Rows(1)

xlWks.Activate

If Not xlWks.AutoFilterMode Then
xlWks.Range("a1").AutoFilter
End If


'xlRange.AutoFilter


'bold the selection, set the font and fontsize, center all the cells
xlRange.Font.Bold = True
xlRange.Font.Size = 10
xlRange.Font.name = "Verdana"
xlRange.HorizontalAlignment = xlCenter
xlRange.Interior.ColorIndex = xlNone

'stretch all the cells to 30 - this maks the auto work better
xlWks.Cells.EntireColumn.ColumnWidth = 30
'autofit the columns
xlWks.Cells.EntireColumn.AutoFit


xlWks.Range("B2", "B2").Select


xlApp.ActiveWindow.FreezePanes = True
xlWks.Cells.EntireColumn.AutoFit


xlWks.Range("A1", "A1").Select ' place cursor neatly


xlWks.Tab.ColorIndex = xlWks.Index + 35
xlRange3.Interior.ColorIndex = xlWks.Tab.ColorIndex


xlWks.Range("A1", "A1").Select


'''''''''''set PAGE SETUP attributes

With xlWks.PageSetup
' .LeftHeader = ""
' .CenterHeader = "&A"
' .RightHeader = ""
' .LeftFooter = ""
' .CenterFooter = "Page &P"
' .RightFooter = ""
.LeftMargin = xlApp.InchesToPoints(0)
.RightMargin = xlApp.InchesToPoints(0.1)
.TopMargin = xlApp.InchesToPoints(0.3)
.BottomMargin = xlApp.InchesToPoints(0.3)
.HeaderMargin = xlApp.InchesToPoints(0.1)
.FooterMargin = xlApp.InchesToPoints(0.1)
' .PrintHeadings = False
' .PrintGridlines = False
' .PrintComments = xlPrintNoComments
' .PrintQuality = 600
.CenterHorizontally = True
.CenterVertically = False
.Orientation = xlLandscape
' .Draft = False
.PaperSize = xlPaperA4
' .FirstPageNumber = xlAutomatic
' .Order = xlDownThenOver
' .BlackAndWhite = False
.Zoom = False
' .PrintErrors = xlPrintErrorsDisplayed
.FitToPagesWide = 1
.FitToPagesTall = 9999
End With

'''''''''''set PAGE SETUP attributes

Next

''''''''''loop through each worksheet, set formats on column heds, freeze panes



xlWBk.Sheets(1).Select




xlWBk.Save
xlWBk.Close


Set xlRange = Nothing
Set xlWks = Nothing
Set xlWBk = Nothing
xlApp.Quit
Set xlApp = Nothing



End Function
This is the FollowHyperlink Outfile:
This command opens the excel file that was just generated by clicking the Report Button on the Access Form. When I comment out this line ('FollowHyperlink OutFile) the function runs properly, but the file is not being opened. Normal users cannot manually browse to the temp folder to open the report file.
 

OBP

Joined
Mar 8, 2005
Messages
19,895
One problem that you get when Access works with an Excel file using VBA is that it makes the file unavailable to Excel.until Access is closed, sometimes even until the computer is switched off, or a long time period has elapsed.
If you comment out the "RunExcelMagicFR Outfile" does the hyperlink work.
Or does a button with just the hyperlink work?
 

whschimmel

Thread Starter
Joined
Jul 7, 2008
Messages
168
Hey OBP,

Thank you for the quick reply....

We are using many excel reports in access only this report is not working properly...

I commented out the line "RunExcelMagicFR Outfile", but the file still doesn't open :

I did not build the database myself, so that is always difficult when having errors...
 

whschimmel

Thread Starter
Joined
Jul 7, 2008
Messages
168
In my case the file is saved in C:\Temp. When I browse to this location and manually open the file, the data IS showing :confused:
 

OBP

Joined
Mar 8, 2005
Messages
19,895
If you have a seperate button on an Access form using Follow Hyperlink does it open as well?
You can use other methods instead of follow Hyperlink.
 

whschimmel

Thread Starter
Joined
Jul 7, 2008
Messages
168
I tried to let Access open the temp folder as a temporary work around.

but when I want access to open %userprofile%\Local Settings\Temp it goes wrong, when I manually type C:\Documents and Settings\MYUSERNAME\Local Settings\Temp it does work....

can I open it differently? Right now it is set AS STRING, any other ways?
 

OBP

Joined
Mar 8, 2005
Messages
19,895
The Hyperlink seems to be the problem in not being able to work with %userprofile%, although it should work with the Outfile string.
Does a Hyperlink work with the original "Environ("temp") & "\FV Overzicht ~ " & Nz(Me.cboClient.Column(2), "Alle Klanten") & " ~ " & IIf(Me.cboFrom = Me.cboTo, Me.cboFrom.Column(1), Me.cboFrom.Column(1) & " tot " & Me.cboTo.Column(1)) & ".xls""

Which is used to create it.
 

whschimmel

Thread Starter
Joined
Jul 7, 2008
Messages
168
When I do this:

Code:
Shell "explorer.exe Environ("temp") & "\FV Overzicht ~ " & Nz(Me.cboClient.Column(2), "Alle Klanten") & " ~ " & IIf(Me.cboFrom = Me.cboTo, Me.cboFrom.Column(1), Me.cboFrom.Column(1) & " tot " & Me.cboTo.Column(1)) & ".xls""
I get an syntax error
 
Joined
Jan 13, 2009
Messages
38
whschimmel,

I have an Access database at work that outputs to an Excel file (which is populated using a series of SQL statements) and when the output is completed, I get a msgbox that says "Done" and the Excel file is still open with the contents present.

Looking at your VBA and comparing it against the Access file I have, one major difference is that my Access file doesn't close the Excel file after it saves. It does end with the following (not sure if this helps):
Code:
exl.ScreenUpdating = True
ws.Cells(3, 2).Select
DoEvents
ws is the Excel worksheet that was outputed. Hope that helps. I'm still learning VBA so if you still need help, I'll see what I can do.
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Members online

Top