excelvba打開指定文件夾

❶ vba怎樣打開指定路徑的excel文件

打開文件如下：Workbooks.Open ("C:\對賬單\對帳單.xlsx")這里你可以把「C:\對賬單\」換成你的絕對路徑，你可以用：a = ThisWorkbook.Patha = ThisWorkbook.PathWorkbooks.Open ("" & a & "&" \ "&對帳單.xlsx")來獲取你當前表的絕對路徑，然後再打開，因為不知道你的具體情況，如果有問題可以hai我 如果當前文件夾下還有A文件夾可以用代碼：Workbooks.Open ("" & a & "&" \ A\"&b.xlsx")Microsoft Excel是Microsoft為使用Windows和Apple Macintosh操作系統的電腦編寫的一款電子表格軟體。直觀的界面、出色的計算功能和圖表工具，再加上成功的市場營銷，使Excel成為最流行的個人計算機數據處理軟體。在1993年，作為Microsoft Office的組件發布了5.0版之後，Excel就開始成為所適用操作平台上的電子製表軟體的霸主。

❷ EXCEL vba 讀取指定文件夾的名字和循環打開文件夾

'------------------------------------------------------------------------------

'

'FormCode

'

'------------------------------------------------------------------------------

OptionExplicit

PrivaterowAsInteger,colAsInteger

PrivateSubCloseWindows_Click()

IfTextStartRow.Text=""ThenTextStartRow=0

IfTextStartCol=""ThenTextStartCol=0

IfTextPath=""ThenTextPath="D:"

CloseMyDialogTextStartRow,TextStartCol

EndSub

PrivateSubGetDir_Click()

IfTextStartRow.Text=""ThenTextStartRow=0

IfTextStartCol=""ThenTextStartCol=0

IfTextPath=""Then

TextPath="D:"

ElseIfRight(TextPath,1)<>""Then

TextPath=TextPath&""

EndIf

doGetDirTextPath,Val(TextStartRow),Val(TextStartCol)

EndSub

PrivateSubShowWindows_Click()

IfTextStartRow.Text=""ThenTextStartRow=0

IfTextStartCol=""ThenTextStartCol=0

IfTextPath=""ThenTextPath="D:"

ShowMyDialogApplication.hWnd,TextStartRow,TextStartCol

EndSub

上面是Form上面的

OptionExplicit

DimMyFile,Mypath,MyName

Dimi%,j%

DimDirPath()AsString

SubGetDir(ByValMypathAsString,rowAsInteger,colAsInteger)

'顯示C:目錄下的名稱。

'MyPath="d:電大"'指定路徑。

MyName=Dir(Mypath,vbDirectory)'找尋第一項。

DoWhileMyName<>""'開始循環。

'跳過當前的目錄及上層目錄。

IfMyName<>"."AndMyName<>".."Then

'使用位比較來確定MyName代表一目錄。

If(GetAttr(Mypath&MyName)AndvbDirectory)=vbDirectoryThen

Cells(row+i,col)=Mypath&MyName'如果它是一個目錄，將其名稱顯示出來。

ReDimPreserveDirPath(i)

DirPath(i)=Mypath&MyName&""

i=i+1

EndIf

EndIf

MyName=Dir'查找下一個目錄。

Loop

EndSub

PublicSubdoGetDir(ByValTextPath$,ByValTextStartRow%,ByValTextStartCol%)

j=1

i=1

Mypath=TextPath

GetDirMypath,TextStartRow,TextStartCol

Forj=1Toi-1

GetDirDirPath(j),TextStartRow,TextStartCol

Next

EndSub

'endcode---------------------------------------------------

OptionExplicit

PublicConstOFN_ALLOWMULTISELECTAsLong=&H200

PublicConstOFN_CREATEPROMPTAsLong=&H2000

PublicConstOFN_ENABLEHOOKAsLong=&H20

PublicConstOFN_ENABLETEMPLATEAsLong=&H40

PublicConstOFN_ENABLETEMPLATEHANDLEAsLong=&H80

PublicConstOFN_EXPLORERAsLong=&H80000

PublicConstOFN_EXTENSIONDIFFERENTAsLong=&H400

PublicConstOFN_FILEMUSTEXISTAsLong=&H1000

PublicConstOFN_HIDEREADONLYAsLong=&H4

PublicConstOFN_LONGNAMESAsLong=&H200000

PublicConstOFN_NOCHANGEDIRAsLong=&H8

PublicConstOFN_NODEREFERENCELINKSAsLong=&H100000

PublicConstOFN_NOLONGNAMESAsLong=&H40000

PublicConstOFN_NONETWORKBUTTONAsLong=&H20000

PublicConstOFN_NOREADONLYRETURNAsLong=&H8000&'*seecomments

PublicConstOFN_NOTESTFILECREATEAsLong=&H10000

PublicConstOFN_NOVALIDATEAsLong=&H100

PublicConstOFN_OVERWRITEPROMPTAsLong=&H2

PublicConstOFN_PATHMUSTEXISTAsLong=&H800

