Converting Excel VB to Access VB, can it be done

[andy callaghan]

After several months pulling my hair out, I have now managed to combined 3 different corporate systems into one MS Access database. unfortunately I am not clever enough to perform the calculations I need, so I export to excel and do there. This is my scenario
VB created to run all 3 three queries, "intake" and "SALES" an amalgamation query"all", export to network file and launch my main calculation spreadhseet"sumssheet" again on company netwwork.

I have created a macro within excel, to go get the export data and copy in the
sumsheet, copy/paste against the imported data and then calculate the whhole workbook( charts and stuff updated accordingly)

here are my two vb codes, first access

Function New_macro()
On Error GoTo New_macro_Err
Beep
MsgBox "You are connecting to the Global network- delays may occur", vbInformation, "Advisory Note"
DoCmd.SetWarnings False
DoCmd.OpenQuery "Delete MRO Compilation", acViewNormal, acEdit
DoCmd.OpenQuery "AP Appending", acViewNormal, acEdit
DoCmd.OpenQuery "MG Appending", acViewNormal, acEdit
DoCmd.OpenQuery "NE Appending", acViewNormal, acEdit
DoCmd.OpenQuery "MRO Intake Compilation Query", acViewNormal, acEdit
DoCmd.Close acQuery, "MRO Intake Compilation Query"
DoCmd.OutputTo acTable, "MRO Intake Compilation", "MicrosoftExcelBiff8(*.xls)", "\\Bhx1sv-dm1\departmental\busdev\MRO\Longhsheets\MRO Overall\2008\MRO Intake.xls", True, "", 0
DoCmd.Close acTable, "MRO Intake Compilation"
DoCmd.OpenQuery "APOrderIntake Table Maker", acViewNormal, acEdit
DoCmd.OutputTo acTable, "AP Order Intake", "MicrosoftExcelBiff8(*.xls)", "\\Bhx1sv-dm1\departmental\busdev\MRO\Longhsheets\MRO Overall\2008\MRO SALES.xls", True, "", 0
DoCmd.Close acQuery, "APOrderIntake"
Beep
MsgBox "Your files have been updated with the lastest system generated information. please close Access and Launch Excel. Using file open to find your required files ", vbInformation, "You Are Finished Updating the database. Thank you and please come again"
Call Shell("C:\Program Files\Microsoft Office\OFFICE11\excel.exe ""Z:\MRO\Longhsheets\MRO Overall\2008\2008 Order Intake Monitor All USD.XLS""", 1)


New_macro_Exit:
Exit Function
New_macro_Err:
MsgBox Error$
Resume New_macro_Exit

End Function

Now the excel Vbmacro called "importmacro"

Sheets("MRO Intake").Select
Range("A1").Select
Workbooks.Open Filename:="Z:\MRO\Longhsheets\MRO Overall\2008\MRO Intake.xls"
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Windows("2008 Order Intake Monitor All USD.xls").Activate
ActiveSheet.Paste
Application.CutCopyMode = False
Windows("MRO Intake.xls").Activate
Range("Z1").Select
Application.DisplayAlerts = False
ActiveWindow.Close
Sheets("MRO Sales").Select
Range("A1").Select
Workbooks.Open Filename:="Z:\MRO\Longhsheets\MRO Overall\2008\MRO Intakev2.xls"
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Windows("2008 Order Intake Monitor All USD.xls").Activate
Range("A1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Windows("MRO Intakev2.xls").Activate
Application.DisplayAlerts = False
ActiveWindow.Close
Sheets("MRO Sales").Select
Range("E2:K2").Select
Selection.Copy
Selection.End(xlToLeft).Select
Selection.End(xlToLeft).Select
Selection.End(xlDown).Select
Range("E3511").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.Copy
Range(Selection, Selection.End(xlUp)).Select
ActiveSheet.Paste
Range("A1").Select
Sheets("MRO intake").Select
Range("E2:K2").Select
Selection.Copy
Selection.End(xlToLeft).Select
Selection.End(xlToLeft).Select
Selection.End(xlDown).Select
Range("E618").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.Copy
Range(Selection, Selection.End(xlUp)).Select
ActiveSheet.Paste
Range("A1").Select
Sheets("MRO Intake 2008").Select
Range("A1").Select
Calculate
End Sub

after all that , can anyone please rewrite macro for the excel part into a access vb version, as pasting the two types of code dont work.

thanking you in advance

Andy

 

[Damon Ostrander]

Hi Andy,

I just want to let you know that yes, it is not difficult to modify an Excel macro to run within Access. It is mostly a matter of properly qualifying all the Excel objects with the Excel Application object, and using GetObject to open the Excel files.

Unfortunately, I don't have time to do it for you right now, but I'm hoping that just knowing that it is possible will be some encouragement to you. I would give it another 24 hours, then try re-posting.

Perhaps someone else can step in here and help out?

Damon