Compile error: Method or data member not found.
Code
Function Print_TestPlan(strTestPlanName As String)
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' This function takes the name of the test plan file, opens it in Excel and '
' then prints selected worksheets. '
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim wb As Workbook
Dim ws As Worksheet
' Turn of alerts momentarily so something doesn't freak out
Application.DisplayAlerts = True
' Open the workbook
Set wb = Application.Workbooks.Open(strTestPlanName)
' Print the first worksheet
' this is fucked
Set ws = wb.Worksheets(1)
' this too
ws.PrintOut
' Print the second worksheet
Set ws = wb.Worksheets(3)
With ws.PageSetup
.Orientation = xlLandscape
.PaperSize = xlPaperA3
End With
ws.PrintOut
' Close the document; don't save
wb.Close SaveChanges:=False
' Turn alerts back on
Application.DisplayAlerts = False
' Clean up
Set wb = Nothing
Set ws = Nothing
End Function
Function Print_Survey(strSurveyName As String, boolIsExcelFile As Boolean)
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' This function takes the name of the survey file and whether or not it's '
' an Excel file. If it is an Excel file it goes through and prints a set '
' of selected pages; if not it just prints the whole document. '
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' If it's an Excel file we treat it differently
If boolIsExcelFile = True Then
Dim wb As Workbook
Dim ws As Worksheet
Dim lngOrientation As Long
Dim lngPaperSize As Long
Dim boolPrintMe As Boolean
' Open the Survey workbook
Set wb = Workbooks.Open(strSurveyName)
' Parse through each worksheet
For Each ws In wb.Worksheets
' By default we want to print
boolPrintMe = True
' Look at the worksheet name; if it's one of the ones we want to print
' then set the orientation and paper size; otherwise do nothing
Select Case ws.Name
Case Is = "Title Sht "
lngOrientation = xlPortrait
lngPaperSize = xlPaperA4
Case Is = "Audit 1"
lngOrientation = xlPortrait
lngPaperSize = xlPaperA4
Case Is = "Audit 2"
lngOrientation = xlPortrait
lngPaperSize = xlPaperA4
Case Is = "Audit 3"
lngOrientation = xlPortrait
lngPaperSize = xlPaperA4
Case Is = "Audit 4"
lngOrientation = xlPortrait
lngPaperSize = xlPaperA4
Case Is = "Audit 5"
lngOrientation = xlPortrait
lngPaperSize = xlPaperA4
Case Is = "Provided information"
lngOrientation = xlPortrait
lngPaperSize = xlPaperA4
Case Is = "SES CDC"
lngOrientation = xlLandscape
lngPaperSize = xlPaperA4
Case Else
' If it's not one of the above then don't print
boolPrintMe = False
End Select
' If we want to print we just need to set the info
If boolPrintMe = True Then
' Set the orientation and paper size
With ws.PageSetup
.Orientation = lngOrientation
.PaperSize = lngPaperSize
End With
' Print it
ws.PrintOut
End If
' Go to the next worksheet
Next ws
' Close the survey but don't save it
wb.Close SaveChanges:=False
Else
' If it's anything else except an Excel document, we just print
ShellExecute 0, "Print", strSurveyName, vbNullString, "", SW_SHOWNORMAL
End If
End FunctionFunction Print_TestPlan(strTestPlanName As String) --- this is highlighted yellow
Application.DisplayAlerts = True --- this is highlighted blue
Ive messed about referencing the DAO object library instead of the access one. Nothing seems to resolve the issue.
The code I have shown does work for the survey but not the testplan. I have no idea what i can do to fix this now as I've looked at everything I can think of/have come accross searching the internet.
Could someone help me here?
This code does work "as is" when run through excel.
Thanks in advance
Andy