UtterAccess Discussion Forums > Variable Not Defined Using Saveas Fileformat:=

Full Version: Variable Not Defined Using Saveas Fileformat:=

UtterAccess Discussion Forums > Microsoft® Office > Microsoft Excel

NineIron

Apr 24 2012, 07:00 AM

I'm getting an error, Variable Not Defined, when running this code. xlHtml is highlighted during Compile.
Any thoughts?

CODE

Public Sub OpenSafetyCrossAnyMonth()
Dim objXL As Object
Dim strPath As String
Dim strMonth As String

    strPath = "S:\WRIR\Reports\Safety\SafetyCross_AnyMonth.xlsm"
    strMonth = Forms!frmReports.cboAnyMonth.Column(1) & "_" & Forms!frmReports.txtYearAnyMonth

    On Error Resume Next
    Set objXL = GetObject(, "Excel.Application")

    If TypeName(objXL) = "Nothing" Then
        Set objXL = CreateObject("Excel.Application")
    End If

    With objXL
        .Visible = True
        .Workbooks.Open strPath
        .ActiveWorkbook.Application.DisplayAlerts = False
        .ActiveWorkbook.SaveAs FileName:="\\bh-intranet.brocktonhospital.internal\E$\Inetpub\wwwroot\intraApp\Intranet\xExternals\safetycross\SafetyCross_Web.htm", FileFormat:=xlHtml

        .Quit

    End With

    objXL.Close
    Set objXL = Nothing

End Sub

DanielPineault

Apr 24 2012, 07:11 AM

It would look to me as this is an Access procedure which calls Excel. Please correct me if I am wrong.

As such, Excel's constants are not known of Access, so you need to explicitly declare them when you require them, like:

CODE

Public Sub OpenSafetyCrossAnyMonth()
    Dim objXL           As Object
    Dim strPath         As String
    Dim strMonth        As String
    Const xlHtml = 44

    strPath = "S:\WRIR\Reports\Safety\SafetyCross_AnyMonth.xlsm"
    strMonth = Forms!frmReports.cboAnyMonth.Column(1) & "_" & Forms!frmReports.txtYearAnyMonth

    On Error Resume Next
    Set objXL = GetObject(, "Excel.Application")

    If TypeName(objXL) = "Nothing" Then
        Set objXL = CreateObject("Excel.Application")
    End If

    With objXL
        .Visible = True
        .Workbooks.Open strPath
        .ActiveWorkbook.Application.DisplayAlerts = False
        .ActiveWorkbook.SaveAs Filename:="[url="file://\bh-intranet.brocktonhospital.internalE$InetpubwwwrootintraAppIntranetxExternalssafetycrossSafetyCross_Web.htm"]\\bh-intranet.brocktonhospital.internal\E$\Inetpub\wwwroot\intraApp\Intranet\xExternals\safetycross\SafetyCross_Web.htm[/url]", FileFormat:=xlHtml
        .Quit
    End With

    objXL.Close
    Set objXL = Nothing
End Sub

arnelgp

Apr 24 2012, 07:14 AM

will FileFormat:=44 will work for you (xlHtml = 44).

NineIron

Apr 24 2012, 07:22 AM

This line .ActiveWorkbook.SaveAs Filename:="\\bh-intranet.brocktonhospital.internal\E$\Inetpub\wwwroot\intraApp\Intranet\xExternals\safetycross\SafetyCross_Web.htm", FileFormat:=xlHtml

comes out all red. I'm not sure where the issue is.

arnelgp

Apr 24 2012, 07:38 AM

Have you checked if a folder "SafetyCross Web" is already created on that folder.

NineIron

Apr 24 2012, 07:39 AM

Yes.

arnelgp

Apr 24 2012, 07:41 AM

Either you delete the folder or you enable this:

.ActiveWorkbook.Application.DisplayAlerts = False

to:

.ActiveWorkbook.Application.DisplayAlerts = True

To see what's going on with Excel.

norie

Apr 24 2012, 09:17 AM

If it's turned red it's a syntax error in the code.

Is the line all one line or is it actually spread over more than one line?

If it is on more than one line you'll either need to split the filename and path into several variables or use the continuation character, _ the underscore.

CODE

strPath = "\\bh-intranet.brocktonhospital.internal\"& _
"E$\Inetpub\wwwroot\intraApp\Intranet\" & _
"xExternals\safetycross\"

ActiveWorkbook.SaveAs Filename:=strPath & strFileName, FileFormat:=xlHtml

ipisors

Apr 24 2012, 09:29 AM

NineIron:

Late binding is fine, but I suggest early binding for trouble shooting. It gives you the benefit of Intellisense which helps guide you. For example, in that With statement. If you are using Early Binding (check a reference to a library and use its members, instead of creating Objects like that in Late Binding), inside the with statement, you will know as soon as you type a period you will know what pops up and what your options are. You also know what the exact syntax should be which is pretty sweet and would have helped in a problem like this, early on.

I think many developers use early binding for developing/testing, then late binding (if that's their preference) ultimately for distribution.

But when it comes time to troubleshoot a problem, it's much more difficult to do so when everything has been switched to objects and late binding.

Just my opinion.

ipisors

Apr 24 2012, 09:32 AM

Agree with Norie, if the line turns red that's a syntax error, not an issue of the folder already existing. : )

Folder already existing might possibly cause a run time error.

norie

Apr 24 2012, 11:37 AM

Oops, forgot the filename - kind of important I suppose.

CODE

strFileName = "SafetyCross_Web.htm"

strPath = "\\bh-intranet.brocktonhospital.internal\" & _
"E$\Inetpub\wwwroot\intraApp\Intranet\" & _
"xExternals\safetycross\"

ActiveWorkbook.SaveAs Filename:=strPath & strFileName, FileFormat:=xlHtml

This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.