GetOpenFile Question

[CJ]

Hi Groupies:

I found the GetOpenFile code while looking through some other
threads....thanks for steering me the right way!

I have used it in my code to export a query to excel, but I have one little
hiccup. The code works great to open the Save As dialog box but I want to
also format
the spreadsheet and I am missing how to capture the file name from the
GetOpenFile code.
At the moment, the user enters the filename to save the exported data but
they must enter it again in order to make the formatting part of my code
run.

I'm not sure how much of the code you will need to see so I will just go
with my code unless you need to see more.

Function ExportPayroll()

Dim xlApp As Object

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"qrptPayrollSummary", GetOpenFile()
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True

With xlApp
.workbooks.Open FileName:=GetOpenFile(varFileName) <this is my
trouble line>
.Cells.Select
With .Selection.Font
.Name = "Arial"
.Size = 10
End With

.Range("1:1").Select
With .Selection
.WrapText = False
.Interior.ColorIndex = 15
.Font.Bold = True
End With

.Cells.Select
.Cells.EntireColumn.AutoFit
.Range("A1").Select

End With

End Function

The GetOpenFile code used is found here:
http://www.mvps.org/access/api/api0001.htm

 

[Steve Sanford]

This is untested... but it should work....
(watch for line wrap)

'--------------------------------
Function ExportPayroll()

Dim xlApp As Object

' feel free to change this variable name
Dim Me_Me_Me as string

' file filename to open
Me_Me_Me = GetOpenFile()

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"qrptPayrollSummary", Me_Me_Me
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True

With xlApp
.workbooks.Open FileName:= Me_Me_Me <this is my trouble line>
.Cells.Select
With .Selection.Font
.Name = "Arial"
.Size = 10
End With

.Range("1:1").Select
With .Selection
.WrapText = False
.Interior.ColorIndex = 15
.Font.Bold = True
End With

.Cells.Select
.Cells.EntireColumn.AutoFit
.Range("A1").Select

End With

End Function
'--------------------------------


HTH

 

[CJ]

Fantastic, it does exactly what I want!!!

Give yourself a hand and have a cold beverage, you deserve it!

Thanks a bunch,

CJ