안녕하세요~

업무처리가 좀 복잡할 듯 보이는 상황인데,

질문의 요지는 크게 세 가지로 보입니다.

원드라이브로 작업하던 파워쿼리 원본(Source) 경로를

회사 내부 NAS 파일서버 경로로 효율적으로 수정하는 방법과

앞으로도 엑셀 + 파워쿼리 구조로 계속 운영해도 되는지에 대한 판단이 필요하고,

경로 변경 작업을 VBA로 자동화할 수 있는지 여부로 보입니다.

​

​

아래에서는

첫 번째, 가장 안전한 “조회 후 수동 변경 방식”과

두 번째, 검증된 후 사용할 수 있는 “VBA 일괄 변경 방식”

을 순서대로 정리 드리겠습니다.



물론, 현장에서 실제로 업무를 진행하시는 환경과

각자 가지고 계신 다양한 접근 방식에 따라

의견은 충분히 달라질 수 있다는 점을 전제로 하겠습니다.

​

일단,
먼저, NAS에 하나의 드라이브나 폴더에
기존 파일을 백업받으시고,
백업된 파일로 테스트를 진행하도록 합니다.

1.  아래 코딩으로
모든 파일의 파워쿼리 원본(Source) 경로를 수집합니다.

Option Explicit

'// ============================================
'// 폴더 내 여러 파일의 Power Query 원본 경로 수집
'// - 폴더 선택 후, 그 안의 모든 .xls* 파일 대상
'// - 각 파일의 모든 WorkbookQuery와 Source 경로 추출
'// - 결과는 이 통합문서의 "쿼리경로" 시트에 정리
'// ============================================
Public Sub 쿼리원본수집()
    On Error GoTo EH
    
    Dim ws As Worksheet
    Dim fPath As String
    Dim fName As String
    Dim wb As Workbook
    Dim qt As WorkbookQuery
    Dim r As Long
    
    Dim prevCalc As XlCalculation
    Dim prevUpd As Boolean, prevEvt As Boolean, prevAlert As Boolean
    
    '// ===== 보고서 시트 준비 =====
    On Error Resume Next
    Set ws = ThisWorkbook.Worksheets("쿼리경로")
    On Error GoTo EH
    
    If ws Is Nothing Then
        Set ws = ThisWorkbook.Worksheets.Add
        ws.Name = "쿼리경로"
    Else
        ws.Cells.Clear
    End If
    
    With ws
        .Range("A1:E1").Value = Array("파일명", "쿼리명", "원본경로(Source)", "로드시트", "로드테이블명")
        .Rows(1).Font.Bold = True
    End With
    
    r = 2
    
    '// ===== 옵션 저장 및 끄기 =====
    prevCalc = Application.Calculation
    prevUpd = Application.ScreenUpdating
    prevEvt = Application.EnableEvents
    prevAlert = Application.DisplayAlerts
    
    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.DisplayAlerts = False
    
    '// ===== 폴더 선택 =====
    fPath = 파일경로()
    If fPath = "" Then
        MsgBox "폴더 선택이 취소되었습니다.", vbInformation
        GoTo CleanExit
    End If
    
    If Right$(fPath, 1) <> "\" Then
        fPath = fPath & "\"
    End If
    
    '// ===== 폴더 내 엑셀파일 순회 =====
    fName = Dir(fPath & "*.xls*")
    
    Do While fName <> ""
        
        '// 이 통합문서(관리용)와 같으면 건너뛰기
        If fPath & fName <> ThisWorkbook.FullName Then
            
            Set wb = Workbooks.Open(Filename:=fPath & fName, ReadOnly:=True)
            
            '// Power Query가 있는 경우에만 처리
            If wb.Queries.Count > 0 Then
                Dim qFormula As String
                Dim srcPath As String
                Dim shLoad As String
                Dim tblLoad As String
                
                For Each qt In wb.Queries
                    qFormula = qt.Formula
                    srcPath = 원본소스경로추출(qFormula)
                    shLoad = ""
                    tblLoad = ""
                    
                    '// 쿼리가 어느 시트/테이블에 로드되는지 찾아보기
                    Call 쿼리시트테이블(wb, qt.Name, shLoad, tblLoad)
                    
                    ws.Cells(r, 1).Value = wb.Name
                    ws.Cells(r, 2).Value = qt.Name
                    ws.Cells(r, 3).Value = srcPath
                    ws.Cells(r, 4).Value = shLoad
                    ws.Cells(r, 5).Value = tblLoad
                    
                    r = r + 1
                Next qt
            End If
            
            wb.Close SaveChanges:=False
        End If
        
        fName = Dir()
    Loop
    
    ws.Columns("A:E").EntireColumn.AutoFit
    
    MsgBox "완료되었습니다." & vbCrLf & _
           "총 " & r - 2 & "개의 쿼리 정보가 수집되었습니다.", vbInformation
    
