VBA to Open Excel and Perform Oprations

[jhabey01]

I am trying to writing code that will perform the following:
1) Open an excel file located in this directory: C:\Users\jhaberme\Desktop\DailyFiles
2) Insert a Colum in Column A
2) Name the Column Group
3) Fill in the column beginning at A2 with the Sheet Name
4) Save and close the File as SheetName_ExportedData_Yestrday'sDate as YYYYMMDD

What I want to happen is if a files exists in the folder Open it and do steps 1 -4, as I could have 30 - 40 files in the folder. Also with step 3 filling in the column with the sheet name, I need to fill in every row that as data in the column next to it (Col b which is called Employee SSN) So what this means is that the number of rows in which I have data varies.

Thanks,
John

 

[Andrzejek]

Start Macro recorder in Excel
Do the steps 1 – 5 ‘by hand’
Stop recording

Look at the code created


Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[jhabey01]

A gee thanks for the reply but it sound like your just posting to post something. Anway I wanted to post the solution:

Sub BatchChangeFiles()
Dim fPath As String
Dim fName As String
Dim wb As Workbook
Dim ws As Worksheet
Dim lastRow As Long
Dim newName As String
Dim newPath As String
Dim i As Long

'Which folder to get from?
fPath = "C:\Users\jhaberme\Desktop\DailyFiles"

'Which folder to save to?
newPath = "R:\Reports\Daily Reports\DailyData"

'Check if slash included
If Right(fPath, 1) <> "\'" Then
fPath = fPath & "\"
End If
If Right(newPath, 1) <> "\" Then
newPath = newPath & "\"
End If

'Check for xlsx files
fName = Dir(fPath & "*.xlsx")

'Turn of the screen
Application.ScreenUpdating = False

'Loop until we run out of files
Do While fName <> ""
'Open the workbook
Set wb = Workbooks.Open(fPath & fName)

'ASSUMPTION: We're only dealing with first worksheet
Set ws = wb.Worksheets(1)
With ws
.Range("A:A").Insert
.Range("A1").Value = "Group"
'Find end of data in col B
lastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
.Range("A2:A" & lastRow).Value = ws.Name
End With
'Determine new file name
newName = newPath & ws.Name & "_ExportedData_" & Format(Date - 1, "YYYYMMDD") & ".xlsx"
'Close and save
wb.Close SaveChanges:=True, Filename:=newName
'Increment count for feedback
i = i + 1
'Get next file name
fName = Dir()
Loop
'turn screen back on
Application.ScreenUpdating = True

'Give feedback
MsgBox "All done." & vbNewLine & "Number of files changed: " & i, vbOKOnly, "Run complete"

End Sub

 

[SkipVought]

Jhabey01, Andy was not, "just posting to post something."

He gave you a solution for generating the code you wanted to perform your list of operations: record a macro.

He showed you how to fish, rather than simply giving you a trout. That's NOT, "just posting to post something!

 

[jhabey01]

Recoding a macro was not the solution I was looking for.

 

[strongm]

Not quite sure why you are posting here if you simply going to be condescending and sarcastic to people who try to help you. Seems an odd response.