Tech Support Guy banner
Status
Not open for further replies.
1 - 14 of 14 Posts

·
Registered
Joined
·
168 Posts
Discussion Starter · #1 ·
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

·
Registered
Joined
·
168 Posts
Discussion Starter · #3 ·
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
 

·
Registered
Joined
·
168 Posts
Discussion Starter · #5 ·
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.
 

·
Registered
Joined
·
19,896 Posts
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?
 

·
Registered
Joined
·
168 Posts
Discussion Starter · #7 ·
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...
 

·
Registered
Joined
·
19,896 Posts
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.
 

·
Registered
Joined
·
168 Posts
Discussion Starter · #11 ·
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?
 

·
Registered
Joined
·
19,896 Posts
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.
 

·
Registered
Joined
·
168 Posts
Discussion Starter · #13 ·
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
 

·
Registered
Joined
·
38 Posts
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.
 
1 - 14 of 14 Posts
Status
Not open for further replies.
Top