PublicConstOFN_READONLYAsLong=&H1

PublicConstOFN_SHAREAWAREAsLong=&H4000

PublicConstOFN_SHAREFALLTHROUGHAsLong=2

PublicConstOFN_SHAREWARNAsLong=0

PublicConstOFN_SHARENOWARNAsLong=1

PublicConstOFN_SHOWHELPAsLong=&H10

PublicConstOFS_MAXPATHNAMEAsLong=260

PublicConstOFS_FILE_OPEN_FLAGS=OFN_EXPLORER_

OrOFN_LONGNAMES_

OrOFN_CREATEPROMPT_

OrOFN_NODEREFERENCELINKS

PublicConstOFS_FILE_SAVE_FLAGS=OFN_EXPLORER_

OrOFN_LONGNAMES_

OrOFN_OVERWRITEPROMPT_

OrOFN_HIDEREADONLY

PublicTypeOPENFILENAME

nStructSizeAsLong

hWndOwnerAsLong

hInstanceAsLong

sFilterAsString

sCustomFilterAsString

nMaxCustFilterAsLong

nFilterIndexAsLong

sFileAsString

nMaxFileAsLong

sFileTitleAsString

nMaxTitleAsLong

sInitialDirAsString

sDialogTitleAsString

flagsAsLong

nFileOffsetAsInteger

nFileExtensionAsInteger

sDefFileExtAsString

nCustDataAsLong

fnHookAsLong

sTemplateNameAsString

EndType

PublicOFNAsOPENFILENAME

PublicConstWM_CLOSE=&H10

"comdlg32"_

Alias"GetOpenFileNameA"_

(pOpenfilenameAsOPENFILENAME)AsLong

"comdlg32"_

Alias"GetSaveFileNameA"_

(pOpenfilenameAsOPENFILENAME)AsLong

"kernel32"_

Alias"GetShortPathNameA"_

(ByVallpszLongPathAsString,_

ByVallpszShortPathAsString,_

ByValcchBufferAsLong)AsLong

PublicConstWM_INITDIALOG=&H110

PrivateConstSW_SHOWNORMAL=1

PublicTypeRECT

LeftAsLong

TopAsLong

RightAsLong

BottomAsLong

EndType

"user32"_

(ByValhWndAsLong)AsLong

"user32"_

Alias"SetWindowTextA"_

(ByValhWndAsLong,_

ByVallpStringAsString)AsLong

"user32"_

(ByValhWndAsLong,_

ByValxAsLong,_

ByValyAsLong,_

ByValnWidthAsLong,_

ByValnHeightAsLong,_

ByValbRepaintAsLong)AsLong

"user32"_

(ByValhWndAsLong,_

lpRectAsRECT)AsLong

"user32"_

Alias"SendMessageA"_

(ByValhWndAsLong,_

ByValwMsgAsLong,_

ByValwParamAsLong,_

lParamAsAny)AsLong

"user32"_

Alias"FindWindowA"_

(ByVallpClassNameAsLong,_

ByVallpWindowNameAsString)AsLong

PublicFunctionFARPROC(ByValpfnAsLong)AsLong

FARPROC=pfn

EndFunction

PublicFunctionOFNHookProc(ByValhWndAsLong,_

ByValuMsgAsLong,_

ByValwParamAsLong,_

ByVallParamAsLong)AsLong

DimhwndParentAsLong

DimrcAsRECT

DimnewLeftAsLong

DimnewTopAsLong

DimdlgWidthAsLong

DimdlgHeightAsLong

DimscrWidthAsLong

DimscrHeightAsLong

SelectCaseuMsg

CaseWM_INITDIALOG

hwndParent=GetParent(hWnd)

IfhwndParent<>0Then

CallGetWindowRect(hwndParent,rc)

dlgWidth=rc.Right-rc.Left

dlgHeight=rc.Bottom-rc.Top

CallMoveWindow(hwndParent,newLeft,newTop,dlgWidth,dlgHeight,True)

OFNHookProc=1

EndIf

CaseElse:

EndSelect

EndFunction

PublicSubShowFolder(hWndAsLong,Mypath$)

DimsFiltersAsString

DimposAsLong

DimbuffAsString

DimsLongnameAsString

DimsShortnameAsString

WithOFN

.nStructSize=Len(OFN)

.hWndOwner=hWnd

.sFilter=sFilters

.nFilterIndex=2

.sFile=Space$(1024)&vbNullChar&vbNullChar

.nMaxFile=Len(.sFile)

.sDefFileExt="bas"&vbNullChar&vbNullChar

.sFileTitle=vbNullChar&Space$(512)&vbNullChar&vbNullChar

.nMaxTitle=Len(OFN.sFileTitle)

.sInitialDir=Mypath&vbNullChar&vbNullChar

.sDialogTitle=Mypath&vbNullChar&vbNullChar

.flags=OFS_FILE_OPEN_FLAGSOr_

OFN_ALLOWMULTISELECTOr_

OFN_EXPLOREROr_

