Late Binding Outlook Problem

T

tmort

I have some code that I am trying to switch to late binding. It is code that
saves the result of a query as an Excel file, then sends this file as an
attaqchment to an Outlook email then deletes the file.

It used to work before I made both changes to the the code dealing with
Excel and Outlook application/object.

Now I get an error at the line:

DoCmd.OutputTo acOutputQuery, "process export qry", acFormatXLS,
CurrentProject.Path & "\" & pFilename, 0

saying that it cannot save the output file to the file name specified.

As I mentioned this used to work before I made changes. It does compile
though.

Any help will be appreciated.

The code is:


Function compexport()

Dim stto As String
Dim stcc As String
Dim stsubject As String
Dim ststartDate As String
Dim stenddate As String
Dim stfrmt As String
Dim stconame As String
Dim stmessage As String
Dim stnoto As String
Dim stnodate As String
Dim stnoconame As String
Dim stpermnumber As String
Dim ststartdateatt As String
Dim stenddateatt As String
Dim mPathAndFile As String, mFileNumber As Integer
Dim R As Recordset, mFieldNum As Integer
Dim mOutputString As String
Dim booDelimitFields As Boolean
Dim booIncludeFieldnames As Boolean
Dim mFieldDeli As String
Dim pbooIncludeFieldnames As String


Dim outApp As Object, objOutlook As Object, outmsg As Object, olmailitem As
Object


Dim oexcel As Object
'Dim osheet As Worksheet
Dim osheet As Object
'Dim rngToFormat As Range
Dim rngToFormat As Object



stconame = Nz([Forms]![export form]![coname], "none")
ststartDate = Nz([Forms]![export form]![begin], "none")
ststartdateatt = Replace(ststartDate, "/", "-")
stenddate = Nz([Forms]![export form]![end], "none")
stenddateatt = Replace(stenddate, "/", "-")
stpermnumber = Nz([Forms]![export form]![cmbpermnumber], "none")
stfrmt = DLookup("[Comp_format]", "export format settings")
stsubject = stconame & " " & "Compliance Sampling Data" & " " & ststartDate
& " " & "to" & " " & stenddate
stto = Nz([Forms]![export form]![to], "none")
stcc = Nz([Forms]![export form]![cc], "")
stmessage = Nz([Forms]![export form]![Message], "")
stnoto = "You forgot to enter a Send To email address"
stnodate = "You must enter a beginning and ending date for the data you wish
to export"
stnoconame = "You forgot to enter a company name"

pbooIncludeFieldnames = "true"

If stto = "none" Then

MsgBox stnoto

Exit Function

Else

If stconame = "none" Then

MsgBox stnoconame

Exit Function

Else

If ststartDate = "none" Then

MsgBox stnodate

Exit Function

Else

If stendate = "none" Then

MsgBox stnodate

Exit Function


Else

If stfrmt = "acFormatXLS" Then

'DoCmd.SendObject acSendQuery, "compliance export qry", acFormatXLS, [stto],
[stcc], , stconame & " " & "Compliance Sampling Data" & " " & ststartDate & "
" & "to" & " " & stenddate, [stmessage], False



pFilename = stconame & " P" & stpermnumber & " " & ststartdateatt & " to " &
stenddateatt & " Compliance Data.xls"



DoCmd.OutputTo acOutputQuery, "compliance export qry", acFormatXLS,
CurrentProject.Path & "\" & pFilename, 0
'DoCmd.TransferSpreadsheet acExport, , "compliance export qry",
CurrentProject.Path & "\" & pFilename, True
'DoCmd.TransferSpreadsheet acImport, 3,"Employees","C:\Lotus\Newemps.wk3",
True, "A1:G12"

mPathAndFile = CurrentProject.Path & "\" & pFilename





'*************************


'Set oapp = CreateObject("Excel.Application")
Set oapp = CreateObject("Excel.Application")
Set oexcel = oapp.Workbooks.Open(Filename:=mPathAndFile)
Set osheet = oexcel.Worksheets("compliance export qry")

oapp.Visible = False
oapp.DisplayAlerts = False
osheet.Activate

With oexcel.Worksheets("compliance export qry").Columns

..Columns("A:S").AutoFit

End With


With oexcel.Worksheets("compliance export qry").PageSetup
..Zoom = False
..FitToPagesTall = 1000
..FitToPagesWide = 1
..Orientation = 2
..PrintGridlines = 0
..PrintTitleRows = "A1:S1"
'.LeftHeader =
..CenterHeader = "&14" & pFilename & "&10"
'.RightHeader =
..LeftFooter = "Report Created &D &T"
'.CenterFooter =
..RightFooter = "Page &P of &N"


..LeftMargin = oapp.InchesToPoints(0.25)
..RightMargin = oapp.InchesToPoints(0.25)
..TopMargin = oapp.InchesToPoints(0.75)
..BottomMargin = oapp.InchesToPoints(0.5)
..HeaderMargin = oapp.InchesToPoints(0.5)
..FooterMargin = oapp.InchesToPoints(0.25)

End With