CleanExit:
    '// ===== 옵션 복구 =====
    Application.Calculation = prevCalc
    Application.ScreenUpdating = prevUpd
    Application.EnableEvents = prevEvt
    Application.DisplayAlerts = prevAlert
    Exit Sub
    
EH:
    MsgBox "오류 발생: " & Err.Description, vbExclamation
    Resume CleanExit
End Sub

'// ============================================
'// 폴더 선택용 함수 (FileDialog)
'// ============================================
Private Function 파일경로() As String
    Dim fd As FileDialog
    Dim sPath As String
    
    sPath = ""
    
    On Error GoTo EH
    Set fd = Application.FileDialog(msoFileDialogFolderPicker)
    
    With fd
        .Title = "파워쿼리 경로를 수집할 엑셀 파일이 들어있는 폴더를 선택하세요."
        .AllowMultiSelect = False
        If .Show = -1 Then
            sPath = .SelectedItems(1)
        End If
    End With
    
    파일경로 = sPath
    Exit Function
    
EH:
    MsgBox "폴더 선택 중 오류: " & Err.Description, vbExclamation
    파일경로 = ""
End Function
'// ============================================
'// M 코드에서 Source 경로 추출 (강화 버전)
'// 1) File.Contents / Folder.Files / SharePoint.Files / Web.Contents 우선
'// 2) 못 찾으면 ".xlsx" / ".xlsm" / ".xls" / ".csv" / ".txt" 등 확장자 기준으로 재탐색
'// ============================================
Private Function 원본소스경로추출(ByVal formulaText As String) As String
    Dim patterns As Variant
    Dim p As Variant
    Dim pos As Long
    Dim q1 As Long, q2 As Long
    Dim result As String
    
    result = ""
    
    '// 1차: 대표 함수 패턴 기준으로 경로 찾기
    patterns = Array("File.Contents(", _
                     "Folder.Files(", _
                     "SharePoint.Files(", _
                     "SharePoint.Contents(", _
                     "Web.Contents(")
    
    For Each p In patterns
        pos = InStr(1, formulaText, CStr(p), vbTextCompare)
        If pos > 0 Then
            q1 = InStr(pos, formulaText, """")      '// 해당 패턴 이후 첫 따옴표
            If q1 > 0 Then
                q2 = InStr(q1 + 1, formulaText, """")   '// 다음 따옴표
                If q2 > q1 Then
                    result = Mid$(formulaText, q1 + 1, q2 - q1 - 1)
                    Exit For
                End If
            End If
        End If
    Next p
    
    '// 2차: 그래도 못 찾으면 파일 확장자를 기준으로 탐색
    If result = "" Then
        Dim exts As Variant
        Dim e As Variant
        Dim extPos As Long
        
        exts = Array(".xlsx", ".xlsm", ".xlsb", ".xls", ".csv", ".txt")
        
        For Each e In exts
            extPos = InStr(1, formulaText, CStr(e) & """", vbTextCompare)
            If extPos > 0 Then
                '// ".xlsx"" 이 위치 기준으로 앞뒤 따옴표 찾기
                q1 = InStrRev(formulaText, """", extPos)
                q2 = InStr(extPos + 1, formulaText, """")
                
                If q1 > 0 And q2 > q1 Then
                    result = Mid$(formulaText, q1 + 1, q2 - q1 - 1)
                    Exit For
                End If
            End If
        Next e
    End If
    
    원본소스경로추출 = result
End Function
'// ============================================
'// 쿼리가 어느 시트/테이블에 로드되어 있는지 찾기
'// - ListObject.QueryTable.WorkbookConnection.Name 이
'//   보통 "Query - 쿼리명" 형태로 되어 있음
'// ============================================
Private Sub 쿼리시트테이블(ByVal wb As Workbook, _
                                     ByVal qName As String, _
                                     ByRef shName As String, _
                                     ByRef tblName As String)
    Dim ws As Worksheet
    Dim lo As ListObject
    Dim connName As String
    
    shName = ""
    tblName = ""
    
    On Error GoTo EH
    
    For Each ws In wb.Worksheets
        For Each lo In ws.ListObjects
            If Not lo.QueryTable Is Nothing Then
                connName = ""
                On Error Resume Next
                connName = lo.QueryTable.WorkbookConnection.Name
                On Error GoTo EH
                
                '// "Query - 쿼리명" 안에 쿼리명이 들어있는지 체크
                If InStr(1, connName, qName, vbTextCompare) > 0 Then
                    shName = ws.Name
                    tblName = lo.Name
                    Exit Sub
                End If
            End If
        Next lo
    Next ws
    
    Exit Sub
    
EH:
    '// 로드 정보를 못 찾거나 에러가 나면 그냥 빈 값으로 둔다.
    shName = shName
    tblName = tblName
End Sub

​

​

위 코딩을 실행하여,
새롭게 생성돤 쿼리경로 시트에서 원본경로
즉, 사용자경로(C:\Users...OneDrive\문서...)를 찾아 NAS 경로(예: \\NAS-SERVER\근태자료)로 개별 수정하는데, 가장 안전하고 확실한 방법입니다.

2. 아래는 VBA로 일괄 수정하는 방법입니다.

​

Option Explicit

'// ===== 경로 상수 (환경에 맞게 수정하세요) =====
Private Const SRC_BASE As String = "C:\Users\Administrator\OneDrive\"          '// 원래 원드라이브 루트
Private Const NAS_BASE As String = "\\NAS-SERVER\근태자료\"        '// 바꾸고 싶은 NAS 루트

'// ============================================
'// 폴더 내 여러 엑셀파일의 Power Query 원본 경로를
'// SRC_BASE → NAS_BASE 로 일괄 변경
'// - 각 쿼리의 M 코드에서 실제 경로를 추출(원본소스경로추출)
'// - 그 경로에 SRC_BASE가 포함되어 있으면 NAS_BASE로 교체
'// - 변경된 파일만 저장(SaveChanges:=True)
'// ============================================
Public Sub 쿼리원본경로교체()
    On Error GoTo EH
    
    Dim wsLog As Worksheet
    Dim fPath As String
    Dim fName As String
    Dim wb As Workbook
    Dim qt As WorkbookQuery
    
    Dim prevCalc As XlCalculation
    Dim prevUpd As Boolean, prevEvt As Boolean, prevAlert As Boolean
    
    Dim r As Long
    Dim qFormula As String
    Dim srcPath As String
    Dim newPath As String
    Dim newFormula As String
    
    Dim fileChanged As Boolean
    Dim totalChanged As Long
    Dim fileCnt As Long
    Dim shLoad As String
    Dim tblLoad As String
    
    '// ===== 로그 시트 준비 =====
    On Error Resume Next
    Set wsLog = ThisWorkbook.Worksheets("경로변경로그")
    On Error GoTo EH
    
    If wsLog Is Nothing Then
        Set wsLog = ThisWorkbook.Worksheets.Add
        wsLog.Name = "경로변경로그"
    Else
        wsLog.Cells.Clear
    End If
    
    With wsLog
        .Range("A1:F1").Value = Array("파일명", "쿼리명", "변경전 경로", "변경후 경로", "로드시트", "로드테이블명")
        .Rows(1).Font.Bold = True
    End With
    r = 2
    
    '// ===== 옵션 저장/끄기 =====
    prevCalc = Application.Calculation
    prevUpd = Application.ScreenUpdating
    prevEvt = Application.EnableEvents
    prevAlert = Application.DisplayAlerts
    
    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.DisplayAlerts = False
    
    '// ===== 폴더 선택 =====
    fPath = 파일경로()
    If fPath = "" Then
        MsgBox "폴더 선택이 취소되었습니다.", vbInformation
        GoTo CleanExit
    End If
    If Right$(fPath, 1) <> "\" Then fPath = fPath & "\"
    
    '// ===== 폴더 내 엑셀파일 반복 =====
    fName = Dir(fPath & "*.xls*")
    
    Do While Len(fName) > 0
        
        '// 현재 매크로 파일은 건너뛰기
        If StrComp(fPath & fName, ThisWorkbook.FullName, vbTextCompare) <> 0 Then
            
            Set wb = Workbooks.Open(Filename:=fPath & fName, ReadOnly:=False)
            fileChanged = False
            fileCnt = fileCnt + 1
            
            If wb.Queries.Count > 0 Then
                
                For Each qt In wb.Queries
                    qFormula = qt.Formula
                    srcPath = 원본소스경로추출(qFormula)   '// 원본 경로 추출
                    
                    If Len(srcPath) > 0 Then
                        '// SRC_BASE가 포함된 경우에만 변경
                        If InStr(1, srcPath, SRC_BASE, vbTextCompare) > 0 Then
                            
                            newPath = Replace(srcPath, SRC_BASE, NAS_BASE)
                            newFormula = Replace(qFormula, srcPath, newPath, , , vbTextCompare)
                            
                            If newFormula <> qFormula Then
                                qt.Formula = newFormula
                                fileChanged = True
                                totalChanged = totalChanged + 1
                                
                                shLoad = "": tblLoad = ""
                                쿼리시트테이블 wb, qt.Name, shLoad, tblLoad
                                
                                '// 로그 기록
                                wsLog.Cells(r, 1).Value = wb.Name
                                wsLog.Cells(r, 2).Value = qt.Name
                                wsLog.Cells(r, 3).Value = srcPath
                                wsLog.Cells(r, 4).Value = newPath
                                wsLog.Cells(r, 5).Value = shLoad
                                wsLog.Cells(r, 6).Value = tblLoad
                                r = r + 1
                            End If
                        End If
                    End If
                Next qt
            End If
            
            '// 변경된 파일만 저장
            If fileChanged Then
                wb.Close SaveChanges:=True
            Else
                wb.Close SaveChanges:=False
            End If
            
        End If
        
        fName = Dir()
    Loop
    
    wsLog.Columns("A:F").EntireColumn.AutoFit
    
    MsgBox "경로 변경 완료." & vbCrLf & _
           "변경된 쿼리 수: " & totalChanged & vbCrLf & _
           "처리한 파일 수: " & fileCnt, vbInformation
    
CleanExit:
    '// ===== 옵션 복구 =====
    Application.Calculation = prevCalc
    Application.ScreenUpdating = prevUpd
    Application.EnableEvents = prevEvt
    Application.DisplayAlerts = prevAlert
    Exit Sub
    
EH:
    MsgBox "오류 발생: " & Err.Description, vbExclamation
    Resume CleanExit
End Sub


'// ============================================
'// 폴더 선택용 함수 (FileDialog)
'// ============================================
Private Function 파일경로() As String
    Dim fd As FileDialog
    Dim sPath As String
    
    sPath = ""
    
    On Error GoTo EH
    Set fd = Application.FileDialog(msoFileDialogFolderPicker)
    
    With fd
        .Title = "파워쿼리 경로를 변경할 엑셀 파일이 들어있는 폴더를 선택하세요."
        .AllowMultiSelect = False
        If .Show = -1 Then
            sPath = .SelectedItems(1)
        End If
    End With
    
    파일경로 = sPath
    Exit Function
    
EH:
    MsgBox "폴더 선택 중 오류: " & Err.Description, vbExclamation
    파일경로 = ""
End Function


'// ============================================
'// M 코드에서 Source 경로 추출 (강화 버전)
'// 1) File.Contents / Folder.Files / SharePoint.Files / Web.Contents 우선
'// 2) 못 찾으면 ".xlsx" / ".xlsm" / ".xls" / ".csv" / ".txt" 등 확장자 기준으로 재탐색
'// ============================================
Private Function 원본소스경로추출(ByVal formulaText As String) As String
    Dim patterns As Variant
    Dim p As Variant
    Dim pos As Long
    Dim q1 As Long, q2 As Long
    Dim result As String
    
    result = ""
    
    '// 1차: 대표 함수 패턴 기준으로 경로 찾기
    patterns = Array("File.Contents(", _
                     "Folder.Files(", _
                     "SharePoint.Files(", _
                     "SharePoint.Contents(", _
                     "Web.Contents(")
    
    For Each p In patterns
        pos = InStr(1, formulaText, CStr(p), vbTextCompare)
        If pos > 0 Then
            q1 = InStr(pos, formulaText, """")          '// 해당 패턴 이후 첫 따옴표
            If q1 > 0 Then
                q2 = InStr(q1 + 1, formulaText, """")   '// 다음 따옴표
                If q2 > q1 Then
                    result = Mid$(formulaText, q1 + 1, q2 - q1 - 1)
                    Exit For
                End If
            End If
        End If
    Next p
    
    '// 2차: 그래도 못 찾으면 파일 확장자를 기준으로 탐색
    If result = "" Then
        Dim exts As Variant
        Dim e As Variant
        Dim extPos As Long
        
        exts = Array(".xlsx", ".xlsm", ".xlsb", ".xls", ".csv", ".txt")
        
        For Each e In exts
            extPos = InStr(1, formulaText, CStr(e) & """", vbTextCompare)
            If extPos > 0 Then
                '// ".xlsx""" 위치 기준으로 앞뒤 따옴표 찾기
                q1 = InStrRev(formulaText, """", extPos)
                q2 = InStr(extPos + 1, formulaText, """")
                
                If q1 > 0 And q2 > q1 Then
                    result = Mid$(formulaText, q1 + 1, q2 - q1 - 1)
                    Exit For
                End If
            End If
        Next e
    End If
    
    원본소스경로추출 = result
End Function


'// ============================================
'// 쿼리가 어느 시트/테이블에 로드되어 있는지 찾기
'// - ListObject.QueryTable.WorkbookConnection.Name 이
'//   보통 "Query - 쿼리명" 형태로 되어 있음
'// ============================================
Private Sub 쿼리시트테이블(ByVal wb As Workbook, _
                           ByVal qName As String, _
                           ByRef shName As String, _
                           ByRef tblName As String)
    Dim ws As Worksheet
    Dim lo As ListObject
    Dim connName As String
    
    shName = ""
    tblName = ""
    
    On Error GoTo EH
    
    For Each ws In wb.Worksheets
        For Each lo In ws.ListObjects
            If Not lo.QueryTable Is Nothing Then
                connName = ""
                On Error Resume Next
                connName = lo.QueryTable.WorkbookConnection.Name
                On Error GoTo EH
                
                '// "Query - 쿼리명" 안에 쿼리명이 들어있는지 체크
                If InStr(1, connName, qName, vbTextCompare) > 0 Then
                    shName = ws.Name
                    tblName = lo.Name
                    Exit Sub
                End If
            End If
        Next lo
    Next ws
    
    Exit Sub
    
EH:
    '// 로드 정보를 못 찾거나 에러가 나면 그냥 빈 값으로 둔다.
    shName = shName
    tblName = tblName
End Sub

​

​

3. 직원이 350명인데, 근태관리를
erp나 인사관리 전용프로그램을 사용하지 않는 이유가 있겠지만,

다만, 350명 × 수년치 근태 데이터는

파일 용량 증가와 새로고침 속도 저하, 여러 명이 동시에 사용할 때 파일 잠김

이슈와 같은 한계가 서서히 드러날 수 있어서,

중장기적으로는 Power BI나 SQL(또는 전용 인사/근태 시스템)으로
접근해 보시는 것을 권장드립니다.

​

답변에 감사드립니다.

잘 기억이 나지 않지만, 3시간 파워쿼리 설명 영상을 보며, 엑셀의 파워쿼리에 대한 대략적인 사용법을 알게 되었습니다. 전혀 기초는 없지만, 전임자가 만들어 사용하고 있는 것이 아무리 생각해도 문제가 될것 같다는 생각에 엑셀-엑세스로 연동하여 만들어 보면 어떨까하고 생각은 했지만, 막막하기만 합니다. GPT PRO로 코딩을 하고 있는데, 시간이 너무 많이 소요되고, 정확성이 떨어져서 결국은 하나 하나, 연결에 문제 있는 것을 찾아보고 있습니다. 경로문제는 이렇게 해결하면 될 것 같은데, 쿼리 자체에 오류가 있는 것으로 다음단계로 해결해야 하는 문제도 있습니다. 하지만, 우선 경로연결문제 부터 해결한 후 꼭 필요한 기능의 사용이 가능하다면, 엑셀-엑세스로 연동해서 새로 만들어 볼려고 합니다. 여기는 베트남 북북에 있는 하롱베이(유명관광지^_^) 근처에 있는 제조공장입니다. 생산팀의 근무파트,근무조, 주야간교대, 생산성 관리를 위한 많은 수당등으로 급여계산이 아주 복잡해서, 전임자가 엑셀파워쿼리를 통해서 만들었다는 것으로 들었습니다. 거의 완성되어서 업그레이드 해야 하는 상태에서 퇴사하게 되어서, 그 이후로 약 2년간 사용하고 있었다고 합니다. 그래서 쿼리중에 오류가 발생하는 것도 많이 있을것으로 보입니다.

저의 계획입니다, 보시고 고견을 부탁드립니다.

아래의 순서를 진행하고자 합니다.

(1)파일서버에서 사용가능하도록 경로수정

(2)현 사용중인 엑셀파일의 주요 대용량 데이터를 엑세스에 저장하고

   필요한 자료를 조회하여 엑셀로 가져와서 기존의 쿼리를 유지하며 사용

(3)가능한 2번으로 하고 싶지만, 힘들다고 판단하면, 엑셀-엑세스 연동으로 새로 만들기

어느 하나 내가 직접 할수 있는 일들이 아니고, 대신 해줄수 있는 IT직원도 없고

베트남이라 대화로 안되고. 어떻게든 해결을 해야 하고, 답답해서 글을 올립니다.

오빠도엑셀을 통해 엑셀,엑세스를 열심히 배우는 것도 답이고, 전문가에게 맡기는 것도 답인데,

우리회사의 업무에 대한 아주 구체적인 내용을 전달하고 설명하는 일도 만만치 않아서 또한 고민입니다.

이런 경우는 어떻게 진행을 해보면 좋을까요? 도움을 부탁드립니다.

감사합니다.

저를 도와주실수 있는 전문가는 어디에서 찾을수 있을지

고견을 부탁드립니다.

일전에 크몽을 통해서 지원을 받았는데, 상담만 하고 시간이 없다는 핑게로 황당한 결말이 있어서. 섣불리 못하겠더라구요. 많은 돈을 날리고 나니 신뢰감이 떨어져서요.