還是一些Excel VBA的問題

2009-01-25 6:40 am
煩請各問優秀的大大們幫我解決下列問題,謝謝!
1.如果我要在按鈕按下後開啟另一個xls檔,我知道如果檔案是放在同一層資料夾可以寫『\庫存管理系統.xls』,但假設欲開啟的的檔案是放在(d:/aaa/),但要打開他的工作表放在(d:/aaa/111/),那請問一下大大要如何寫捷徑呢?
2.我要寫一個按鈕可以直接啟動印表機並選定好固定的列印範圍,如(A1:D20)?
3.可以用VBA的語法將xls的檔案圖檔更改嗎?
4.如何用VBA的語法寫出點下打開檔案後須先輸入帳號及密碼才能登入(用其中一張工作表建立帳號及密碼的資料庫)
更新1:

在此非常感謝Calvin大大的仔細解說,問題1)及2)已經解決,不過問題3)及4)我還是看不太懂,希望還有大大幫我補充或是翻譯的,謝謝!

回答 (2)

2009-01-29 12:21 am
✔ 最佳答案
I assume you know how to create & program buttons & here are your answers:

1) Workbooks.Open Filename:="d:/aaa/aaa/xxx.xls"

2) Assume your print area is A1:D7
ActiveSheet.PageSetup.PrintArea = "$A$1:$D$7"
ActiveWindow.SelectedSheets.PrintOut Copies:=1

Note: the following line allows you to cancel the print area
ActiveSheet.PageSetup.PrintArea = ""

3) (not clear about your question).....this code will delete the first picture in your curren worksheet, import a new picture and then move it to the same location

Dim Lft as long, Tp as long
ActiveSheet.Shapes(ActiveSheet.Shapes.count).select
Lft = Selection.ShapeRange.left
Tp = Selection.ShapeRange.top

Selection.Delete

ActiveSheet.Pictures.Insert("d:\abc.jpg").Select
Selection.ShapeRange.left = Lft
Selection.ShapeRange.top = Tp

4) Use auto_open to run commands when excel is opened.
Sub Auto_Open()
If "123" = InputBox("Pls input password", "Login") Then
sheet("sheetx").select
else
activeworkbook.Close
End If
End Sub

note: you should add similar code to the protected worksheet and will ask fro p/w everytime it is being selected

Calvin Lee

2009-01-29 00:38:49 補充:
4) missed a 's' in my example...

Sub Auto_Open()
If "123" = InputBox("Pls input password", "Login") Then
sheets("sheetx").select
else
activeworkbook.Close
End If
End Sub
2009-01-30 9:45 pm
我的意見:

1) Workbooks.Open Filename:="d:\aaa\aaa\xxx.xls"

3) 題意模糊, 若是指 XLS 檔裡面插入的圖片, 必須刪除重新插入, 不能直接更改

4) auto_open() 沒用, 使用者只要關閉 Excel 的巨集功能就可以避過


收錄日期: 2021-05-01 16:26:19
原文連結 [永久失效]:
https://hk.answers.yahoo.com/question/index?qid=20090124000010KK08493

檢視 Wayback Machine 備份