Each month I receive an Excel file with information that needs to be
processed then sent or linked to an Access database for further processing.
Essentially, the columns are AuthorizationID, SystemID, Service, and
CPUMinutes. The received file has a three row header before the column
headings and data. In order to prepare it for processing I need to remove the
heading information and add three new columns: Allocation, YearMonth, and
Type. The Allocation is derived from the AuthorizationID column, and I can
use a formula. The YearMonth is the new processing year and month in yyyymm
format; and the Type is the same for each new file, but does not come in the
received file, so I have to copy it for the amount of rows of data (each
month can have different numbers of rows).

I would like to streamline and automate the process as much as possible,
perhaps using macros or vba. I have what I would classify as a good
intermediate knowledge and experience of vba in Access, but I'm not familiar
with its use in Excel. My idea is to get the received data into the format
necessary for the Access processing and perhaps link that file so Access
handles it automatically. The idea seems on the right track to me, but a bit
cumbersome in that I'd have to also clear all the prior month data from the
linked file each time after Access processes it into a cumulative archive.
I'm not sure if I want or need to archive each processed excel file, since I
keep the original files sent to me.

I would greatly appreciate any ideas and help.

This sounds like something you could take a big first step on using the
macro recorder. Start with a new received file, turn on the recorder,
and modify it. Turn the recorder off. The code it generates is not the
most efficient, but it's faster than manual.

Hth,
Merjet

Thanks, I'll try that and let you know if it helps.

"merjet" wrote:

This sounds like something you could take a big first step on using the
macro recorder. Start with a new received file, turn on the recorder,
and modify it. Turn the recorder off. The code it generates is not the
most efficient, but it's faster than manual.

Hth,
Merjet

OK, I tried it. Here's my sticking point. This is a copy of the relevant part
of the macro in visual editor:

Sub copy_IDs()

Range("G2").Select
Selection.Copy
'move one cell to the left to find the end row of the data
Range("F2").Select
'move to the end of the data in column F
Selection.End(xlDown).Select
'move one cell to the right into empty column G that corresponds to the last
row
'in data column F
Range("G884").Select
'select from that cell to the top of column G
Range(Selection, Selection.End(xlUp)).Select
'this again should be a relative range designation
Range("G3:G884").Select
Range("G884").Activate
ActiveSheet.Paste
Application.CutCopyMode = False
End Sub

The idea here is to copy the formula in the first cell of column G and fill
the column with results read from the other data columns...The new data each
month will come into row 2 (columns A thru F). Columns G, H, and I add
information not in the original data. The problem here is that once the
relative end of column F is selected, the range is identified with the
specific row number. I hope I'm clear on what I'd like to do. Can you help
now?

"Ray S." wrote:

Thanks, I'll try that and let you know if it helps.

"merjet" wrote:

This sounds like something you could take a big first step on using the
macro recorder. Start with a new received file, turn on the recorder,
and modify it. Turn the recorder off. The code it generates is not the
most efficient, but it's faster than manual.

Hth,
Merjet