Log In
Register
Home
New Member
Power BI
January 12, 2022
Offline
Hi All,
I have a folder named ABC and a file called H.xlsm inside. There is a subfolder named ABCs. Inside the folder, there are some excel files. My current codes will go to the ABC folders. Open each file, run the query, and then close and save the file. But now I need to open file A, then B and the rest of the files. The order for the rest of the file doesn't matter.
Maybe I can rename the file names A_1, B_2, C_3, and D_4. So it can sort in ascending order. I know there is a function dir.
A.xlsm
B.xlsm
C.xlsm
D.xlsm
Sub RefreshABCs(Folder)
Dim File
Dim Worksheet
For Each File In Folder.Files
Workbooks.Open (File), False
Dim lCnt As Long
With ActiveWorkbook
For lCnt = 1 To .Connections.Count
If .Connections(lCnt).Type = xlConnectionTypeOLEDB Then
.Connections(lCnt).OLEDBConnection.BackgroundQuery = False
End If
Next lCnt
End With
ActiveWorkbook.RefreshAll
ActiveWorkbook.Save
ActiveWorkbook.Close
Next
End Sub
Thank you so much!
New Member
Power BI
January 12, 2022
Offline
I check Chatgpt
Sub LoopThroughFiles()
Dim myFile As String
Dim myDir As String
Dim i As Integer
' Specify the directory containing the files myDir = "C:\Your\File\Path\"
' Loop through the files in the directory i = 1
myFile = Dir(myDir & i & ".xlsx") Do While myFile <> ""
' Open the file and perform your operations here
Workbooks.Open (myDir & myFile)
' Add your code here to process the file
' Close the file Workbooks(myFile).Close SaveChanges:=False
' Move to the next file
i = i + 1 myFile = Dir(myDir & i & ".xlsx") Loop
End Sub
1 Guest(s)
