Opening an Excel file from within Access

  • Thread starter Thread starter Sam
  • Start date Start date
S

Sam

Hi Folks,

Im working my way through access and vba, and am looking for a little
helping hand :)

I have a piece of code that exports data from my Access project to an
Excel file. the catch in my code is the File has to exist alreday. I.e.
i create and save a file, genReport.xls, and my code opens it and
writes data to it when executed.

Suggestions fo rmodifying my code so as to create the Excel file if it
doesnt already exist?

Here is a cut down version of this block of code:

Set mysheet = GetObject(Application.CurrentProject.Path &
txtFileName, "excel.sheet")

Set mysheet = xlApp.workbooks.Open(Application.CurrentProject.Path
&
txtFileName).sheets(1)
....
....
'export code in between
...
...
mysheet.SaveAs Application.CurrentProject.Path & txtFileName
xlApp.Quit
Set mysheet = Nothing

Many thanks,
Sam
 
Sam

Generically, how 'bout using an If-Then statement to check for the
existence, and using the Export command to create if it doesn't exist?
 
Thanks Jeff for the suggestion,

what i ahave done is:

Set xlApp = CreateObject("Excel.Application")
xlApp.workbooks.Add
Set mySheet = xlApp.activeworkbook.sheets(1)
.....
.....
in here is code to populate cells in spreadsheet
.....
.....
mySheet.SaveAs Application.CurrentProject.Path & strTemp &
txtFileName
xlApp.Quit
Set mySheet = Nothing

This will create a new excel file if needed, else will just overwrite
old one. which is fine.
Unfortunately there is an adverse side effect, and i do not understand
how i am producing it.

the excel file i saved, and when i open it i can view the data which
has been euccessfully exported accross. But rather than just one Excel
window open up, i also have two other windows, blank excel files, that
open up.

Any help or pointers on where i went wrong?

Thanks all!
Sam
 
Sam

I'm not familiar with that effect -- I'll step back and see if other 'group
readers might know...
 
Sam wrote in message
Thanks Jeff for the suggestion,

what i ahave done is:

Set xlApp = CreateObject("Excel.Application")
xlApp.workbooks.Add
Set mySheet = xlApp.activeworkbook.sheets(1)
.....
.....
in here is code to populate cells in spreadsheet
.....
.....
mySheet.SaveAs Application.CurrentProject.Path & strTemp &
txtFileName
xlApp.Quit
Set mySheet = Nothing

This will create a new excel file if needed, else will just overwrite
old one. which is fine.
Unfortunately there is an adverse side effect, and i do not understand
how i am producing it.

the excel file i saved, and when i open it i can view the data which
has been euccessfully exported accross. But rather than just one Excel
window open up, i also have two other windows, blank excel files, that
open up.

Any help or pointers on where i went wrong?

Thanks all!
Sam
Click to expand...

OK, I'll have a go ...

I think what happens here, is that your implicit referencing and
instantiation creates an extra instance of Excel, as you describe.

I much prefer something like:

Set xlApp = CreateObject("Excel.Application")
'xlApp.workbooks.Add <- don't use this
set xlWb = xlApp.workbooks.Add
Set mySheet = xlWb.sheets(1)

I've usually used the SaveAs method of the workbook object,
but I don't know if that changes anything.

set mysheet = nothing
xlWb.saveas <the name>
xlWB.close
DoEvents ' perhaps add this too?
set xlWb = nothing
xlApp.Quit
set xlApp = nothing

If this doesn't work, perhaps try being a bit less secretive
about your code;-)
 
*chuckles*

ill give that a try just now, thanks Roy.

and if it doesnt work, i'll come back and throw in the "middle" code
too!

Thanks,
Sam
 
If you use the "TransferSpreadsheet" method to export a query to Excel, the
..xls file will be created if it doesn't exist. The name of the worksheet
becomes the name of the query.
 
Back again,

The issue still persists. follow is most of the code, if your game to
have a browse through and see if you can spot where i am going wrong.
NB: im still picking my way through access and vba, so my coding may be
rough round edges *wink* i take criticism well however, so feel free to
point out anywhere else im going wrong, or if there is a better more
efficient way to code anything i have here.

thanks again, and Enjoy:

Private Sub pbExport_Click()
On Error GoTo ERRORHandler

' ----------------------------------------- '
' Export Here '
' ----------------------------------------- '

Dim mySheet As Object
Dim currentValue As Variant
Dim CompareValue As Variant
Dim xlApp As Object
Dim xlWb As Object
Dim db As DAO.Database
Dim strWhere As String
Dim RsSql As String
Dim Rs As DAO.Recordset
Dim j As Integer
Dim i As Integer
Dim bValidate As Boolean


' validate
Call Validate(bValidate)

If bValidate = True Then

Set xlApp = CreateObject("Excel.Application")
'xlApp.workbooks.Add <- don't use this
Set xlWb = xlApp.workbooks.Add
Set mySheet = xlWb.sheets(1)

' my old code
' Set xlApp = CreateObject("Excel.Application")
' xlApp.workbooks.Add
' Set mySheet = xlApp.activeworkbook.sheets(1)

Set db = DBEngine.Workspaces(0).Databases(0)

Call CreateWhere(strWhere)

RsSql = "SELECT tblSOEResults.* FROM"
RsSql = RsSql & " (tblPerson INNER JOIN tblEqualOpportunities ON
tblPerson.SupportedPersonID=tblEqualOpportunities.SupportedPersonID) "
RsSql = RsSql & " INNER JOIN tblSOEResults ON
tblPerson.SupportedPersonID=tblSOEResults.SupportedPersonID"

RsSql = RsSql & " WHERE"
RsSql = RsSql & strWhere