OFN_ENABLEHOOK

.fnHook=FARPROC(AddressOfOFNHookProc)

EndWith

GetOpenFileNameOFN

EndSub

PublicSubCloseFolder(MypathAsString)

DimhWndAsLong

hWnd=FindWindow(0,Mypath)

CallSendMessage(hWnd,WM_CLOSE,0&,ByVal0&)

EndSub

PublicSubShowMyDialog(MyhWndAsLong,TextStartRowAsInteger,TextStartColAsInteger)

Dimrow,col

Dimi

DimhWndAsLong

hWnd=MyhWnd

i=1:row=TextStartRow:col=TextStartCol

DoWhileCells(i+row,col)<>""

Shell"C:Windowsexplorer.exe"&Cells(i+row,col)

'ShowFolderhWnd,Cells(i+row,col)

'hWnd=FindWindow(0,Cells(i+row,col))

i=i+1

Loop

EndSub

PublicSubCloseMyDialog(TextStartRowAsInteger,TextStartColAsInteger)

Dimrow,col

Dimi

i=1:row=TextStartRow:col=TextStartCol

DoWhileCells(i+row,col)<>""

CloseFolderpathToName(Cells(i+row,col))

i=i+1

Loop

EndSub

PrivateFunctionpathToName(Mypath$)AsString

Dimstr()AsString

str=Split(Mypath,"")

pathToName=str(UBound(str))

EndFunction

❸ 如何在Excel中用VBA打開指定的文件夾

打開E盤
Shell "explorer.exe " & "E:\", vbNormalFocus

❹ excel運用VBA打開文件夾並搜索打開文件：

wb=range("B1")&""&range("C5")&".xls"
workbooks.open(wb)

❺ EXCEL如何使用VBA打開相對路徑下的文件或文件夾

打開文件如下：

Workbooks.Open ("C:對賬單對帳單.xlsx")

這里你可以把「C:對賬單」換成你的絕對路徑，

你可以用：a = ThisWorkbook.Path

a = ThisWorkbook.Path

Workbooks.Open ("" & a & "&" "&對帳單.xlsx")

來獲取你當前表的絕對路徑，然後再打開，因為不知道你的具體情況，如果有問題可以hai我

如果當前文件夾下還有A文件夾可以用代碼：

Workbooks.Open ("" & a & "&" A"&b.xlsx")

❻ 用EXCEL VBA獲取指定目錄下的文件名(包括文件夾名)

1.點開始菜單，就能看到上面的界面，選擇「運行」！點了運行程序後，在裡面輸入「cmd」點擊確定會進入命令提示符串口。

❼ Excel 如何使用VBA打開指定路徑下的文件夾

將當前文件夾設為默認即可。
在你的語句前面加上下面兩句：
ChDir ThisWorkbook.Path
ChDrive ThisWorkbook.Path

❽ EXCEL2010 vba 循環打開某些文件夾下的excel文件

就是遍歷文件夾嘛,關鍵代碼如下
在工程-引用里添加 microsoft scripting run time
然後用下面的兩個函數遞歸遍歷 就可以了
sub main()
Dim objFSO As Object
Dim objTemplateFolder As Object
Set objFSO = New Scripting.FileSystemObject
Set objTemplateFolder = objFSO.GetFolder(「D:\」)
getFiles objTemplateFolder
end sub

Sub getFiles(ByRef theFolder As Object)
Dim folder As Object
Dim c As New Scripting.FileSystemObject
『此處執行你的操作：打開關閉文件夾，取名字等
For Each folder In theFolder.subFolders
getFiles folder 』遞歸遍歷子文件夾
Next
End Sub

❾ 關於Excel VBA打開文件夾

Sub openmyexcel()
Dim fs, f, i, j, k, f1, f2
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.getfolder("f:\hk")
For Each i In f.subfolders
If InStr(i.Name, "102471") <> 0 Then

Set f1 = fs.getfolder(i.Path)
For Each j In f1.subfolders
If InStr(j.Name, "prem") <> 0 Then

Set f2 = fs.getfolder(j.Path)
For Each k In f2.Files
If InStr(k.Name, "00") <> 0 Then
Workbooks.Open (k.Path)
Set f2 = Nothing
Exit For
End If
Next
Set f1 = Nothing
Exit For
End If
Next
Set f = Nothing
Exit For
End If
Next
End Sub
————————————————————————————
在thisworkbook中執行

❿ EXCEL如何使用VBA打開相對路徑下的文件或文件夾急！！！

打開文件如下：

Workbooks.Open ("C:對賬單對帳單.xlsx")

這里你可以把「C:對賬單」換成你的絕對路徑，

你可以用：a = ThisWorkbook.Path

a = ThisWorkbook.Path

Workbooks.Open ("" & a & "&" "&對帳單.xlsx")

來獲取你當前表的絕對路徑，然後再打開，因為不知道你的具體情況，如果有問題可以hai我

如果當前文件夾下還有A文件夾可以用代碼：

Workbooks.Open ("" & a & "&" A"&b.xlsx")