G
Guest
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.
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.