So I found this code, more than likely on this site, and it works like you would expect it to. However, instead of only replacing one cell I need to replace a whole column of values. Column "W" is the one to be replaced and Column "X" will be the new values. This code is being run on import into the database.
The column placement will always be the same, the number of rows will vary day-to-day (shouldn't be more than 100 but that is just a guess). I had tried using Sheets(1).Range("W2:W200").Value in the appropriate locations but that has not worked. Is there another way to do this without having to go cell by cell?
Ideally I would not want to open the excel file and manually change everything that way. Any manipulation done to the file I would want done through Access. I can provide the full import code if needed.
The column placement will always be the same, the number of rows will vary day-to-day (shouldn't be more than 100 but that is just a guess). I had tried using Sheets(1).Range("W2:W200").Value in the appropriate locations but that has not worked. Is there another way to do this without having to go cell by cell?
Ideally I would not want to open the excel file and manually change everything that way. Any manipulation done to the file I would want done through Access. I can provide the full import code if needed.
Code:
For Each sPath In .SelectedItems
With objXL
.Workbooks.Open (sPath)
ProdDate = Mid(sPath, InStrRev(sPath, " ", -1), 11)
.Sheets(1).Range("A1").Value = Replace(.Sheets(1).Range("A1").Value, ".", "")
.Sheets(1).Range("D1").Value = Replace(.Sheets(1).Range("D1").Value, ".", "")
.Sheets(1).Range("F1").Value = Replace(.Sheets(1).Range("F1").Value, ".", "")
.Sheets(1).Range("W2").Value = Replace(.Sheets(1).Range("W2").Value, "Other", .Sheets(1).Range("X2").Value)
.ActiveWorkbook.Save
.ActiveWorkbook.Close
.Application.Quit
End With