With osheet.Range("A1:S1")
Set rngToFormat = osheet.Range(oexcel.Worksheets("compliance export
qry").Range("S1"), .Cells(osheet.Rows.Count, "C").end(xlUp).Offset(0, -2)) '

End With

With rngToFormat.Cells.Select



'With borders


oapp.Selection.Interior.ColorIndex = 2
oapp.Selection.Interior.Pattern = 1

oapp.Selection.Borders(xlDiagonalDown.LineStyle) = -4142
oapp.Selection.Borders(xlDiagonalUp).LineStyle = -4142
With oapp.Selection.Borders(xlEdgeLeft)
..LineStyle = 1
..Weight = 2
..ColorIndex = -4105
End With
With oapp.Selection.Borders(xlEdgeTop)
..LineStyle = 1
..Weight = 2
..ColorIndex = -4105
End With
With oapp.Selection.Borders(xlEdgeBottom)
..LineStyle = 1
..Weight = 2
..ColorIndex = -4105
End With
With oapp.Selection.Borders(xlEdgeRight)
..LineStyle = 1
..Weight = 2
..ColorIndex = -4105
End With
With oapp.Selection.Borders(xlInsideVertical)
..LineStyle = 1
..Weight = 2
..ColorIndex = -4105
End With
With oapp.Selection.Borders(xlInsideHorizontal)
..LineStyle = 1
..Weight = 2
..ColorIndex = -4105
End With

End With



With osheet.Range("A1:S1")
..Font.ColorIndex = 1
..Font.Bold = -1
..Interior.ColorIndex = 15
..Interior.Pattern = 1
End With




Set osheet = Nothing 'disconnect from the Worksheet
oexcel.Close SaveChanges:=True 'Save (and disconnect from) the Workbook



Set oexcel = Nothing
oapp.Quit 'Close (and disconnect from) Excel
Set oapp = Nothing



'*******************************************

Set outApp = CreateObject("Outlook.Application")
Set outmsg = outApp.CreateItem(olmailitem)



If stcc = "" Then

With outmsg

..Recipients.Add (stto)
..subject = stsubject
..ReadReceiptRequested = -1
..body = stmessage
..Importance = olImportanceHigh
..Attachments.Add (mPathAndFile)
..Send

End With

Else

With outmsg

..Recipients.Add(stto).Type = 1
..Recipients.Add(stcc).Type = 2
..subject = stsubject
..ReadReceiptRequested = -1
..body = stmessage
..Importance = olImportanceHigh
..Attachments.Add (mPathAndFile)
..Send

End With

End If

Kill mPathAndFile
 
J

Jeanette Cunningham

Hi,
what happens if you do a test with pFilename by hardcoding the filename?
what happens if you change the filename to a different name? different
folder? different path?
what was the error number and what was the description?

Jeanette Cunningham


tmort said:
I have some code that I am trying to switch to late binding. It is code
that
saves the result of a query as an Excel file, then sends this file as an
attaqchment to an Outlook email then deletes the file.

It used to work before I made both changes to the the code dealing with
Excel and Outlook application/object.

Now I get an error at the line:

DoCmd.OutputTo acOutputQuery, "process export qry", acFormatXLS,
CurrentProject.Path & "\" & pFilename, 0

saying that it cannot save the output file to the file name specified.

As I mentioned this used to work before I made changes. It does compile
though.

Any help will be appreciated.

The code is:


Function compexport()

Dim stto As String
Dim stcc As String
Dim stsubject As String
Dim ststartDate As String
Dim stenddate As String
Dim stfrmt As String
Dim stconame As String
Dim stmessage As String
Dim stnoto As String
Dim stnodate As String
Dim stnoconame As String
Dim stpermnumber As String
Dim ststartdateatt As String
Dim stenddateatt As String
Dim mPathAndFile As String, mFileNumber As Integer
Dim R As Recordset, mFieldNum As Integer
Dim mOutputString As String
Dim booDelimitFields As Boolean
Dim booIncludeFieldnames As Boolean
Dim mFieldDeli As String
Dim pbooIncludeFieldnames As String


Dim outApp As Object, objOutlook As Object, outmsg As Object, olmailitem
As
Object


Dim oexcel As Object
'Dim osheet As Worksheet
Dim osheet As Object
'Dim rngToFormat As Range
Dim rngToFormat As Object



stconame = Nz([Forms]![export form]![coname], "none")
ststartDate = Nz([Forms]![export form]![begin], "none")
ststartdateatt = Replace(ststartDate, "/", "-")
stenddate = Nz([Forms]![export form]![end], "none")
stenddateatt = Replace(stenddate, "/", "-")
stpermnumber = Nz([Forms]![export form]![cmbpermnumber], "none")
stfrmt = DLookup("[Comp_format]", "export format settings")
stsubject = stconame & " " & "Compliance Sampling Data" & " " &
ststartDate
& " " & "to" & " " & stenddate
stto = Nz([Forms]![export form]![to], "none")
stcc = Nz([Forms]![export form]![cc], "")
stmessage = Nz([Forms]![export form]![Message], "")
stnoto = "You forgot to enter a Send To email address"
stnodate = "You must enter a beginning and ending date for the data you
wish
to export"
stnoconame = "You forgot to enter a company name"

pbooIncludeFieldnames = "true"

If stto = "none" Then

MsgBox stnoto

Exit Function

Else

If stconame = "none" Then

MsgBox stnoconame

Exit Function

Else

If ststartDate = "none" Then

MsgBox stnodate

Exit Function

Else

If stendate = "none" Then

MsgBox stnodate

Exit Function


Else

If stfrmt = "acFormatXLS" Then

'DoCmd.SendObject acSendQuery, "compliance export qry", acFormatXLS,
[stto],
[stcc], , stconame & " " & "Compliance Sampling Data" & " " & ststartDate
& "
" & "to" & " " & stenddate, [stmessage], False



pFilename = stconame & " P" & stpermnumber & " " & ststartdateatt & " to "
&
stenddateatt & " Compliance Data.xls"



DoCmd.OutputTo acOutputQuery, "compliance export qry", acFormatXLS,
CurrentProject.Path & "\" & pFilename, 0
'DoCmd.TransferSpreadsheet acExport, , "compliance export qry",
CurrentProject.Path & "\" & pFilename, True
'DoCmd.TransferSpreadsheet acImport, 3,"Employees","C:\Lotus\Newemps.wk3",
True, "A1:G12"

mPathAndFile = CurrentProject.Path & "\" & pFilename





'*************************


'Set oapp = CreateObject("Excel.Application")
Set oapp = CreateObject("Excel.Application")
Set oexcel = oapp.Workbooks.Open(Filename:=mPathAndFile)
Set osheet = oexcel.Worksheets("compliance export qry")

oapp.Visible = False
oapp.DisplayAlerts = False
osheet.Activate

With oexcel.Worksheets("compliance export qry").Columns

.Columns("A:S").AutoFit

End With


With oexcel.Worksheets("compliance export qry").PageSetup
.Zoom = False
.FitToPagesTall = 1000
.FitToPagesWide = 1
.Orientation = 2
.PrintGridlines = 0
.PrintTitleRows = "A1:S1"
'.LeftHeader =
.CenterHeader = "&14" & pFilename & "&10"
'.RightHeader =
.LeftFooter = "Report Created &D &T"
'.CenterFooter =
.RightFooter = "Page &P of &N"


.LeftMargin = oapp.InchesToPoints(0.25)
.RightMargin = oapp.InchesToPoints(0.25)
.TopMargin = oapp.InchesToPoints(0.75)
.BottomMargin = oapp.InchesToPoints(0.5)
.HeaderMargin = oapp.InchesToPoints(0.5)
.FooterMargin = oapp.InchesToPoints(0.25)

End With


With osheet.Range("A1:S1")
Set rngToFormat = osheet.Range(oexcel.Worksheets("compliance export
qry").Range("S1"), .Cells(osheet.Rows.Count, "C").end(xlUp).Offset(0, -2))
'

End With

With rngToFormat.Cells.Select



'With borders


oapp.Selection.Interior.ColorIndex = 2
oapp.Selection.Interior.Pattern = 1

oapp.Selection.Borders(xlDiagonalDown.LineStyle) = -4142
oapp.Selection.Borders(xlDiagonalUp).LineStyle = -4142
With oapp.Selection.Borders(xlEdgeLeft)
.LineStyle = 1
.Weight = 2
.ColorIndex = -4105
End With
With oapp.Selection.Borders(xlEdgeTop)
.LineStyle = 1
.Weight = 2
.ColorIndex = -4105
End With
With oapp.Selection.Borders(xlEdgeBottom)
.LineStyle = 1
.Weight = 2
.ColorIndex = -4105
End With
With oapp.Selection.Borders(xlEdgeRight)
.LineStyle = 1
.Weight = 2
.ColorIndex = -4105
End With
With oapp.Selection.Borders(xlInsideVertical)
.LineStyle = 1
.Weight = 2
.ColorIndex = -4105
End With
With oapp.Selection.Borders(xlInsideHorizontal)
.LineStyle = 1
.Weight = 2
.ColorIndex = -4105
End With

End With



With osheet.Range("A1:S1")
.Font.ColorIndex = 1
.Font.Bold = -1
.Interior.ColorIndex = 15
.Interior.Pattern = 1
End With




Set osheet = Nothing 'disconnect from the Worksheet
oexcel.Close SaveChanges:=True 'Save (and disconnect from) the Workbook



Set oexcel = Nothing
oapp.Quit 'Close (and disconnect from) Excel
Set oapp = Nothing



'*******************************************

Set outApp = CreateObject("Outlook.Application")
Set outmsg = outApp.CreateItem(olmailitem)



If stcc = "" Then

With outmsg

.Recipients.Add (stto)
.subject = stsubject
.ReadReceiptRequested = -1
.body = stmessage
.Importance = olImportanceHigh
.Attachments.Add (mPathAndFile)
.Send

End With

Else

With outmsg

.Recipients.Add(stto).Type = 1
.Recipients.Add(stcc).Type = 2
.subject = stsubject
.ReadReceiptRequested = -1
.body = stmessage
.Importance = olImportanceHigh
.Attachments.Add (mPathAndFile)
.Send

End With

End If

Kill mPathAndFile
Click to expand...
 
D

Douglas J. Steele

Don't know for sure that this is relevant, but what file extension are you
using? Access is picky in some cases.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


tmort said:
I have some code that I am trying to switch to late binding. It is code
that
saves the result of a query as an Excel file, then sends this file as an
attaqchment to an Outlook email then deletes the file.

It used to work before I made both changes to the the code dealing with
Excel and Outlook application/object.

Now I get an error at the line:

DoCmd.OutputTo acOutputQuery, "process export qry", acFormatXLS,
CurrentProject.Path & "\" & pFilename, 0

saying that it cannot save the output file to the file name specified.

As I mentioned this used to work before I made changes. It does compile
though.

Any help will be appreciated.

The code is:


Function compexport()

Dim stto As String
Dim stcc As String
Dim stsubject As String
Dim ststartDate As String
Dim stenddate As String
Dim stfrmt As String
Dim stconame As String
Dim stmessage As String
Dim stnoto As String
Dim stnodate As String
Dim stnoconame As String
Dim stpermnumber As String
Dim ststartdateatt As String
Dim stenddateatt As String
Dim mPathAndFile As String, mFileNumber As Integer
Dim R As Recordset, mFieldNum As Integer
Dim mOutputString As String
Dim booDelimitFields As Boolean
Dim booIncludeFieldnames As Boolean
Dim mFieldDeli As String
Dim pbooIncludeFieldnames As String


Dim outApp As Object, objOutlook As Object, outmsg As Object, olmailitem
As
Object


Dim oexcel As Object
'Dim osheet As Worksheet
Dim osheet As Object
'Dim rngToFormat As Range
Dim rngToFormat As Object



stconame = Nz([Forms]![export form]![coname], "none")
ststartDate = Nz([Forms]![export form]![begin], "none")
ststartdateatt = Replace(ststartDate, "/", "-")
stenddate = Nz([Forms]![export form]![end], "none")
stenddateatt = Replace(stenddate, "/", "-")
stpermnumber = Nz([Forms]![export form]![cmbpermnumber], "none")
stfrmt = DLookup("[Comp_format]", "export format settings")
stsubject = stconame & " " & "Compliance Sampling Data" & " " &
ststartDate
& " " & "to" & " " & stenddate
stto = Nz([Forms]![export form]![to], "none")
stcc = Nz([Forms]![export form]![cc], "")
stmessage = Nz([Forms]![export form]![Message], "")
stnoto = "You forgot to enter a Send To email address"
stnodate = "You must enter a beginning and ending date for the data you
wish
to export"
stnoconame = "You forgot to enter a company name"

pbooIncludeFieldnames = "true"

If stto = "none" Then

MsgBox stnoto

Exit Function

Else

If stconame = "none" Then

MsgBox stnoconame

Exit Function

Else

If ststartDate = "none" Then

MsgBox stnodate

Exit Function

Else

If stendate = "none" Then

MsgBox stnodate

Exit Function


Else

If stfrmt = "acFormatXLS" Then

'DoCmd.SendObject acSendQuery, "compliance export qry", acFormatXLS,
[stto],
[stcc], , stconame & " " & "Compliance Sampling Data" & " " & ststartDate
& "
" & "to" & " " & stenddate, [stmessage], False



pFilename = stconame & " P" & stpermnumber & " " & ststartdateatt & " to "
&
stenddateatt & " Compliance Data.xls"



DoCmd.OutputTo acOutputQuery, "compliance export qry", acFormatXLS,
CurrentProject.Path & "\" & pFilename, 0
'DoCmd.TransferSpreadsheet acExport, , "compliance export qry",
CurrentProject.Path & "\" & pFilename, True
'DoCmd.TransferSpreadsheet acImport, 3,"Employees","C:\Lotus\Newemps.wk3",
True, "A1:G12"

mPathAndFile = CurrentProject.Path & "\" & pFilename





'*************************


'Set oapp = CreateObject("Excel.Application")
Set oapp = CreateObject("Excel.Application")
Set oexcel = oapp.Workbooks.Open(Filename:=mPathAndFile)
Set osheet = oexcel.Worksheets("compliance export qry")

oapp.Visible = False
oapp.DisplayAlerts = False
osheet.Activate

With oexcel.Worksheets("compliance export qry").Columns

.Columns("A:S").AutoFit

End With


With oexcel.Worksheets("compliance export qry").PageSetup
.Zoom = False
.FitToPagesTall = 1000
.FitToPagesWide = 1
.Orientation = 2
.PrintGridlines = 0
.PrintTitleRows = "A1:S1"
'.LeftHeader =
.CenterHeader = "&14" & pFilename & "&10"
'.RightHeader =
.LeftFooter = "Report Created &D &T"
'.CenterFooter =
.RightFooter = "Page &P of &N"


.LeftMargin = oapp.InchesToPoints(0.25)
.RightMargin = oapp.InchesToPoints(0.25)
.TopMargin = oapp.InchesToPoints(0.75)
.BottomMargin = oapp.InchesToPoints(0.5)
.HeaderMargin = oapp.InchesToPoints(0.5)
.FooterMargin = oapp.InchesToPoints(0.25)

End With


With osheet.Range("A1:S1")
Set rngToFormat = osheet.Range(oexcel.Worksheets("compliance export
qry").Range("S1"), .Cells(osheet.Rows.Count, "C").end(xlUp).Offset(0, -2))
'

End With

With rngToFormat.Cells.Select



'With borders


oapp.Selection.Interior.ColorIndex = 2
oapp.Selection.Interior.Pattern = 1

oapp.Selection.Borders(xlDiagonalDown.LineStyle) = -4142
oapp.Selection.Borders(xlDiagonalUp).LineStyle = -4142
With oapp.Selection.Borders(xlEdgeLeft)
.LineStyle = 1
.Weight = 2
.ColorIndex = -4105
End With
With oapp.Selection.Borders(xlEdgeTop)
.LineStyle = 1
.Weight = 2
.ColorIndex = -4105
End With
With oapp.Selection.Borders(xlEdgeBottom)
.LineStyle = 1
.Weight = 2
.ColorIndex = -4105
End With
With oapp.Selection.Borders(xlEdgeRight)
.LineStyle = 1
.Weight = 2
.ColorIndex = -4105
End With
With oapp.Selection.Borders(xlInsideVertical)
.LineStyle = 1
.Weight = 2
.ColorIndex = -4105
End With
With oapp.Selection.Borders(xlInsideHorizontal)
.LineStyle = 1
.Weight = 2
.ColorIndex = -4105
End With

End With



With osheet.Range("A1:S1")
.Font.ColorIndex = 1
.Font.Bold = -1
.Interior.ColorIndex = 15
.Interior.Pattern = 1
End With




Set osheet = Nothing 'disconnect from the Worksheet
oexcel.Close SaveChanges:=True 'Save (and disconnect from) the Workbook



Set oexcel = Nothing
oapp.Quit 'Close (and disconnect from) Excel
Set oapp = Nothing



'*******************************************

Set outApp = CreateObject("Outlook.Application")
Set outmsg = outApp.CreateItem(olmailitem)



If stcc = "" Then

With outmsg

.Recipients.Add (stto)
.subject = stsubject
.ReadReceiptRequested = -1
.body = stmessage
.Importance = olImportanceHigh
.Attachments.Add (mPathAndFile)
.Send

End With

Else

With outmsg

.Recipients.Add(stto).Type = 1
.Recipients.Add(stcc).Type = 2
.subject = stsubject
.ReadReceiptRequested = -1
.body = stmessage
.Importance = olImportanceHigh
.Attachments.Add (mPathAndFile)
.Send

End With

End If

Kill mPathAndFile
Click to expand...
 
T

tmort

The file extension I am using is xls.

I think I found that problem, but, now I have two more.

I had some dummy data that put a "." in the file name where it didn't
belong. It was :

ABC Co. P0002 12-20-2007 to 12-21-2007 Compliance Data.xls

instead of

ABC Co. P0002 12-20-2007 to 12-21-2007 Compliance Data.xls

Now it chokes at:

Set rngToFormat = osheet.Range(oexcel.Worksheets("compliance export
qry").Range("S1"), .Cells(osheet.Rows.Count, "C").end(xlUp).Offset(0, -2)) '

The db also has a front end and backend and for some reason I can no longer
open it (without using the shift key). It is set to automatically link the
front end to the backend or for you to point to the backend.

I get a message sying that:

File dbpath does not contain the required backenddb tables.

I haven't done anything to the backend to remove or change any tables.


I know I'm vearing of topic but any ideas?

It still does this even if I manually link the tables


Douglas J. Steele said:
Don't know for sure that this is relevant, but what file extension are you
using? Access is picky in some cases.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


tmort said:
I have some code that I am trying to switch to late binding. It is code
that
saves the result of a query as an Excel file, then sends this file as an
attaqchment to an Outlook email then deletes the file.

It used to work before I made both changes to the the code dealing with
Excel and Outlook application/object.

Now I get an error at the line:

DoCmd.OutputTo acOutputQuery, "process export qry", acFormatXLS,
CurrentProject.Path & "\" & pFilename, 0

saying that it cannot save the output file to the file name specified.

As I mentioned this used to work before I made changes. It does compile
though.

Any help will be appreciated.

The code is:


Function compexport()

Dim stto As String
Dim stcc As String
Dim stsubject As String
Dim ststartDate As String
Dim stenddate As String
Dim stfrmt As String
Dim stconame As String
Dim stmessage As String
Dim stnoto As String
Dim stnodate As String
Dim stnoconame As String
Dim stpermnumber As String
Dim ststartdateatt As String
Dim stenddateatt As String
Dim mPathAndFile As String, mFileNumber As Integer
Dim R As Recordset, mFieldNum As Integer
Dim mOutputString As String
Dim booDelimitFields As Boolean
Dim booIncludeFieldnames As Boolean
Dim mFieldDeli As String
Dim pbooIncludeFieldnames As String


Dim outApp As Object, objOutlook As Object, outmsg As Object, olmailitem
As
Object


Dim oexcel As Object
'Dim osheet As Worksheet
Dim osheet As Object
'Dim rngToFormat As Range
Dim rngToFormat As Object



stconame = Nz([Forms]![export form]![coname], "none")
ststartDate = Nz([Forms]![export form]![begin], "none")
ststartdateatt = Replace(ststartDate, "/", "-")
stenddate = Nz([Forms]![export form]![end], "none")
stenddateatt = Replace(stenddate, "/", "-")
stpermnumber = Nz([Forms]![export form]![cmbpermnumber], "none")
stfrmt = DLookup("[Comp_format]", "export format settings")
stsubject = stconame & " " & "Compliance Sampling Data" & " " &
ststartDate
& " " & "to" & " " & stenddate
stto = Nz([Forms]![export form]![to], "none")
stcc = Nz([Forms]![export form]![cc], "")
stmessage = Nz([Forms]![export form]![Message], "")
stnoto = "You forgot to enter a Send To email address"
stnodate = "You must enter a beginning and ending date for the data you
wish
to export"
stnoconame = "You forgot to enter a company name"

pbooIncludeFieldnames = "true"

If stto = "none" Then

MsgBox stnoto

Exit Function

Else

If stconame = "none" Then

MsgBox stnoconame

Exit Function

Else

If ststartDate = "none" Then

MsgBox stnodate

Exit Function

Else

If stendate = "none" Then

MsgBox stnodate

Exit Function


Else

If stfrmt = "acFormatXLS" Then

'DoCmd.SendObject acSendQuery, "compliance export qry", acFormatXLS,
[stto],
[stcc], , stconame & " " & "Compliance Sampling Data" & " " & ststartDate
& "
" & "to" & " " & stenddate, [stmessage], False



pFilename = stconame & " P" & stpermnumber & " " & ststartdateatt & " to "
&
stenddateatt & " Compliance Data.xls"



DoCmd.OutputTo acOutputQuery, "compliance export qry", acFormatXLS,
CurrentProject.Path & "\" & pFilename, 0
'DoCmd.TransferSpreadsheet acExport, , "compliance export qry",
CurrentProject.Path & "\" & pFilename, True
'DoCmd.TransferSpreadsheet acImport, 3,"Employees","C:\Lotus\Newemps.wk3",
True, "A1:G12"

mPathAndFile = CurrentProject.Path & "\" & pFilename





'*************************


'Set oapp = CreateObject("Excel.Application")
Set oapp = CreateObject("Excel.Application")
Set oexcel = oapp.Workbooks.Open(Filename:=mPathAndFile)
Set osheet = oexcel.Worksheets("compliance export qry")

oapp.Visible = False
oapp.DisplayAlerts = False
osheet.Activate

With oexcel.Worksheets("compliance export qry").Columns

.Columns("A:S").AutoFit

End With


With oexcel.Worksheets("compliance export qry").PageSetup
.Zoom = False
.FitToPagesTall = 1000
.FitToPagesWide = 1
.Orientation = 2
.PrintGridlines = 0
.PrintTitleRows = "A1:S1"
'.LeftHeader =
.CenterHeader = "&14" & pFilename & "&10"
'.RightHeader =
.LeftFooter = "Report Created &D &T"
'.CenterFooter =
.RightFooter = "Page &P of &N"


.LeftMargin = oapp.InchesToPoints(0.25)
.RightMargin = oapp.InchesToPoints(0.25)
.TopMargin = oapp.InchesToPoints(0.75)
.BottomMargin = oapp.InchesToPoints(0.5)
.HeaderMargin = oapp.InchesToPoints(0.5)
.FooterMargin = oapp.InchesToPoints(0.25)

End With


With osheet.Range("A1:S1")
Set rngToFormat = osheet.Range(oexcel.Worksheets("compliance export
qry").Range("S1"), .Cells(osheet.Rows.Count, "C").end(xlUp).Offset(0, -2))
'

End With

With rngToFormat.Cells.Select



'With borders


oapp.Selection.Interior.ColorIndex = 2
oapp.Selection.Interior.Pattern = 1

oapp.Selection.Borders(xlDiagonalDown.LineStyle) = -4142
oapp.Selection.Borders(xlDiagonalUp).LineStyle = -4142
With oapp.Selection.Borders(xlEdgeLeft)
.LineStyle = 1
.Weight = 2
.ColorIndex = -4105
End With
With oapp.Selection.Borders(xlEdgeTop)
.LineStyle = 1
.Weight = 2
.ColorIndex = -4105
End With
With oapp.Selection.Borders(xlEdgeBottom)
.LineStyle = 1
.Weight = 2
.ColorIndex = -4105
End With
With oapp.Selection.Borders(xlEdgeRight)
.LineStyle = 1
.Weight = 2
.ColorIndex = -4105
End With
With oapp.Selection.Borders(xlInsideVertical)
.LineStyle = 1
.Weight = 2
.ColorIndex = -4105
End With
With oapp.Selection.Borders(xlInsideHorizontal)
.LineStyle = 1
.Weight = 2
.ColorIndex = -4105
End With

End With



With osheet.Range("A1:S1")
.Font.ColorIndex = 1
.Font.Bold = -1
.Interior.ColorIndex = 15
.Interior.Pattern = 1
End With




Set osheet = Nothing 'disconnect from the Worksheet
oexcel.Close SaveChanges:=True 'Save (and disconnect from) the Workbook



Set oexcel = Nothing
oapp.Quit 'Close (and disconnect from) Excel
Set oapp = Nothing



'*******************************************

Set outApp = CreateObject("Outlook.Application")
Set outmsg = outApp.CreateItem(olmailitem)



If stcc = "" Then

With outmsg

.Recipients.Add (stto)
.subject = stsubject
.ReadReceiptRequested = -1
.body = stmessage
.Importance = olImportanceHigh
.Attachments.Add (mPathAndFile)
.Send

End With

Else

With outmsg

.Recipients.Add(stto).Type = 1
Click to expand...
Click to expand...
 
D

Douglas J. Steele

Periods in the file name shouldn't matter.

You mention that you're using Late Binding for Outlook. Are you also using
Late Binding for Excel? If so, have you defined the value of xlUp?
(it's -4162)

As well, unless that line is inside a With construct, I think it should be

Set rngToFormat = osheet.Range(oexcel.Worksheets("compliance export
qry").Range("S1"), osheet.Cells(osheet.Rows.Count,
"C").end(xlUp).Offset(0, -2))

or possibly

Set rngToFormat = osheet.Range(oexcel.Worksheets("compliance export
qry").Range("S1"), oexcel.Worksheets("compliance export
qry").Cells(osheet.Rows.Count, "C").end(xlUp).Offset(0, -2)) '

Is the error message you posted the exact error message? I somehow doubt
that Access is generating that error message: it's likely code in the
application that's checking for some condition, and raising the message
itself.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


tmort said:
The file extension I am using is xls.

I think I found that problem, but, now I have two more.

I had some dummy data that put a "." in the file name where it didn't
belong. It was :

ABC Co. P0002 12-20-2007 to 12-21-2007 Compliance Data.xls

instead of

ABC Co. P0002 12-20-2007 to 12-21-2007 Compliance Data.xls

Now it chokes at:

Set rngToFormat = osheet.Range(oexcel.Worksheets("compliance export
qry").Range("S1"), .Cells(osheet.Rows.Count, "C").end(xlUp).Offset(0, -2))
'

The db also has a front end and backend and for some reason I can no
longer
open it (without using the shift key). It is set to automatically link
the
front end to the backend or for you to point to the backend.

I get a message sying that:

File dbpath does not contain the required backenddb tables.

I haven't done anything to the backend to remove or change any tables.


I know I'm vearing of topic but any ideas?

It still does this even if I manually link the tables


Douglas J. Steele said:
Don't know for sure that this is relevant, but what file extension are
you
using? Access is picky in some cases.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


tmort said:
I have some code that I am trying to switch to late binding. It is code
that
saves the result of a query as an Excel file, then sends this file as
an
attaqchment to an Outlook email then deletes the file.

It used to work before I made both changes to the the code dealing with
Excel and Outlook application/object.

Now I get an error at the line:

DoCmd.OutputTo acOutputQuery, "process export qry", acFormatXLS,
CurrentProject.Path & "\" & pFilename, 0

saying that it cannot save the output file to the file name specified.

As I mentioned this used to work before I made changes. It does
compile
though.

Any help will be appreciated.

The code is:


Function compexport()

Dim stto As String
Dim stcc As String
Dim stsubject As String
Dim ststartDate As String
Dim stenddate As String
Dim stfrmt As String
Dim stconame As String
Dim stmessage As String
Dim stnoto As String
Dim stnodate As String
Dim stnoconame As String
Dim stpermnumber As String
Dim ststartdateatt As String
Dim stenddateatt As String
Dim mPathAndFile As String, mFileNumber As Integer
Dim R As Recordset, mFieldNum As Integer
Dim mOutputString As String
Dim booDelimitFields As Boolean
Dim booIncludeFieldnames As Boolean
Dim mFieldDeli As String
Dim pbooIncludeFieldnames As String


Dim outApp As Object, objOutlook As Object, outmsg As Object,
olmailitem
As
Object


Dim oexcel As Object
'Dim osheet As Worksheet
Dim osheet As Object
'Dim rngToFormat As Range
Dim rngToFormat As Object



stconame = Nz([Forms]![export form]![coname], "none")
ststartDate = Nz([Forms]![export form]![begin], "none")
ststartdateatt = Replace(ststartDate, "/", "-")
stenddate = Nz([Forms]![export form]![end], "none")
stenddateatt = Replace(stenddate, "/", "-")
stpermnumber = Nz([Forms]![export form]![cmbpermnumber], "none")
stfrmt = DLookup("[Comp_format]", "export format settings")
stsubject = stconame & " " & "Compliance Sampling Data" & " " &
ststartDate
& " " & "to" & " " & stenddate
stto = Nz([Forms]![export form]![to], "none")
stcc = Nz([Forms]![export form]![cc], "")
stmessage = Nz([Forms]![export form]![Message], "")
stnoto = "You forgot to enter a Send To email address"
stnodate = "You must enter a beginning and ending date for the data you
wish
to export"
stnoconame = "You forgot to enter a company name"

pbooIncludeFieldnames = "true"

If stto = "none" Then

MsgBox stnoto

Exit Function

Else

If stconame = "none" Then

MsgBox stnoconame

Exit Function

Else

If ststartDate = "none" Then

MsgBox stnodate

Exit Function

Else

If stendate = "none" Then

MsgBox stnodate

Exit Function


Else

If stfrmt = "acFormatXLS" Then

'DoCmd.SendObject acSendQuery, "compliance export qry", acFormatXLS,
[stto],
[stcc], , stconame & " " & "Compliance Sampling Data" & " " &
ststartDate
& "
" & "to" & " " & stenddate, [stmessage], False



pFilename = stconame & " P" & stpermnumber & " " & ststartdateatt & "
to "
&
stenddateatt & " Compliance Data.xls"



DoCmd.OutputTo acOutputQuery, "compliance export qry", acFormatXLS,
CurrentProject.Path & "\" & pFilename, 0
'DoCmd.TransferSpreadsheet acExport, , "compliance export qry",
CurrentProject.Path & "\" & pFilename, True
'DoCmd.TransferSpreadsheet acImport,
3,"Employees","C:\Lotus\Newemps.wk3",
True, "A1:G12"

mPathAndFile = CurrentProject.Path & "\" & pFilename





'*************************


'Set oapp = CreateObject("Excel.Application")
Set oapp = CreateObject("Excel.Application")
Set oexcel = oapp.Workbooks.Open(Filename:=mPathAndFile)
Set osheet = oexcel.Worksheets("compliance export qry")

oapp.Visible = False
oapp.DisplayAlerts = False
osheet.Activate

With oexcel.Worksheets("compliance export qry").Columns

.Columns("A:S").AutoFit

End With


With oexcel.Worksheets("compliance export qry").PageSetup
.Zoom = False
.FitToPagesTall = 1000
.FitToPagesWide = 1
.Orientation = 2
.PrintGridlines = 0
.PrintTitleRows = "A1:S1"
'.LeftHeader =
.CenterHeader = "&14" & pFilename & "&10"
'.RightHeader =
.LeftFooter = "Report Created &D &T"
'.CenterFooter =
.RightFooter = "Page &P of &N"


.LeftMargin = oapp.InchesToPoints(0.25)
.RightMargin = oapp.InchesToPoints(0.25)
.TopMargin = oapp.InchesToPoints(0.75)
.BottomMargin = oapp.InchesToPoints(0.5)
.HeaderMargin = oapp.InchesToPoints(0.5)
.FooterMargin = oapp.InchesToPoints(0.25)

End With


With osheet.Range("A1:S1")
Set rngToFormat = osheet.Range(oexcel.Worksheets("compliance export
qry").Range("S1"), .Cells(osheet.Rows.Count,
"C").end(xlUp).Offset(0, -2))
'

End With

With rngToFormat.Cells.Select



'With borders


oapp.Selection.Interior.ColorIndex = 2
oapp.Selection.Interior.Pattern = 1

oapp.Selection.Borders(xlDiagonalDown.LineStyle) = -4142
oapp.Selection.Borders(xlDiagonalUp).LineStyle = -4142
With oapp.Selection.Borders(xlEdgeLeft)
.LineStyle = 1
.Weight = 2
.ColorIndex = -4105
End With
With oapp.Selection.Borders(xlEdgeTop)
.LineStyle = 1
.Weight = 2
.ColorIndex = -4105
End With
With oapp.Selection.Borders(xlEdgeBottom)
.LineStyle = 1
.Weight = 2
.ColorIndex = -4105
End With
With oapp.Selection.Borders(xlEdgeRight)
.LineStyle = 1
.Weight = 2
.ColorIndex = -4105
End With
With oapp.Selection.Borders(xlInsideVertical)
.LineStyle = 1
.Weight = 2
.ColorIndex = -4105
End With
With oapp.Selection.Borders(xlInsideHorizontal)
.LineStyle = 1
.Weight = 2
.ColorIndex = -4105
End With

End With



With osheet.Range("A1:S1")
.Font.ColorIndex = 1
.Font.Bold = -1
.Interior.ColorIndex = 15
.Interior.Pattern = 1
End With




Set osheet = Nothing 'disconnect from the Worksheet
oexcel.Close SaveChanges:=True 'Save (and disconnect from) the Workbook



Set oexcel = Nothing
oapp.Quit 'Close (and disconnect from) Excel
Set oapp = Nothing



'*******************************************

Set outApp = CreateObject("Outlook.Application")
Set outmsg = outApp.CreateItem(olmailitem)



If stcc = "" Then

With outmsg

.Recipients.Add (stto)
.subject = stsubject
.ReadReceiptRequested = -1
.body = stmessage
.Importance = olImportanceHigh
.Attachments.Add (mPathAndFile)
.Send

End With

Else

With outmsg

.Recipients.Add(stto).Type = 1
Click to expand...
Click to expand...
Click to expand...
 
T

tmort

I think I did find the problem of the file not saving. It actually saves the
file, but either because I had errors in ,y code and it didn't get to kell
mpathfilename or because I have to stop Excel after I am done saving it. I
noticed that I couldn't delete this file because it said it was open with
Excel. I didn't have Excel open but in the task manager I found a number of
instances where it was open. I closed all of them and it would save the file.

What command do I use to terminate Excel?

Also, I Made the change to -4162 and now the code gets hung up at:

With oapp.Selection.Borders(xlDiagonalDown)
.LineStyle = -4142
End With
With oapp.Selection.Borders(xlDiagonalUp)
.LineStyle = -4142
End With

I'll have to look into the other error more.

Douglas J. Steele said:
Periods in the file name shouldn't matter.

You mention that you're using Late Binding for Outlook. Are you also using
Late Binding for Excel? If so, have you defined the value of xlUp?
(it's -4162)

As well, unless that line is inside a With construct, I think it should be

Set rngToFormat = osheet.Range(oexcel.Worksheets("compliance export
qry").Range("S1"), osheet.Cells(osheet.Rows.Count,
"C").end(xlUp).Offset(0, -2))

or possibly

Set rngToFormat = osheet.Range(oexcel.Worksheets("compliance export
qry").Range("S1"), oexcel.Worksheets("compliance export
qry").Cells(osheet.Rows.Count, "C").end(xlUp).Offset(0, -2)) '

Is the error message you posted the exact error message? I somehow doubt
that Access is generating that error message: it's likely code in the
application that's checking for some condition, and raising the message
itself.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


tmort said:
The file extension I am using is xls.

I think I found that problem, but, now I have two more.

I had some dummy data that put a "." in the file name where it didn't
belong. It was :

ABC Co. P0002 12-20-2007 to 12-21-2007 Compliance Data.xls

instead of

ABC Co. P0002 12-20-2007 to 12-21-2007 Compliance Data.xls

Now it chokes at:

Set rngToFormat = osheet.Range(oexcel.Worksheets("compliance export
qry").Range("S1"), .Cells(osheet.Rows.Count, "C").end(xlUp).Offset(0, -2))
'

The db also has a front end and backend and for some reason I can no
longer
open it (without using the shift key). It is set to automatically link
the
front end to the backend or for you to point to the backend.

I get a message sying that:

File dbpath does not contain the required backenddb tables.

I haven't done anything to the backend to remove or change any tables.


I know I'm vearing of topic but any ideas?

It still does this even if I manually link the tables


Douglas J. Steele said:
Don't know for sure that this is relevant, but what file extension are
you
using? Access is picky in some cases.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I have some code that I am trying to switch to late binding. It is code
that
saves the result of a query as an Excel file, then sends this file as
an
attaqchment to an Outlook email then deletes the file.

It used to work before I made both changes to the the code dealing with
Excel and Outlook application/object.

Now I get an error at the line:

DoCmd.OutputTo acOutputQuery, "process export qry", acFormatXLS,
CurrentProject.Path & "\" & pFilename, 0

saying that it cannot save the output file to the file name specified.

As I mentioned this used to work before I made changes. It does
compile
though.

Any help will be appreciated.

The code is:


Function compexport()

Dim stto As String
Dim stcc As String
Dim stsubject As String
Dim ststartDate As String
Dim stenddate As String
Dim stfrmt As String
Dim stconame As String
Dim stmessage As String
Dim stnoto As String
Dim stnodate As String
Dim stnoconame As String
Dim stpermnumber As String
Dim ststartdateatt As String
Dim stenddateatt As String
Dim mPathAndFile As String, mFileNumber As Integer
Dim R As Recordset, mFieldNum As Integer
Dim mOutputString As String
Dim booDelimitFields As Boolean
Dim booIncludeFieldnames As Boolean
Dim mFieldDeli As String
Dim pbooIncludeFieldnames As String


Dim outApp As Object, objOutlook As Object, outmsg As Object,
olmailitem
As
Object


Dim oexcel As Object
'Dim osheet As Worksheet
Dim osheet As Object
'Dim rngToFormat As Range
Dim rngToFormat As Object



stconame = Nz([Forms]![export form]![coname], "none")
ststartDate = Nz([Forms]![export form]![begin], "none")
ststartdateatt = Replace(ststartDate, "/", "-")
stenddate = Nz([Forms]![export form]![end], "none")
stenddateatt = Replace(stenddate, "/", "-")
stpermnumber = Nz([Forms]![export form]![cmbpermnumber], "none")
stfrmt = DLookup("[Comp_format]", "export format settings")
stsubject = stconame & " " & "Compliance Sampling Data" & " " &
ststartDate
& " " & "to" & " " & stenddate
stto = Nz([Forms]![export form]![to], "none")
stcc = Nz([Forms]![export form]![cc], "")
stmessage = Nz([Forms]![export form]![Message], "")
stnoto = "You forgot to enter a Send To email address"
stnodate = "You must enter a beginning and ending date for the data you
wish
to export"
stnoconame = "You forgot to enter a company name"

pbooIncludeFieldnames = "true"

If stto = "none" Then

MsgBox stnoto

Exit Function

Else

If stconame = "none" Then

MsgBox stnoconame

Exit Function

Else

If ststartDate = "none" Then

MsgBox stnodate

Exit Function

Else

If stendate = "none" Then

MsgBox stnodate

Exit Function


Else

If stfrmt = "acFormatXLS" Then

'DoCmd.SendObject acSendQuery, "compliance export qry", acFormatXLS,
[stto],
[stcc], , stconame & " " & "Compliance Sampling Data" & " " &
ststartDate
& "
" & "to" & " " & stenddate, [stmessage], False



pFilename = stconame & " P" & stpermnumber & " " & ststartdateatt & "
to "
&
stenddateatt & " Compliance Data.xls"



DoCmd.OutputTo acOutputQuery, "compliance export qry", acFormatXLS,
CurrentProject.Path & "\" & pFilename, 0
'DoCmd.TransferSpreadsheet acExport, , "compliance export qry",
CurrentProject.Path & "\" & pFilename, True
'DoCmd.TransferSpreadsheet acImport,
3,"Employees","C:\Lotus\Newemps.wk3",
True, "A1:G12"

mPathAndFile = CurrentProject.Path & "\" & pFilename





'*************************


'Set oapp = CreateObject("Excel.Application")
Set oapp = CreateObject("Excel.Application")
Set oexcel = oapp.Workbooks.Open(Filename:=mPathAndFile)
Set osheet = oexcel.Worksheets("compliance export qry")

oapp.Visible = False
oapp.DisplayAlerts = False
osheet.Activate

With oexcel.Worksheets("compliance export qry").Columns

.Columns("A:S").AutoFit

End With


With oexcel.Worksheets("compliance export qry").PageSetup
.Zoom = False
.FitToPagesTall = 1000
.FitToPagesWide = 1
.Orientation = 2
.PrintGridlines = 0
.PrintTitleRows = "A1:S1"
'.LeftHeader =
.CenterHeader = "&14" & pFilename & "&10"
'.RightHeader =
.LeftFooter = "Report Created &D &T"
'.CenterFooter =
.RightFooter = "Page &P of &N"


.LeftMargin = oapp.InchesToPoints(0.25)
.RightMargin = oapp.InchesToPoints(0.25)
.TopMargin = oapp.InchesToPoints(0.75)
.BottomMargin = oapp.InchesToPoints(0.5)
.HeaderMargin = oapp.InchesToPoints(0.5)
.FooterMargin = oapp.InchesToPoints(0.25)

End With


With osheet.Range("A1:S1")
Set rngToFormat = osheet.Range(oexcel.Worksheets("compliance export
qry").Range("S1"), .Cells(osheet.Rows.Count,
"C").end(xlUp).Offset(0, -2))
'

End With

With rngToFormat.Cells.Select



'With borders


oapp.Selection.Interior.ColorIndex = 2
oapp.Selection.Interior.Pattern = 1

oapp.Selection.Borders(xlDiagonalDown.LineStyle) = -4142
oapp.Selection.Borders(xlDiagonalUp).LineStyle = -4142
With oapp.Selection.Borders(xlEdgeLeft)
.LineStyle = 1
.Weight = 2
.ColorIndex = -4105
Click to expand...
Click to expand...
Click to expand...
 
D

Douglas J. Steele

You still didn't answer whether or not you've got a reference to Excel, or
if you're using Late Binding for it as well.

If you're using Late Binding, you must provide values for all of the
intrinsic Excel constants (the ones that start xl, like xlDiagonalDown,
xlDiagonalUp and so on).

Do you have VBA set up to require declaration of all variables? (I've never
understood why that's not the default!) Do the words "Option Explicit"
appear at the top of each module? If not, add them (and go into Tool |
Options and check the "Require Variable Declaration" box on the Editor tab
so that the line will appear on all subsequent modules) (The reason I'm
telling you this is that you'll quickly find out what constants haven't been
defined when you have that option set)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


tmort said:
I think I did find the problem of the file not saving. It actually saves
the
file, but either because I had errors in ,y code and it didn't get to kell
mpathfilename or because I have to stop Excel after I am done saving it.
I
noticed that I couldn't delete this file because it said it was open with
Excel. I didn't have Excel open but in the task manager I found a number
of
instances where it was open. I closed all of them and it would save the
file.

What command do I use to terminate Excel?

Also, I Made the change to -4162 and now the code gets hung up at:

With oapp.Selection.Borders(xlDiagonalDown)
.LineStyle = -4142
End With
With oapp.Selection.Borders(xlDiagonalUp)
.LineStyle = -4142
End With

I'll have to look into the other error more.

Douglas J. Steele said:
Periods in the file name shouldn't matter.

You mention that you're using Late Binding for Outlook. Are you also
using
Late Binding for Excel? If so, have you defined the value of xlUp?
(it's -4162)

As well, unless that line is inside a With construct, I think it should
be

Set rngToFormat = osheet.Range(oexcel.Worksheets("compliance export
qry").Range("S1"), osheet.Cells(osheet.Rows.Count,
"C").end(xlUp).Offset(0, -2))

or possibly

Set rngToFormat = osheet.Range(oexcel.Worksheets("compliance export
qry").Range("S1"), oexcel.Worksheets("compliance export
qry").Cells(osheet.Rows.Count, "C").end(xlUp).Offset(0, -2)) '

Is the error message you posted the exact error message? I somehow doubt
that Access is generating that error message: it's likely code in the
application that's checking for some condition, and raising the message
itself.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


tmort said:
The file extension I am using is xls.

I think I found that problem, but, now I have two more.

I had some dummy data that put a "." in the file name where it didn't
belong. It was :

ABC Co. P0002 12-20-2007 to 12-21-2007 Compliance Data.xls

instead of

ABC Co. P0002 12-20-2007 to 12-21-2007 Compliance Data.xls

Now it chokes at:

Set rngToFormat = osheet.Range(oexcel.Worksheets("compliance export
qry").Range("S1"), .Cells(osheet.Rows.Count,
"C").end(xlUp).Offset(0, -2))
'

The db also has a front end and backend and for some reason I can no
longer
open it (without using the shift key). It is set to automatically link
the
front end to the backend or for you to point to the backend.

I get a message sying that:

File dbpath does not contain the required backenddb tables.

I haven't done anything to the backend to remove or change any tables.


I know I'm vearing of topic but any ideas?

It still does this even if I manually link the tables


:

Don't know for sure that this is relevant, but what file extension are
you
using? Access is picky in some cases.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I have some code that I am trying to switch to late binding. It is
code
that
saves the result of a query as an Excel file, then sends this file
as
an
attaqchment to an Outlook email then deletes the file.

It used to work before I made both changes to the the code dealing
with
Excel and Outlook application/object.

Now I get an error at the line:

DoCmd.OutputTo acOutputQuery, "process export qry", acFormatXLS,
CurrentProject.Path & "\" & pFilename, 0

saying that it cannot save the output file to the file name
specified.

As I mentioned this used to work before I made changes. It does
compile
though.

Any help will be appreciated.

The code is:


Function compexport()

Dim stto As String
Dim stcc As String
Dim stsubject As String
Dim ststartDate As String
Dim stenddate As String
Dim stfrmt As String
Dim stconame As String
Dim stmessage As String
Dim stnoto As String
Dim stnodate As String
Dim stnoconame As String
Dim stpermnumber As String
Dim ststartdateatt As String
Dim stenddateatt As String
Dim mPathAndFile As String, mFileNumber As Integer
Dim R As Recordset, mFieldNum As Integer
Dim mOutputString As String
Dim booDelimitFields As Boolean
Dim booIncludeFieldnames As Boolean
Dim mFieldDeli As String
Dim pbooIncludeFieldnames As String


Dim outApp As Object, objOutlook As Object, outmsg As Object,
olmailitem
As
Object


Dim oexcel As Object
'Dim osheet As Worksheet
Dim osheet As Object
'Dim rngToFormat As Range
Dim rngToFormat As Object



stconame = Nz([Forms]![export form]![coname], "none")
ststartDate = Nz([Forms]![export form]![begin], "none")
ststartdateatt = Replace(ststartDate, "/", "-")
stenddate = Nz([Forms]![export form]![end], "none")
stenddateatt = Replace(stenddate, "/", "-")
stpermnumber = Nz([Forms]![export form]![cmbpermnumber], "none")
stfrmt = DLookup("[Comp_format]", "export format settings")
stsubject = stconame & " " & "Compliance Sampling Data" & " " &
ststartDate
& " " & "to" & " " & stenddate
stto = Nz([Forms]![export form]![to], "none")
stcc = Nz([Forms]![export form]![cc], "")
stmessage = Nz([Forms]![export form]![Message], "")
stnoto = "You forgot to enter a Send To email address"
stnodate = "You must enter a beginning and ending date for the data
you
wish
to export"
stnoconame = "You forgot to enter a company name"

pbooIncludeFieldnames = "true"

If stto = "none" Then

MsgBox stnoto

Exit Function

Else

If stconame = "none" Then

MsgBox stnoconame

Exit Function

Else

If ststartDate = "none" Then

MsgBox stnodate

Exit Function

Else

If stendate = "none" Then

MsgBox stnodate

Exit Function


Else

If stfrmt = "acFormatXLS" Then

'DoCmd.SendObject acSendQuery, "compliance export qry", acFormatXLS,
[stto],
[stcc], , stconame & " " & "Compliance Sampling Data" & " " &
ststartDate
& "
" & "to" & " " & stenddate, [stmessage], False



pFilename = stconame & " P" & stpermnumber & " " & ststartdateatt &
"
to "
&
stenddateatt & " Compliance Data.xls"



DoCmd.OutputTo acOutputQuery, "compliance export qry", acFormatXLS,
CurrentProject.Path & "\" & pFilename, 0
'DoCmd.TransferSpreadsheet acExport, , "compliance export qry",
CurrentProject.Path & "\" & pFilename, True
'DoCmd.TransferSpreadsheet acImport,
3,"Employees","C:\Lotus\Newemps.wk3",
True, "A1:G12"

mPathAndFile = CurrentProject.Path & "\" & pFilename





'*************************


'Set oapp = CreateObject("Excel.Application")
Set oapp = CreateObject("Excel.Application")
Set oexcel = oapp.Workbooks.Open(Filename:=mPathAndFile)
Set osheet = oexcel.Worksheets("compliance export qry")

oapp.Visible = False
oapp.DisplayAlerts = False
osheet.Activate

With oexcel.Worksheets("compliance export qry").Columns

.Columns("A:S").AutoFit

End With


With oexcel.Worksheets("compliance export qry").PageSetup
.Zoom = False
.FitToPagesTall = 1000
.FitToPagesWide = 1
.Orientation = 2
.PrintGridlines = 0
.PrintTitleRows = "A1:S1"
'.LeftHeader =
.CenterHeader = "&14" & pFilename & "&10"
'.RightHeader =
.LeftFooter = "Report Created &D &T"
'.CenterFooter =
.RightFooter = "Page &P of &N"


.LeftMargin = oapp.InchesToPoints(0.25)
.RightMargin = oapp.InchesToPoints(0.25)
.TopMargin = oapp.InchesToPoints(0.75)
.BottomMargin = oapp.InchesToPoints(0.5)
.HeaderMargin = oapp.InchesToPoints(0.5)
.FooterMargin = oapp.InchesToPoints(0.25)

End With


With osheet.Range("A1:S1")
Set rngToFormat = osheet.Range(oexcel.Worksheets("compliance export
qry").Range("S1"), .Cells(osheet.Rows.Count,
"C").end(xlUp).Offset(0, -2))
'

End With

With rngToFormat.Cells.Select



'With borders


oapp.Selection.Interior.ColorIndex = 2
oapp.Selection.Interior.Pattern = 1

oapp.Selection.Borders(xlDiagonalDown.LineStyle) = -4142
oapp.Selection.Borders(xlDiagonalUp).LineStyle = -4142
With oapp.Selection.Borders(xlEdgeLeft)
.LineStyle = 1
.Weight = 2
.ColorIndex = -4105
Click to expand...
Click to expand...
Click to expand...
 
T

tmort

Yes,

I'm using late binding for Excel as well. I didn't have options explicit.
I've done so now and found a number of items that needed to be dimaensioned.
I also found some instances where I missed switching the xl constants to the
numeric constants.

I have to look over the code some more because now when I compile I get an
Else without an If message.

Also, the output file continues to be being held open by Excel and there is
an Excel process running that I have to turn off.



tmort said:
I think I did find the problem of the file not saving. It actually saves the
file, but either because I had errors in ,y code and it didn't get to kell
mpathfilename or because I have to stop Excel after I am done saving it. I
noticed that I couldn't delete this file because it said it was open with
Excel. I didn't have Excel open but in the task manager I found a number of
instances where it was open. I closed all of them and it would save the file.

What command do I use to terminate Excel?

Also, I Made the change to -4162 and now the code gets hung up at:

With oapp.Selection.Borders(xlDiagonalDown)
.LineStyle = -4142
End With
With oapp.Selection.Borders(xlDiagonalUp)
.LineStyle = -4142
End With

I'll have to look into the other error more.

Douglas J. Steele said:
Periods in the file name shouldn't matter.

You mention that you're using Late Binding for Outlook. Are you also using
Late Binding for Excel? If so, have you defined the value of xlUp?
(it's -4162)

As well, unless that line is inside a With construct, I think it should be

Set rngToFormat = osheet.Range(oexcel.Worksheets("compliance export
qry").Range("S1"), osheet.Cells(osheet.Rows.Count,
"C").end(xlUp).Offset(0, -2))

or possibly

Set rngToFormat = osheet.Range(oexcel.Worksheets("compliance export
qry").Range("S1"), oexcel.Worksheets("compliance export
qry").Cells(osheet.Rows.Count, "C").end(xlUp).Offset(0, -2)) '

Is the error message you posted the exact error message? I somehow doubt
that Access is generating that error message: it's likely code in the
application that's checking for some condition, and raising the message
itself.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


tmort said:
The file extension I am using is xls.

I think I found that problem, but, now I have two more.

I had some dummy data that put a "." in the file name where it didn't
belong. It was :

ABC Co. P0002 12-20-2007 to 12-21-2007 Compliance Data.xls

instead of

ABC Co. P0002 12-20-2007 to 12-21-2007 Compliance Data.xls

Now it chokes at:

Set rngToFormat = osheet.Range(oexcel.Worksheets("compliance export
qry").Range("S1"), .Cells(osheet.Rows.Count, "C").end(xlUp).Offset(0, -2))
'

The db also has a front end and backend and for some reason I can no
longer
open it (without using the shift key). It is set to automatically link
the
front end to the backend or for you to point to the backend.

I get a message sying that:

File dbpath does not contain the required backenddb tables.

I haven't done anything to the backend to remove or change any tables.


I know I'm vearing of topic but any ideas?

It still does this even if I manually link the tables


:

Don't know for sure that this is relevant, but what file extension are
you
using? Access is picky in some cases.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I have some code that I am trying to switch to late binding. It is code
that
saves the result of a query as an Excel file, then sends this file as
an
attaqchment to an Outlook email then deletes the file.

It used to work before I made both changes to the the code dealing with
Excel and Outlook application/object.

Now I get an error at the line:

DoCmd.OutputTo acOutputQuery, "process export qry", acFormatXLS,
CurrentProject.Path & "\" & pFilename, 0

saying that it cannot save the output file to the file name specified.

As I mentioned this used to work before I made changes. It does
compile
though.

Any help will be appreciated.

The code is:


Function compexport()

Dim stto As String
Dim stcc As String
Dim stsubject As String
Dim ststartDate As String
Dim stenddate As String
Dim stfrmt As String
Dim stconame As String
Dim stmessage As String
Dim stnoto As String
Dim stnodate As String
Dim stnoconame As String
Dim stpermnumber As String
Dim ststartdateatt As String
Dim stenddateatt As String
Dim mPathAndFile As String, mFileNumber As Integer
Dim R As Recordset, mFieldNum As Integer
Dim mOutputString As String
Dim booDelimitFields As Boolean
Dim booIncludeFieldnames As Boolean
Dim mFieldDeli As String
Dim pbooIncludeFieldnames As String


Dim outApp As Object, objOutlook As Object, outmsg As Object,
olmailitem
As
Object


Dim oexcel As Object
'Dim osheet As Worksheet
Dim osheet As Object
'Dim rngToFormat As Range
Dim rngToFormat As Object



stconame = Nz([Forms]![export form]![coname], "none")
ststartDate = Nz([Forms]![export form]![begin], "none")
ststartdateatt = Replace(ststartDate, "/", "-")
stenddate = Nz([Forms]![export form]![end], "none")
stenddateatt = Replace(stenddate, "/", "-")
stpermnumber = Nz([Forms]![export form]![cmbpermnumber], "none")
stfrmt = DLookup("[Comp_format]", "export format settings")
stsubject = stconame & " " & "Compliance Sampling Data" & " " &
ststartDate
& " " & "to" & " " & stenddate
stto = Nz([Forms]![export form]![to], "none")
stcc = Nz([Forms]![export form]![cc], "")
stmessage = Nz([Forms]![export form]![Message], "")
stnoto = "You forgot to enter a Send To email address"
stnodate = "You must enter a beginning and ending date for the data you
wish
to export"
stnoconame = "You forgot to enter a company name"

pbooIncludeFieldnames = "true"

If stto = "none" Then

MsgBox stnoto

Exit Function

Else

If stconame = "none" Then

MsgBox stnoconame

Exit Function

Else

If ststartDate = "none" Then

MsgBox stnodate

Exit Function

Else

If stendate = "none" Then

MsgBox stnodate

Exit Function


Else

If stfrmt = "acFormatXLS" Then

'DoCmd.SendObject acSendQuery, "compliance export qry", acFormatXLS,
[stto],
[stcc], , stconame & " " & "Compliance Sampling Data" & " " &
ststartDate
& "
" & "to" & " " & stenddate, [stmessage], False



pFilename = stconame & " P" & stpermnumber & " " & ststartdateatt & "
to "
&
stenddateatt & " Compliance Data.xls"



DoCmd.OutputTo acOutputQuery, "compliance export qry", acFormatXLS,
CurrentProject.Path & "\" & pFilename, 0
'DoCmd.TransferSpreadsheet acExport, , "compliance export qry",
CurrentProject.Path & "\" & pFilename, True
'DoCmd.TransferSpreadsheet acImport,
3,"Employees","C:\Lotus\Newemps.wk3",
True, "A1:G12"

mPathAndFile = CurrentProject.Path & "\" & pFilename





'*************************


'Set oapp = CreateObject("Excel.Application")
Set oapp = CreateObject("Excel.Application")
Set oexcel = oapp.Workbooks.Open(Filename:=mPathAndFile)
Set osheet = oexcel.Worksheets("compliance export qry")

oapp.Visible = False
oapp.DisplayAlerts = False
osheet.Activate

With oexcel.Worksheets("compliance export qry").Columns

.Columns("A:S").AutoFit

End With


With oexcel.Worksheets("compliance export qry").PageSetup
.Zoom = False
.FitToPagesTall = 1000
.FitToPagesWide = 1
.Orientation = 2
.PrintGridlines = 0
.PrintTitleRows = "A1:S1"
'.LeftHeader =
.CenterHeader = "&14" & pFilename & "&10"
'.RightHeader =
.LeftFooter = "Report Created &D &T"
'.CenterFooter =
.RightFooter = "Page &P of &N"


.LeftMargin = oapp.InchesToPoints(0.25)
.RightMargin = oapp.InchesToPoints(0.25)
.TopMargin = oapp.InchesToPoints(0.75)
.BottomMargin = oapp.InchesToPoints(0.5)
.HeaderMargin = oapp.InchesToPoints(0.5)
.FooterMargin = oapp.InchesToPoints(0.25)

End With


With osheet.Range("A1:S1")
Set rngToFormat = osheet.Range(oexcel.Worksheets("compliance export
Click to expand...
Click to expand...
Click to expand...
 
D

David W. Fenton

ABC Co. P0002 12-20-2007 to 12-21-2007 Compliance Data.xls
Click to expand...

You realize that in this case, your file name is "ABC Co" and the
extension is " P0002 12-20-2007 to 12-21-2007 Compliance Data.xls"?
 
D

Douglas J. Steele

Assuming you've got something like

Set xlCurr = CreateObject("Excel.Application")
Set wkbCurr = xlCurr.Workbooks.Open(strFile)

then it's necessary to close the workbook using

wkbCurr.Close SaveChanges:=True

(or SaveChanges:=False if you don't want to save the changes), then quit the
application using

xlCurr.Application.Quit
Set xlCurr = Nothing

I've been told that xlCurr.Quit should be sufficient though.


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


tmort said:
Yes,

I'm using late binding for Excel as well. I didn't have options explicit.
I've done so now and found a number of items that needed to be
dimaensioned.
I also found some instances where I missed switching the xl constants to
the
numeric constants.

I have to look over the code some more because now when I compile I get an
Else without an If message.

Also, the output file continues to be being held open by Excel and there
is
an Excel process running that I have to turn off.



tmort said:
I think I did find the problem of the file not saving. It actually saves
the
file, but either because I had errors in ,y code and it didn't get to
kell
mpathfilename or because I have to stop Excel after I am done saving it.
I
noticed that I couldn't delete this file because it said it was open with
Excel. I didn't have Excel open but in the task manager I found a number
of
instances where it was open. I closed all of them and it would save the
file.

What command do I use to terminate Excel?

Also, I Made the change to -4162 and now the code gets hung up at:

With oapp.Selection.Borders(xlDiagonalDown)
.LineStyle = -4142
End With
With oapp.Selection.Borders(xlDiagonalUp)
.LineStyle = -4142
End With

I'll have to look into the other error more.

Douglas J. Steele said:
Periods in the file name shouldn't matter.

You mention that you're using Late Binding for Outlook. Are you also
using
Late Binding for Excel? If so, have you defined the value of xlUp?
(it's -4162)

As well, unless that line is inside a With construct, I think it should
be

Set rngToFormat = osheet.Range(oexcel.Worksheets("compliance export
qry").Range("S1"), osheet.Cells(osheet.Rows.Count,
"C").end(xlUp).Offset(0, -2))

or possibly

Set rngToFormat = osheet.Range(oexcel.Worksheets("compliance export
qry").Range("S1"), oexcel.Worksheets("compliance export
qry").Cells(osheet.Rows.Count, "C").end(xlUp).Offset(0, -2)) '

Is the error message you posted the exact error message? I somehow
doubt
that Access is generating that error message: it's likely code in the
application that's checking for some condition, and raising the message
itself.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


The file extension I am using is xls.

I think I found that problem, but, now I have two more.

I had some dummy data that put a "." in the file name where it didn't
belong. It was :

ABC Co. P0002 12-20-2007 to 12-21-2007 Compliance Data.xls

instead of

ABC Co. P0002 12-20-2007 to 12-21-2007 Compliance Data.xls

Now it chokes at:

Set rngToFormat = osheet.Range(oexcel.Worksheets("compliance export
qry").Range("S1"), .Cells(osheet.Rows.Count,
"C").end(xlUp).Offset(0, -2))
'

The db also has a front end and backend and for some reason I can no
longer
open it (without using the shift key). It is set to automatically
link
the
front end to the backend or for you to point to the backend.

I get a message sying that:

File dbpath does not contain the required backenddb tables.

I haven't done anything to the backend to remove or change any
tables.


I know I'm vearing of topic but any ideas?

It still does this even if I manually link the tables


:

Don't know for sure that this is relevant, but what file extension
are
you
using? Access is picky in some cases.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I have some code that I am trying to switch to late binding. It is
code
that
saves the result of a query as an Excel file, then sends this file
as
an
attaqchment to an Outlook email then deletes the file.

It used to work before I made both changes to the the code dealing
with
Excel and Outlook application/object.

Now I get an error at the line:

DoCmd.OutputTo acOutputQuery, "process export qry", acFormatXLS,
CurrentProject.Path & "\" & pFilename, 0

saying that it cannot save the output file to the file name
specified.

As I mentioned this used to work before I made changes. It does
compile
though.

Any help will be appreciated.

The code is:


Function compexport()

Dim stto As String
Dim stcc As String
Dim stsubject As String
Dim ststartDate As String
Dim stenddate As String
Dim stfrmt As String
Dim stconame As String
Dim stmessage As String
Dim stnoto As String
Dim stnodate As String
Dim stnoconame As String
Dim stpermnumber As String
Dim ststartdateatt As String
Dim stenddateatt As String
Dim mPathAndFile As String, mFileNumber As Integer
Dim R As Recordset, mFieldNum As Integer
Dim mOutputString As String
Dim booDelimitFields As Boolean
Dim booIncludeFieldnames As Boolean
Dim mFieldDeli As String
Dim pbooIncludeFieldnames As String


Dim outApp As Object, objOutlook As Object, outmsg As Object,
olmailitem
As
Object


Dim oexcel As Object
'Dim osheet As Worksheet
Dim osheet As Object
'Dim rngToFormat As Range
Dim rngToFormat As Object



stconame = Nz([Forms]![export form]![coname], "none")
ststartDate = Nz([Forms]![export form]![begin], "none")
ststartdateatt = Replace(ststartDate, "/", "-")
stenddate = Nz([Forms]![export form]![end], "none")
stenddateatt = Replace(stenddate, "/", "-")
stpermnumber = Nz([Forms]![export form]![cmbpermnumber], "none")
stfrmt = DLookup("[Comp_format]", "export format settings")
stsubject = stconame & " " & "Compliance Sampling Data" & " " &
ststartDate
& " " & "to" & " " & stenddate
stto = Nz([Forms]![export form]![to], "none")
stcc = Nz([Forms]![export form]![cc], "")
stmessage = Nz([Forms]![export form]![Message], "")
stnoto = "You forgot to enter a Send To email address"
stnodate = "You must enter a beginning and ending date for the
data you
wish
to export"
stnoconame = "You forgot to enter a company name"

pbooIncludeFieldnames = "true"

If stto = "none" Then

MsgBox stnoto

Exit Function

Else

If stconame = "none" Then

MsgBox stnoconame

Exit Function

Else

If ststartDate = "none" Then

MsgBox stnodate

Exit Function

Else

If stendate = "none" Then

MsgBox stnodate

Exit Function


Else

If stfrmt = "acFormatXLS" Then

'DoCmd.SendObject acSendQuery, "compliance export qry",
acFormatXLS,
[stto],
[stcc], , stconame & " " & "Compliance Sampling Data" & " " &
ststartDate
& "
" & "to" & " " & stenddate, [stmessage], False



pFilename = stconame & " P" & stpermnumber & " " & ststartdateatt
& "
to "
&
stenddateatt & " Compliance Data.xls"



DoCmd.OutputTo acOutputQuery, "compliance export qry",
acFormatXLS,
CurrentProject.Path & "\" & pFilename, 0
'DoCmd.TransferSpreadsheet acExport, , "compliance export qry",
CurrentProject.Path & "\" & pFilename, True
'DoCmd.TransferSpreadsheet acImport,
3,"Employees","C:\Lotus\Newemps.wk3",
True, "A1:G12"

mPathAndFile = CurrentProject.Path & "\" & pFilename





'*************************


'Set oapp = CreateObject("Excel.Application")
Set oapp = CreateObject("Excel.Application")
Set oexcel = oapp.Workbooks.Open(Filename:=mPathAndFile)
Set osheet = oexcel.Worksheets("compliance export qry")

oapp.Visible = False
oapp.DisplayAlerts = False
osheet.Activate

With oexcel.Worksheets("compliance export qry").Columns

.Columns("A:S").AutoFit

End With


With oexcel.Worksheets("compliance export qry").PageSetup
.Zoom = False
.FitToPagesTall = 1000
.FitToPagesWide = 1
.Orientation = 2
.PrintGridlines = 0
.PrintTitleRows = "A1:S1"
'.LeftHeader =
.CenterHeader = "&14" & pFilename & "&10"
'.RightHeader =
.LeftFooter = "Report Created &D &T"
'.CenterFooter =
.RightFooter = "Page &P of &N"


.LeftMargin = oapp.InchesToPoints(0.25)
.RightMargin = oapp.InchesToPoints(0.25)
.TopMargin = oapp.InchesToPoints(0.75)
.BottomMargin = oapp.InchesToPoints(0.5)
.HeaderMargin = oapp.InchesToPoints(0.5)
.FooterMargin = oapp.InchesToPoints(0.25)

End With


With osheet.Range("A1:S1")
Set rngToFormat = osheet.Range(oexcel.Worksheets("compliance
export
Click to expand...
Click to expand...
Click to expand...
 
D

Douglas J. Steele

David W. Fenton said:
You realize that in this case, your file name is "ABC Co" and the
extension is " P0002 12-20-2007 to 12-21-2007 Compliance Data.xls"?
Click to expand...

I'm not sure that's true, David. At least, when I renamed a file to ABC Co.
P0002 12-20-2007 to 12-21-2007 Compliance Data.xls, Windows correctly
identified it as a Microsoft Excel Worksheet, which I've always assumed is
strictly based on the file extension.
 
D

David W. Fenton

I'm not sure that's true, David. At least, when I renamed a file
to ABC Co. P0002 12-20-2007 to 12-21-2007 Compliance Data.xls,
Windows correctly identified it as a Microsoft Excel Worksheet,
which I've always assumed is strictly based on the file extension.
Click to expand...

OK, that may be the case. But it's simply not a good idea (in my
opinion) to have periods anywhere in a file name, except to
designate the *real* extension.
 
D

Douglas J. Steele

David W. Fenton said:
OK, that may be the case. But it's simply not a good idea (in my
opinion) to have periods anywhere in a file name, except to
designate the *real* extension.
Click to expand...

No disagreement from me on that.
 
T

tmort

Thanks

I had most of it but had

oApp.Quit 'Close (and disconnect from) Excel
Set oexcel = Nothing
Set oApp = Nothing

and changed it to

aoApp.Application.Quit 'Close (and disconnect from)
Excel
Set oexcel = Nothing
Set oApp = Nothing


I'm confused about this else without an if though. I was in a bicycle bus
accident and have a splint on my right hand which turns using the keyboard
into an adventure. I figured I just managed to delete or move something.
However, as I'm looking at the code I can't find any missing ifs or extra
elses.

It's a little off topic but cre to take a look?


Douglas J. Steele said:
Assuming you've got something like

Set xlCurr = CreateObject("Excel.Application")
Set wkbCurr = xlCurr.Workbooks.Open(strFile)

then it's necessary to close the workbook using

wkbCurr.Close SaveChanges:=True

(or SaveChanges:=False if you don't want to save the changes), then quit the
application using

xlCurr.Application.Quit
Set xlCurr = Nothing

I've been told that xlCurr.Quit should be sufficient though.


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


tmort said:
Yes,

I'm using late binding for Excel as well. I didn't have options explicit.
I've done so now and found a number of items that needed to be
dimaensioned.
I also found some instances where I missed switching the xl constants to
the
numeric constants.

I have to look over the code some more because now when I compile I get an
Else without an If message.

Also, the output file continues to be being held open by Excel and there
is
an Excel process running that I have to turn off.



tmort said:
I think I did find the problem of the file not saving. It actually saves
the
file, but either because I had errors in ,y code and it didn't get to
kell
mpathfilename or because I have to stop Excel after I am done saving it.
I
noticed that I couldn't delete this file because it said it was open with
Excel. I didn't have Excel open but in the task manager I found a number
of
instances where it was open. I closed all of them and it would save the
file.

What command do I use to terminate Excel?

Also, I Made the change to -4162 and now the code gets hung up at:

With oapp.Selection.Borders(xlDiagonalDown)
.LineStyle = -4142
End With
With oapp.Selection.Borders(xlDiagonalUp)
.LineStyle = -4142
End With

I'll have to look into the other error more.

:

Periods in the file name shouldn't matter.

You mention that you're using Late Binding for Outlook. Are you also
using
Late Binding for Excel? If so, have you defined the value of xlUp?
(it's -4162)

As well, unless that line is inside a With construct, I think it should
be

Set rngToFormat = osheet.Range(oexcel.Worksheets("compliance export
qry").Range("S1"), osheet.Cells(osheet.Rows.Count,
"C").end(xlUp).Offset(0, -2))

or possibly

Set rngToFormat = osheet.Range(oexcel.Worksheets("compliance export
qry").Range("S1"), oexcel.Worksheets("compliance export
qry").Cells(osheet.Rows.Count, "C").end(xlUp).Offset(0, -2)) '

Is the error message you posted the exact error message? I somehow
doubt
that Access is generating that error message: it's likely code in the
application that's checking for some condition, and raising the message
itself.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


The file extension I am using is xls.

I think I found that problem, but, now I have two more.

I had some dummy data that put a "." in the file name where it didn't
belong. It was :

ABC Co. P0002 12-20-2007 to 12-21-2007 Compliance Data.xls

instead of

ABC Co. P0002 12-20-2007 to 12-21-2007 Compliance Data.xls

Now it chokes at:

Set rngToFormat = osheet.Range(oexcel.Worksheets("compliance export
qry").Range("S1"), .Cells(osheet.Rows.Count,
"C").end(xlUp).Offset(0, -2))
'

The db also has a front end and backend and for some reason I can no
longer
open it (without using the shift key). It is set to automatically
link
the
front end to the backend or for you to point to the backend.

I get a message sying that:

File dbpath does not contain the required backenddb tables.

I haven't done anything to the backend to remove or change any
tables.


I know I'm vearing of topic but any ideas?

It still does this even if I manually link the tables


:

Don't know for sure that this is relevant, but what file extension
are
you
using? Access is picky in some cases.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I have some code that I am trying to switch to late binding. It is
code
that
saves the result of a query as an Excel file, then sends this file
as
an
attaqchment to an Outlook email then deletes the file.

It used to work before I made both changes to the the code dealing
with
Excel and Outlook application/object.

Now I get an error at the line:

DoCmd.OutputTo acOutputQuery, "process export qry", acFormatXLS,
CurrentProject.Path & "\" & pFilename, 0

saying that it cannot save the output file to the file name
specified.

As I mentioned this used to work before I made changes. It does
compile
though.

Any help will be appreciated.

The code is:


Function compexport()

Dim stto As String
Dim stcc As String
Dim stsubject As String
Dim ststartDate As String
Dim stenddate As String
Dim stfrmt As String
Dim stconame As String
Dim stmessage As String
Dim stnoto As String
Dim stnodate As String
Dim stnoconame As String
Dim stpermnumber As String
Dim ststartdateatt As String
Dim stenddateatt As String
Dim mPathAndFile As String, mFileNumber As Integer
Dim R As Recordset, mFieldNum As Integer
Dim mOutputString As String
Dim booDelimitFields As Boolean
Dim booIncludeFieldnames As Boolean
Dim mFieldDeli As String
Dim pbooIncludeFieldnames As String


Dim outApp As Object, objOutlook As Object, outmsg As Object,
olmailitem
As
Object


Dim oexcel As Object
'Dim osheet As Worksheet
Dim osheet As Object
'Dim rngToFormat As Range
Dim rngToFormat As Object



stconame = Nz([Forms]![export form]![coname], "none")
ststartDate = Nz([Forms]![export form]![begin], "none")
ststartdateatt = Replace(ststartDate, "/", "-")
stenddate = Nz([Forms]![export form]![end], "none")
stenddateatt = Replace(stenddate, "/", "-")
stpermnumber = Nz([Forms]![export form]![cmbpermnumber], "none")
stfrmt = DLookup("[Comp_format]", "export format settings")
stsubject = stconame & " " & "Compliance Sampling Data" & " " &
ststartDate
& " " & "to" & " " & stenddate
stto = Nz([Forms]![export form]![to], "none")
stcc = Nz([Forms]![export form]![cc], "")
stmessage = Nz([Forms]![export form]![Message], "")
stnoto = "You forgot to enter a Send To email address"
stnodate = "You must enter a beginning and ending date for the
data you
wish
to export"
stnoconame = "You forgot to enter a company name"

pbooIncludeFieldnames = "true"

If stto = "none" Then

MsgBox stnoto

Exit Function

Else

If stconame = "none" Then

MsgBox stnoconame

Exit Function

Else

If ststartDate = "none" Then

MsgBox stnodate

Exit Function

Else

If stendate = "none" Then

MsgBox stnodate

Exit Function


Else

If stfrmt = "acFormatXLS" Then

'DoCmd.SendObject acSendQuery, "compliance export qry",
acFormatXLS,
[stto],
[stcc], , stconame & " " & "Compliance Sampling Data" & " " &
ststartDate
& "
" & "to" & " " & stenddate, [stmessage], False



pFilename = stconame & " P" & stpermnumber & " " & ststartdateatt
& "
to "
Click to expand...
Click to expand...
Click to expand...
 
D

Douglas J. Steele

Sorry, there's a little too much to this thread for me to be able to
determine where the "else without an if" to which you're referring exists.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Else Without If 8
Excel Automation - Gridlines 3
Excel hangs. Why? 1
Late binding error - running Ms Access Report in VB.Net 1
Convert export calendar to Outlook to late binding 0
Emailing an Attachment - Code 1
Excel Remains Open until Access is Closed... 2
add a chart in a Add-In 1

Top