If this can be done then your a genius Access to Excel VB coding

[andy callaghan]

Converting Excel VB to Access VB, can it be done

--------------------------------------------------------------------------------

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
Sub Mail_sheets()
Dim MyArr As Variant
Dim last As Long
Dim shname As Long
Dim a As Integer
Dim Arr() As String
Dim N As Integer
Dim strdate As String
For a = 1 To 253 Step 3
If ThisWorkbook.Sheets("mail").Cells(1, a).Value = "" Then
Exit Sub
End If
Application.ScreenUpdating = False
last = ThisWorkbook.Sheets("mail").Cells(Rows.Count, _
a).End(xlUp).Row
N = 0
For shname = 1 To last
N = N + 1
ReDim Preserve Arr(1 To N)
Arr(N) = ThisWorkbook.Sheets("mail").Cells(shname, a).Value
Next shname
ThisWorkbook.Sheets(Arr).Copy
strdate = Format(Date, "dd-mm-yy") & " " & _
Format(Time, "h-mm-ss")
ActiveWorkbook.SaveAs "Part of " & ThisWorkbook.Name _
& " " & strdate & ".xls"
With ThisWorkbook.Sheets("mail")
MyArr = .Range(.Cells(1, a + 1), .Cells(Rows.Count, _
a + 1).End(xlUp))
End With
ActiveWorkbook.SendMail MyArr, ThisWorkbook.Sheets("mail").Cells(1, a + 2).Value
ActiveWorkbook.ChangeFileAccess xlReadOnly
Kill ActiveWorkbook.FullName
ActiveWorkbook.Close False
Application.ScreenUpdating = True
Next a
End Sub
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

 

[RoryA]

I think I know why your mail macro wasn't working - you can't put one sub inside another! move the mail sub below the other one, and then add a line:

Mail_sheets

to your import sub at the point where you want to mail the sheets.

For the rest, you could do all of that from Excel including the Access bit if you wanted, or do it all from Access, including the Excel bit.

 

[andy callaghan]

this is the problerm when you know a bit, guess abit and it ends up so longa coding you lose it or doesnt understand the answer. Because this forum question is for coding and no one has accepted the role of converting, I am running three separate macros

Access, then "import" macro in excel, and now trying to creat this mail macro/code in . I only show it all just in case someone wanted to try to write it all for me. I ahve taken out my addy as you suggested, didnt think about spam. juts banging my head omn desk,