Close workbook without Clipboard prompt?

Close workbook without Clipboard prompt?

Post by Phyllis She » Sat, 12 Feb 2000 04:00:00



When I wrote macros to input some columns data from Excel file (workbook 1)
to another workbook (workbook 2), I used copy and paste method. I tried to
close workbook 1 silently, but the Clipboard prompt window always comes out:
-----
Microsoft Excel:
Save Large Clipboard from 'workbook1'?
Yes or No or Cancel or Help.
-----

How can I close the workbook without clipboard prompt? Is there a way I
write code to clear clipboard first then close the workbook?

Thank you for your help.

 
 
 

Close workbook without Clipboard prompt?

Post by Clark Huffma » Sun, 13 Feb 2000 04:00:00


There is probably some way to clear the application clipboard directly -
hopefully
one of the MVP's or some other kind sole will note it.

In the mean time, setting displayalerts=false should get you around the
problem.
Another workaround that *will* work is to select a single cell and copy it.
The
application clipboard is so small that it's contents are are automatically
transferred
to the windows clipboard w/o the save prompt.

hth


>When I wrote macros to input some columns data from Excel file (workbook 1)
>to another workbook (workbook 2), I used copy and paste method. I tried to
>close workbook 1 silently, but the Clipboard prompt window always comes
out:
>-----
>Microsoft Excel:
>Save Large Clipboard from 'workbook1'?
>Yes or No or Cancel or Help.
>-----

>How can I close the workbook without clipboard prompt? Is there a way I
>write code to clear clipboard first then close the workbook?

>Thank you for your help.


 
 
 

Close workbook without Clipboard prompt?

Post by RUSS BARTOL » Mon, 14 Feb 2000 04:00:00


You can try:

Application.DisplayAlerts = False

Russ


>When I wrote macros to input some columns data from Excel file (workbook 1)
>to another workbook (workbook 2), I used copy and paste method. I tried to
>close workbook 1 silently, but the Clipboard prompt window always comes
out:
>-----
>Microsoft Excel:
>Save Large Clipboard from 'workbook1'?
>Yes or No or Cancel or Help.
>-----

>How can I close the workbook without clipboard prompt? Is there a way I
>write code to clear clipboard first then close the workbook?

>Thank you for your help.

 
 
 

Close workbook without Clipboard prompt?

Post by Henk Krijne » Thu, 17 Feb 2000 04:00:00



> There is probably some way to clear the application clipboard directly -
> hopefully
> one of the MVP's or some other kind sole will note it.

> In the mean time, setting displayalerts=false should get you around the
> problem.
> Another workaround that *will* work is to select a single cell and copy it.
> The
> application clipboard is so small that it's contents are are automatically
> transferred
> to the windows clipboard w/o the save prompt.

> hth


> >When I wrote macros to input some columns data from Excel file (workbook 1)
> >to another workbook (workbook 2), I used copy and paste method. I tried to
> >close workbook 1 silently, but the Clipboard prompt window always comes
> out:
> >-----
> >Microsoft Excel:
> >Save Large Clipboard from 'workbook1'?
> >Yes or No or Cancel or Help.
> >-----

> >How can I close the workbook without clipboard prompt? Is there a way I
> >write code to clear clipboard first then close the workbook?

> >Thank you for your help.

Run Inputx
You input information from an input file
This is to much info but see in first macro between spaces for you
answer

Sub Inputx()
Application.ScreenUpdating = False
Ri = 1
Ki = 1

WorkFileName = ActiveWorkbook.Name   'is destination file
sht = ActiveSheet.Name
ClearInputsheet
On Error GoTo ErrorHandler
Path = Sheets(sht).Cells(1, 1).Value
flnm = Sheets(sht).Cells(1, 2).Value
Pathflnm = Path & flnm
FileValidate Pathflnm:=Pathflnm
OpenBlok Pathflnm:=(Pathflnm)
InputFileName = ActiveWorkbook.Name
ZoekFullRange Rm:=Ri, Km:=Ki            's source

Selection.Copy
ActiveWindow.WindowState = xlMinimized
Windows(WorkFileName).Activate
Sheets("Input").Select
ActiveWindow.WindowState = xlNormal
Cells(2, 1).Select
'Range("A2").Select                                                    
'Eerste Regel wordt vrijgelaten
ActiveSheet.Paste
Windows(InputFileName).Application.CutCopyMode = False
Windows(InputFileName).Close saveChanges:=False
Sheets("Input").Select

Cells(1, 3).Value = "Filename"
Cells(1, 4).Value = InputFileName
Cells(1, 1).Value = FileDateTime(InputFileName)
Application.CutCopyMode = False
Range("A1").Select
ActiveWindow.WindowState = xlMaximized
Application.ScreenUpdating = True
Exit Sub
ErrorHandler:
Beep
Select Case Err
Case Is = 75
MsgBox "Foutje Diskette is niet aanwezig", 0, "Directory klopt niet!"
Resume Next
Case Is = 76
Resume Next
Case Is = 1004
Case Is = 62
Resume Next
Case Is = 55
Close #1

Resume
Case Else
Error Err
End Select
End Sub

Sub ClearInputsheet()
On Error GoTo ErrorHandler
Sheets("Input").Select
Application.GoTo reference:="R1C1"
Cells.Select
Selection.ClearContents
Range("A1").Select
Exit Sub
ErrorHandler:
Select Case Err
Case Is = 1004
Sheets.Add.Name = "Input"
Case Is = 9
Sheets.Add.Name = "Input"
Case Else
Error Err
End Select
End Sub

Sub FileValidate(ByRef Pathflnm)
Dim szFilter, szTitle, szFile
szFilter = "De file opgegeven in Commando A1B1," & Pathflnm & " ,Alle
files (*.*),*.*,Lab files(*.lab),*.lab,Spectrale
files(*.SPR),*.spr,Ultrascan (*.ASC),*.asc,LabCH files(*.CIE),*.cie,XYZ
files (*.xyz),*.xyz,Alle files (*.*),*.*"
Pathflnm = Application.GetOpenFilename(FileFilter:=szFilter,
Title:=szTitle)
If Pathflnm = False Then
MsgBox Prompt:="U kunt in cel A1 van het Commandosheet het gewenste path
aangeven", Title:="U wilt terug?"
Sheets("Commando").Select

End If
End Sub

Sub ZoekFullRange(ByRef Rm, ByRef Km)
'Select all used cells in the source sheet
Set first = Cells(Rm, Km)
LastRow = ActiveSheet.UsedRange.Rows.Count
Application.ScreenUpdating = False
For R = LastRow To 1 Step -1
If Not Application.CountA(Rows(R)) = 0 Then GoTo kolommmen
Next R
kolommmen:
LastColumn = ActiveSheet.UsedRange.Columns.Count
For C = LastColumn To 1 Step -1
If Not Application.CountA(Columns(C)) = 0 Then GoTo EIND
Next C
EIND:
Rm = R
Km = C
Set secend = Cells(R + 1, C)
Range(first, secend).Select
End Sub

Sub OpenBlok(Pathflnm)
Workbooks.OpenText FileName:=Pathflnm, Origin:=xlWindows, _
StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, Semicolon _
:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _
:=Array(Array(1, 1))
End Sub