Set Rs = db.OpenRecordset(RsSql, dbOpenDynaset)

j = 1

For i = 0 To Rs.Fields.count - 1

Call populateHeader(mySheet, currentValue, j, i)

Next i

j = 2

Do Until Rs.EOF
For i = 0 To Rs.Fields.count - 1

If i = 0 Then

currentValue = Rs.Fields(i).Value

If currentValue > 0 Then

currentValue = DLookup("[FirstName] & ' ' &
[MiddleName] & ' ' & [SurName]", "tblPerson", "[SupportedPersonID] = "
& currentValue & "")
mySheet.cells(j, i + 1).Value = currentValue

End If

ElseIf i = 1 Then

currentValue = Rs.Fields(i).Value

If currentValue > 0 Then

currentValue = DLookup("SOECategoryDescription",
"tblCategory", "[SOECategoryCode] = " & currentValue & "")
mySheet.cells(j, i + 1).Value = currentValue

End If

ElseIf i = 2 Then

currentValue = Rs.Fields(i).Value

If currentValue > 0 Then

currentValue = DLookup("SOEOutcomeDescription",
"tblSOEOutcome", "[SOEOutcomeCode] = '" & currentValue & "'")
mySheet.cells(j, i + 1).Value = currentValue

End If

ElseIf i = 3 Then

currentValue = Rs.Fields(i).Value

If currentValue > 0 Then

currentValue = DLookup("Description", "tblLookup",
"[Type] = 'Key' AND
Code: 
 = " & currentValue & "")
mySheet.cells(j, i + 1).Value = currentValue

End If

ElseIf i = 4 Then

currentValue = Rs.Fields(i).Value

If currentValue > 0 Then

currentValue = DLookup(" [tblLookup]![Description] ",
"[tblLookup]", "[Type] ='Key' AND [Code] = " & currentValue & "")
mySheet.cells(j, i + 1).Value = currentValue

End If

ElseIf i = 5 Then

currentValue = Rs.Fields(i).Value

If currentValue > 0 Then

currentValue = DLookup(" [tblLookup]![Description] ",
"[tblLookup]", "[Type] ='Key' AND [Code] = " & currentValue & "")
mySheet.cells(j, i + 1).Value = currentValue

End If

ElseIf i = 6 Then

currentValue = Rs.Fields(i).Value

If currentValue > 0 Then

currentValue = DLookup(" [tblLookup]![Description] ",
"[tblLookup]", "[Type] ='Key' AND [Code] = " & currentValue & "")
mySheet.cells(j, i + 1).Value = currentValue

End If

ElseIf i = 7 Then

currentValue = Rs.Fields(i).Value

If currentValue > 0 Then

currentValue = DLookup(" [tblLookup]![Description] ",
"[tblLookup]", "[Type] ='Key' AND [Code] = " & currentValue & "")
mySheet.cells(j, i + 1).Value = currentValue

End If

End If

Next i
Rs.MoveNext
j = j + 1
Loop

Dim strTemp As String


Set mySheet = Nothing
strTemp = "\"
xlWb.saveas Application.CurrentProject.Path & strTemp & txtFileName
xlWb.Close
DoEvents ' perhaps add this too?
Set xlWb = Nothing
xlApp.Quit
Set xlApp = Nothing

' old code
'    strTemp = "\"
'
'    mySheet.saveas Application.CurrentProject.Path & strTemp &
txtFileName
'    xlApp.Quit
'    Set mySheet = Nothing

End If

ERRORHandler:
Select Case Err.Number
Case 0
'nowt
Case 1004
MsgBox "You have cancelled the export."
Case Else
MsgBox Err.Number & "-" & Err.Description
End Select

End Sub

If you need to see any of the methods that are called, just ask, i can
post them also.

Sam
 
Hi Declan,

thanks for your responce.

i tried that approach early on, but this method does not seem to giv
eme the control i need over what i export and how i export it. the code
above is a work in progress, basically, i want too add to the excel
file more that just the data from the recordset.

Sam
 
My Apoligies!

Roy, your changes seem to have done the trick - although ill be honest
and say im not sure i understand what made the difference. That doesnt
stop me from being appreciative though!

feel free to look through and criticise my coding habits anyways ;)

Thanks again,
Sam
 
Sam wrote in message
My Apoligies!

Roy, your changes seem to have done the trick - although ill be honest
and say im not sure i understand what made the difference. That doesnt
stop me from being appreciative though!

feel free to look through and criticise my coding habits anyways ;)

Thanks again,
Sam
Click to expand...

Maybe this will give some info
 
Sam wrote in message
My Apoligies!

Roy, your changes seem to have done the trick - although ill be honest
and say im not sure i understand what made the difference. That doesnt
stop me from being appreciative though!

feel free to look through and criticise my coding habits anyways ;)

Thanks again,
Sam
Click to expand...

Sorry about the above replies ...

Maybe this will give some info
http://support.microsoft.com/?kbid=244264
I'm not very good at explaining such;-)

I'm happy with commenting on the automation code, which now
is probably close to something I could do myself. For the rest,
I'm not sure, but since you ask - here are some comments.

I would try to fetch all the data through one query, if possible,
and avoid repetitive calls to Domain Aggregates - they might
slow down operations.

I would be carefull to rely upon the ordinal position of fields
in a table (and also recordset, since you're not specifying the
column list in the sql)

Then, lesson to myself - laern the shortcut keys for posting
messages to NG <blush>
 
Roy, you have no idea about the weird and wonderful thing i make my
machine do via shortcuts, and always without even realising i'm doing
it *chuckles*.

Thanks for the reply back as well as the link, is all very appreciated
and welcome.

Kindest Regards,
Sam
 
Back
Top