Access automation: Running a macro from one Excel Spreadsheet to a

M

mattieflo

Hello,

I have these reports that get saved to an excel file. What I need done is to
have these reports formatted in a certain way so that I can import them
cleanly. (Remove unnecessary rows, format headers correctly). I'm trying to
figure out the best approach. I have an excel macro which I can run from
access by hitting a button which formats the report and imports it cleanly.
The problem is that the user must cut from one excel file to the excel file
with the macro. Does anyone have a better approach?
 
B

bhicks11 via AccessMonster.com

Not sure what you mean by "the user must cut from one excel file." Do you
mean the user must copy cells from another spreadsheet into the spreadsheet
you are running the macro from. If so, why not have the macro do it at the
outset also?

Bonnie

http://www.dataplus-svc.com
 
M

mattieflo

What happens is the user has a report that has an "Export to excel" function.
If I have them export to the excel file which the macro is located on, it
will write over that file and thus the macro itself. I have to have them save
to a different file. I think I get what you're saying. Why not have them save
to a location each time and then have the macro cut from that file and paste
 
B

bhicks11 via AccessMonster.com

Yes - what I do is in the code before I run the process that overwrites the
file, I back it up. You can using a name based on the date you create with a
variable.

Here's a snippet of my code (I zip it with the PKZIP utility):

WhoID = UCase(Left(fOSUserName(), 3))
DoCmd.TransferDatabase acExport, "dBase III", "M:\", acTable, "MASTER",
WhoID + ".dbf", False
Call Shell("C:\ProfCert\PKZIP.EXE /k " & "C:\ProfCert\" & Format(Now(),
"yymmdd") & UCase(Left(CurrentUser(), 2)) & ".ZIP " & "M:\" & WhoID & ".dbf")
What happens is the user has a report that has an "Export to excel" function.
If I have them export to the excel file which the macro is located on, it
will write over that file and thus the macro itself. I have to have them save
to a different file. I think I get what you're saying. Why not have them save
to a location each time and then have the macro cut from that file and paste
on the file where the macro is stored?
Not sure what you mean by "the user must cut from one excel file." Do you
mean the user must copy cells from another spreadsheet into the spreadsheet
Click to expand...
[quoted text clipped - 14 lines]
Click to expand...
 
Top