jonsharman
New Member
- Joined
- Jan 4, 2014
- Messages
- 28
Hi,
Please can someone help me?! I have completed a Workbook that is used across our organisation that is used to track daily and monthly sales and automate some of our daily processes. There are components contained in the workbook that are crucial to the correct operation for all users and, more importantly, help us improve our reporting accuracy. One of the requirements is a daily import of the stock file and the other is the ability to automatically email the daily and monthly trackers directly from Excel.
All of the test machines have been running Office 2010 and all features are now working a treat. Now that the Workbook has made it out into the wider world some users are running Office 2007 and they cannot run the Import or Email routines. The error is a 'Compile Error' on these machines - when I run it unlocked the debug seems to point to a line of code that seems totally fine ... !
After some research on Early and Late Binding I have re-written the code to try and move this to the Late (preferred) method so as to support the 2007 based machines. However when I run these macros they are still failing and I am now at the point where I cannot see the wood for the trees...
The code is as follows:
IMPORT ROUTINE ...
EMAIL ROUTINE (I have removed the Email addresses for obvious reasons ... )
Can anyone point me in the direction of where I am going wrong so I can get this to work on both 2007 and 2010 platforms?
Many Thanks
Please can someone help me?! I have completed a Workbook that is used across our organisation that is used to track daily and monthly sales and automate some of our daily processes. There are components contained in the workbook that are crucial to the correct operation for all users and, more importantly, help us improve our reporting accuracy. One of the requirements is a daily import of the stock file and the other is the ability to automatically email the daily and monthly trackers directly from Excel.
All of the test machines have been running Office 2010 and all features are now working a treat. Now that the Workbook has made it out into the wider world some users are running Office 2007 and they cannot run the Import or Email routines. The error is a 'Compile Error' on these machines - when I run it unlocked the debug seems to point to a line of code that seems totally fine ... !
After some research on Early and Late Binding I have re-written the code to try and move this to the Late (preferred) method so as to support the 2007 based machines. However when I run these macros they are still failing and I am now at the point where I cannot see the wood for the trees...
The code is as follows:
IMPORT ROUTINE ...
Code:
Private Sub ImportData()
Dim wb1 As Workbook
Dim wb2 As Workbook
Dim ws As Worksheet
Dim nextrow As Long
Dim Res As Variant
Dim sheetarray As Variant
Dim ans1 As Integer
Dim ans2 As Integer
Dim ans3 As Integer
Set wb1 = ActiveWorkbook
On Error GoTo ErrorHandler_ImportData
Sheets("Usage").Range("USEIDS") = Sheets("Usage").Range("USEIDS") + 1
sheetarray = Array("Menu", "DailySales", "MonthlySales", "DealStack", "StockProfile", "InvData", _
"DATA", "Execs", "Usage", "WTY", "MPL", "STOCK", "PREP", "IDEAL")
ans1 = MsgBox("Would you like to update the Stock File now?", vbYesNo + vbQuestion, "Update Stock?")
If ans1 = vbYes Then
Application.ScreenUpdating = False
'FileToOpen = Application.GetOpenFilename(Title:="Please Choose Stock Source File", FileFilter:="*.xlsx (*.xlsx),")
FileToOpen = Sheets("MENU").Range("STOCKFILE")
If FileToOpen = "" Then
MsgBox "No File Specified!", vbCritical & vbInformation, "WARNING: No File Found!"
Exit Sub
Else
Set wb2 = Workbooks.Open(Filename:=FileToOpen)
For Each sheet In wb2.Sheets
If sheet.Visible = True Then
sheet.Copy After:=wb1.Sheets(wb1.Sheets.Count)
End If
Next sheet
wb2.Close
End If
Sheets("Stock").Rows("2:" & Rows.Count).ClearContents
With Sheets("UsedVSB_MA5 (VS)")
nextrow = .Range("B" & Rows.Count).End(xlUp).Row
.Range("A2:Z" & nextrow).Copy
Sheets("Stock").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
End With
Sheets("Stock").Activate
Range("AA2") = "=TODAY()-I2"
Range("AA2").AutoFill Destination:=Range("AA2:AA" & Cells(Rows.Count, "A").End(xlUp).Row)
Range("AB2") = "=ROUNDDOWN(YEARFRAC(H2, TODAY(),1),0)"
Range("AB2").AutoFill Destination:=Range("AB2:AB" & Cells(Rows.Count, "A").End(xlUp).Row)
Range("AC2") = "=IF(V2=0,SRCNA,INDEX(SRCDESC,MATCH(V2,SRCCODE,0)))"
Range("AC2").AutoFill Destination:=Range("AC2:AC" & Cells(Rows.Count, "A").End(xlUp).Row)
Range("AD2") = "=IF(R2=0,0,IF(B2=""N"",(((R2-J2)/1.2)+(J2-K2)-(SUM(L2:N2))),(IF(B2=""Q"",((R2/1.2)-K2)-(SUM(L2:N2))))))"
Range("AD2").AutoFill Destination:=Range("AD2:AD" & Cells(Rows.Count, "A").End(xlUp).Row)
wb1.Save
On Error Resume Next
Set ws = Worksheets("Menu")
On Error GoTo 0
If Not ws Is Nothing Then
For Each ws In ThisWorkbook.Worksheets
Res = Application.Match(ws.Name, sheetarray, 0)
If IsError(Res) Then ws.Delete
Next ws
End If
Sheets("Stock").Columns.AutoFit
Sheets("Menu").Range("SIDATE") = Date
Sheets("Menu").Activate
ans2 = MsgBox("The Stock file has been updated succesfully!", vbOKOnly + vbInformation, "Success!")
Application.ScreenUpdating = True
End If
Exit Sub
ErrorHandler_ImportData:
ans3 = MsgBox("The Stock File has not been imported!", vbCritical + vbInformaiton, "WARNING: No Import Found!")
Application.ScreenUpdating = True
Exit Sub
End SubEMAIL ROUTINE (I have removed the Email addresses for obvious reasons ... )
Code:
Private Sub EmailDailyTracker()
Dim oApp As Object
Dim oMail As Object
Dim LWorkbook As Workbook
Dim LFileName As String
Dim LSiteRef As String
Dim LDate As String
Dim LSiteName As String
Dim OutApp As Outlook.Application
Dim OutMail As Outlook.MailItem
Dim ans1 As Integer
On Error GoTo ErrorHandler_SendEmail
Application.ScreenUpdating = False
Sheets("Usage").Range("USEEDS") = Sheets("Usage").Range("USEEDS") + 1
Load DSTForm
DSTForm.Show
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(olMailItem)
LSiteRef = Sheets("DailySales").Range("DSTSITEREF")
LSiteName = Sheets("DailySales").Range("DSTSITE")
LDate = Date
Sheets("DailySales").Visible = xlSheetVisible
Sheets("DailySales").Copy
Set LWorkbook = ActiveWorkbook
LFileName = LWorkbook.Worksheets(1).Name & "_" + LSiteRef
On Error Resume Next
Kill LFileName
On Error GoTo 0
LWorkbook.SaveAs Filename:=LFileName
Set oApp = CreateObject("Outlook.Application")
Set oMail = oApp.CreateItem(0)
With oMail
.SendUsingAccount = OutApp.Session.Accounts.Item(1)
.To = ""
.CC = ""
Subject = "Used Car Daily Sales Tracker " & LDate
.body = "Hi All" & vbCrLf & vbCrLf & _
"Please see attached the Used Car Daily Sales Tracker from " _
& LSiteName & " for " & LDate & vbCrLf & vbCrLf & _
"Thanks" & vbCrLf & vbCrLf
.Attachments.Add LWorkbook.FullName
'.Send
.Display
End With
LWorkbook.ChangeFileAccess Mode:=xlReadOnly
Kill LWorkbook.FullName
LWorkbook.Close SaveChanges:=False
Set oMail = Nothing
Set oApp = Nothing
Application.ScreenUpdating = True
ShowMenu
Exit Sub
ErrorHandler_SendEmail:
ans1 = MsgBox("The eMail could not be sent - please try again!", vbCritical + vbInformaiton, "WARNING: eMail Send Error!")
Application.ScreenUpdating = True
Exit Sub
End SubCan anyone point me in the direction of where I am going wrong so I can get this to work on both 2007 and 2010 platforms?
Many Thanks
