Hello - I have code (below) that grabs values present in specific cells in .CSV files in the folder designated in my 'BkLogDir' variable and writes them a designated number of columns over to the right in the Active Worksheet (I believe). However, I need to the code to be adjusted to write the values extracted from those targeted .CSV files to a particular Worksheet of the active Workbook. Being only partially familiar with VBA code in general, I'm not even seeing where my current code knows which Worksheet to write to (unless, as I said earlier, it is just writing to the active Worksheet). How do I adjust the code to write to a particular Worksheet in the active Workbook?
Thank you!
Sub Update_WebstatCnt_Values()
Dim cell As Range
Dim strPath As String
Dim strSheet As String
Dim BkLogDir As String
'Establish directory where Webstats .XLSM 'Daily' report files have been copied to (from the original .CSV versions of those files)
BkLogDir = "C:\Users\ptdooley\OneDrive - Commonwealth of Massachusetts\HIX Materials\HIX_WebStats_Materials\Webstats_Hrly_Daily_Rpts\Orig_CSV_Rpts" '-- *This is the PROD file path*
'BkLogDir = "C:\Webstats_RptAttachments\Webstats_TestingArea\Webstats_AttachmentsTEST\WebstatsDaily_Testing" '-- *This is the TEST file path*
strPath = BkLogDir & Application.PathSeparator
strSheet = "MAHIX_Individual_Site_Unique_Vi"
For Each cell In Range("A2", Range("A" & Rows.Count).End(xlUp))
With cell.Offset(, 10)
If Len(Dir(strPath & cell.Value)) Then
.Formula = "='" & strPath & "[" & cell.Value & "]" & strSheet & "'!A14"
.Value = .Value
Else
.ClearContents
End If
End With
With cell.Offset(, 11)
If Len(Dir(strPath & cell.Value)) Then
.Formula = "='" & strPath & "[" & cell.Value & "]" & strSheet & "'!B14"
.Value = .Value
Else
.ClearContents
End If
End With
Next cell
'Change Excel file extension from ".xlsm" back to ".csv"
With Range("A:A")
Do Until .Find(".xlsm") Is Nothing
.Replace ".xlsm", ".csv"
Loop
End With
End Sub
Bookmarks