vba to send email at the end of the day.

[Pancakes1032]

Hi All,

So I have this code that I am currently using to send emails at 6pm to supervisors of all their employee's work production. It does work however it doesn't shut itself off properly. Or maybe I just didn't compile this code correctly. The issue I am having is that the employee logs are Macro enabled because well they have codes in them. Some of the employee's workbook seem to show the project explorer for the master workbooks that have the email code, even though they don't have those workbooks open and shouldn't have those project explorers on their computers. Please let me know if I did something wrong or if I need to add something to the code to remove it from all workbooks at the end of the day.

Personally that specific code should only run from my personal computer and then shut off at the end of the night. Here's the code I have:


Private Sub Workbook_Open()
Application.OnTime TimeValue("18:00:00"), "Send_Lilly"
End Sub





Sub Send_Lilly()
Dim Source As Range
Dim Dest As Workbook
Dim wb As Workbook
Dim TempFilePath As String
Dim TempFileName As String
Dim FileExtStr As String
Dim FileFormatNum As Long
Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String
Dim Excel_workbook As Excel.Workbook

Set Source = Nothing
On Error Resume Next
Set Source = Range("A1:Q100")
On Error GoTo 0

With Application
.ScreenUpdating = False
.EnableEvents = False
End With
Set wb = ActiveWorkbook
Sheets("Employee-Lilly").Select
Range("A1:Q1").Select
Selection.AutoFilter
Range("C2").Select

ActiveSheet.Range("A1:V80000").AutoFilter Field:=15, Criteria1:=">=" & Date
Set Dest = Workbooks.Add(xlWBATWorksheet)
Source.Copy
With Dest.Sheets(1)
.Cells(1).PasteSpecial Paste:=8
.Cells(1).PasteSpecial Paste:=xlPasteValues
.Cells(1).PasteSpecial Paste:=xlPasteFormats
.Cells(1).Select
Application.CutCopyMode = False
End With

TempFilePath = Environ$("temp") & "\"
TempFileName = "Lilly's Daily Completed Assignments " & wb.name & " " _
& Format(Now, "dd-mmm-yy h-mm-ss")
If Val(Application.Version) < 12 Then
' You are using Excel 2000 or 2003.
FileExtStr = ".xls": FileFormatNum = -4143
Else
' You are using Excel 2007 or 2010.
FileExtStr = ".xlsx": FileFormatNum = 51
End If
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
With Dest
.SaveAs TempFilePath & TempFileName & FileExtStr, _
FileFormat:=FileFormatNum
On Error Resume Next

With OutMail
strbody = "Hello," & vbNewLine & vbNewLine & _
"Here are today's cases I have touched and/or completed" & vbNewLine & _
"Thank you"
On Error Resume Next

With OutMail
.To = "SUPS"
.CC = ""
.BCC = ""
.Subject = "Lilly's Daily Assignment Production"
.Body = strbody
.Attachments.Add Dest.FullName
.Send
.Close SaveChanges:=False
End With
On Error GoTo 0
End With

With ActiveWorkbook
.Close SaveChanges:=False
End With
Set OutMail = Nothing
Set OutApp = Nothing
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End With

Windows("Master.xlsm").Activate
ActiveSheet.ShowAllData
Selection.AutoFilter
Set Excel_workbook = Nothing


End Sub


Thank you!

[westconn1]

so which workbook is the code in?

it would appear that each employees workbook has a send procedure within the workbook, though this may be an incorrect assumption on my part

maybe you should be using an addin workbook with appropriate code to send all employees workbooks as required
the addin, then would only be on your computer, and no send code would be required within employees workbooks

[Pancakes1032]

so which workbook is the code in?

it would appear that each employees workbook has a send procedure within the workbook, though this may be an incorrect assumption on my part

maybe you should be using an addin workbook with appropriate code to send all employees workbooks as required
the addin, then would only be on your computer, and no send code would be required within employees workbooks

Actually there's only 1 workbook. Each employee has their own sheet within the workbook. The workbook open code is obviously under the workbook. The send _ lilly code is in module 2. All the send codes are in that module

[SamT]

Pancakes, This post is to show you a slightly improved version of your code style. The algorithm is all yours.

BTW, Are you sure that you have only been coding for the last month or so?!? I'm impressed.

Option Explicit

Sub Send_Lilly()
'Dim Source As Range ' "Source" is an Excel Key Word
Dim Src As Range
Dim Dest As Workbook
Dim ThisBook As Workbook 'Replaces generic "wb"
Dim TempFilePath As String
Dim TempFileName As String
Dim FileExtStr As String
Dim FileFormatNum As Long
Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String
'Dim Excel_workbook As Excel.Workbook 'Not used

'Set Src = Nothing
'an Object Variable is Nothing until it is set to something in code

'On Error Resume Next
'Do not use VBA Error handling until Coding is complete and tested

Set Src = Range("A1:Q100")
'Src will be Range("A1:Q100") on the active or selected sheet.

'On Error GoTo 0 'Disable Error Handling.

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

Set ThisBook = ActiveWorkbook
With ThisBook 'ActiveWorkbook
With Sheets("Employee-Lilly")
.Range("A1:Q1").AutoFilter
'Range("C2").Select

.Range("A1:V80000").AutoFilter Field:=15, Criteria1:=">=" & Date

Set Dest = Workbooks.Add(xlWBATWorksheet) 'ATT is ActiveWorkbook
Src.Copy

With Dest.Sheets(1).Range("A1") 'Reads better than Cells(1)
.PasteSpecial Paste:=xlPasteColumnWidths '8 'Avoid "hard coded" values
.PasteSpecial Paste:=xlPasteValues
.PasteSpecial Paste:=xlPasteFormats
'.Select 'This Range no longer used.
Application.CutCopyMode = False
End With 'Dest Sheet1

TempFilePath = Environ$("temp") & "\"
TempFileName = "Lilly's Daily Completed Assignments " & ThisBook.Name & " " _
& Format(Now, "dd-mmm-yy h-mm-ss")

If Val(Application.Version) < 12 Then
' You are using Excel 2000 or 2003.
FileExtStr = ".xls": FileFormatNum = -4143
Else
' You are using Excel 2007 or 2010.
FileExtStr = ".xlsx": FileFormatNum = 51
End If

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

With Dest 'Workbook
.SaveAs TempFilePath & TempFileName & FileExtStr, _
FileFormat:=FileFormatNum
'On Error Resume Next 'Redundant

With OutMail
strbody = "Hello," & vbNewLine & vbNewLine & _
"Here are today's cases I have touched and/or completed" & vbNewLine & _
"Thank you"

'On Error Resume Next 'Redundant

'With OutMail 'Redundant
.To = "SUPS"
'.CC = "" 'Not Used? Don't encode.
'.BCC = ""
.Subject = "Lilly's Daily Assignment Production"
.Body = strbody
.Attachments.Add Dest.FullName
.Send
.Close SaveChanges:=False
'End With 'outmail Redundant
End With 'OutMail
On Error GoTo 0 'Disable Error Handling.
End With 'Dest Workbook

'With ActiveWorkbook 'Programmers should not use "With" with single lines
ActiveWorkbook.Close SaveChanges:=False
'ActiveWorkbook is Dest Workbook ATT.
'End With 'Active workbook Dest ATT

Set OutMail = Nothing
Set OutApp = Nothing

With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End With 'Range("A1:Q1")
End With 'ThisBook

Windows("Master.xlsm").Activate
ActiveSheet.ShowAllData
Selection.AutoFilter 'Replace "Selection" with Range.
'Set Excel_workbook = Nothing 'Not used


End Sub

[SamT]

This time I refactored the code my way for your consideration.
Note that this code is also based on the way I structure my workbooks.

All Employee sheets are named with their Log-In name, or (If they use nicknames as Log-ins,) a cross reference list on the VBA lists and references sheet that I can VLookUp with.

All other sheets have a 2 or 3 letter prefix in their CodeNames.

In VBA, press the F4 key to see the Properties Window, and Ctrl+R for the Project Explorer. Clcik a sheet in the Explorer. It will have two names like "Sheet1" and "(Master)." look at the Properties Window, the top (Name) lists will be like "Sheet1," and the other Name will be like "Master." In the Properties Window you can change the Code Name of a Sheet by changing the (Name) Property. IOW, The Name Property is the Tab Name and the (Name) Property is the Code Name.

Back to the Prefixes. With your project I might use "ea_" (for Employee Assignment Project,) I might Use a generic "vba_" as a prefix. I would never use "xl_" or "vb_" to at all costs avoid confusing VBA.

Since you have many employee sheets and only a few others, if you use a code name prefix, you can use simple Employee names for their sheets and use exclusionary code like


For each Sht in Sheets
If Not (Left(Sht.CodeName, 4) = "vba_" Then
'it is an Employee sheet
Call StuffToDo Sht.Name
End If
Next Sht

Anyway, here it is "My Way."

Option Explicit

Sub Send_Lilly()
'Sends a copy of Employee's work to Supervisors at end of day

Dim ThisBook As Workbook
Dim RprtSht As Worksheet
Dim Src As Range
Dim TempBook As Workbook
Dim Dest As Range

Dim TempFilePath As String
Dim TempFileName As String
Dim EmpName As String
Dim TimeStamp As String
Dim FileExtStr As String
Dim FileFormatNum As Long

Dim OutApp As Object
Dim OutMail As Object
Dim MailTo As String
Dim Subj As String
Dim strBody As String

GoFast 'Set Application Properties

EmpName = Environ$("USERNAME") 'Empname must be first

''''Set Object Variables
Set ThisBook = ActiveWorkbook
Set RprtSht = ThisBook.Sheets(EmpName) 'Or use ("Employee_" & EmpName
Set Src = RprtSht.UsedRange
Set TempBook = Workbooks.Add(xlWBATWorksheet)
Set Dest = TempBook.Sheets(1).Range("A1")

''''Set Filename Parts
TimeStamp = Format(Now, "dd-mmm-yy h-mm-ss")
TempFilePath = Environ$("temp") & "\"
TempFileName = EmpName & "'s Daily Completed Assignments " & ThisBook.Name & TimeStamp

If Val(Application.Version) < 12 Then 'XL 2002,3
FileExtStr = ".xls": FileFormatNum = -4143
Else 'Xl >=2007
FileExtStr = ".xlsx": FileFormatNum = 51
End If

''''Set Outlook Variables
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
MailTo = "Sups"
Subj = EmpName & "'s Daily Assignment Production"
strBody = "Hello," & vbNewLine & vbNewLine & _
"Here are today's cases I have touched and/or completed" & vbNewLine & _
"Thank you"



'**** ACTIVE CODE ****
'On Error Resume Next 'Uncomment when all code tested

''''Copy Data
With Src
.Rows(1).AutoFilter
.AutoFilter Field:=15, Criteria1:=">=" & Date
.Copy
End With 'Src

''''Paste Data
With Dest
.PasteSpecial Paste:=xlPasteColumnWidths
.PasteSpecial Paste:=xlPasteValues
.PasteSpecial Paste:=xlPasteFormats
Application.CutCopyMode = False
.SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum
End With 'Dest

''''Send temp book to bosses
With OutMail
.To = MailTo
.Subject = Subj
.Body = strBody
.Attachments.Add TempBook.FullName
.Send
.Close SaveChanges:=False
End With 'OutMail

''''Clean up
Set OutMail = Nothing
Set OutApp = Nothing
TempBook.Close SaveChanges:=False
On Error GoTo 0 'Disable Error Handling.

'The above code looks like it is for all employees.
'Do they all use the Master Workbook?
Windows("Master.xlsm").Activate
ActiveSheet.ShowAllData
Selection.AutoFilter 'Replace "Selection" with Range.

GoSlow 'Reset application Properties
End Sub


These two functions are used in almost all procedures, and I am a lazy typist :D

Function GoFast()
With Application
.ScreenUpdating = False
.EnableEvents = False
.DisplayAlerts Alerts = False
End With
End Function

Function GoSlow()
With Application
.ScreenUpdating = True
.EnableEvents = True
.DisplayAlerts = True
End With
End Function

[Pancakes1032]

hahaha Sam, I said I had gotten really good at getting codes on the the internet and manipulating them to my workbooks. Thanks for the advice and info. Below your code looks much better, clearer, and cleaner!

[Pancakes1032]

This time I refactored the code my way for your consideration.
Note that this code is also based on the way I structure my workbooks.

All Employee sheets are named with their Log-In name, or (If they use nicknames as Log-ins,) a cross reference list on the VBA lists and references sheet that I can VLookUp with.

All other sheets have a 2 or 3 letter prefix in their CodeNames.

In VBA, press the F4 key to see the Properties Window, and Ctrl+R for the Project Explorer. Clcik a sheet in the Explorer. It will have two names like "Sheet1" and "(Master)." look at the Properties Window, the top (Name) lists will be like "Sheet1," and the other Name will be like "Master." In the Properties Window you can change the Code Name of a Sheet by changing the (Name) Property. IOW, The Name Property is the Tab Name and the (Name) Property is the Code Name.

Back to the Prefixes. With your project I might use "ea_" (for Employee Assignment Project,) I might Use a generic "vba_" as a prefix. I would never use "xl_" or "vb_" to at all costs avoid confusing VBA.

Since you have many employee sheets and only a few others, if you use a code name prefix, you can use simple Employee names for their sheets and use exclusionary code like


For each Sht in Sheets
If Not (Left(Sht.CodeName, 4) = "vba_" Then
'it is an Employee sheet
Call StuffToDo Sht.Name
End If
Next Sht

Anyway, here it is "My Way."

Option Explicit

Sub Send_Lilly()
'Sends a copy of Employee's work to Supervisors at end of day

Dim ThisBook As Workbook
Dim RprtSht As Worksheet
Dim Src As Range
Dim TempBook As Workbook
Dim Dest As Range

Dim TempFilePath As String
Dim TempFileName As String
Dim EmpName As String
Dim TimeStamp As String
Dim FileExtStr As String
Dim FileFormatNum As Long

Dim OutApp As Object
Dim OutMail As Object
Dim MailTo As String
Dim Subj As String
Dim strBody As String

GoFast 'Set Application Properties

EmpName = Environ$("USERNAME") 'Empname must be first

''''Set Object Variables
Set ThisBook = ActiveWorkbook
Set RprtSht = ThisBook.Sheets(EmpName) 'Or use ("Employee_" & EmpName
Set Src = RprtSht.UsedRange
Set TempBook = Workbooks.Add(xlWBATWorksheet)
Set Dest = TempBook.Sheets(1).Range("A1")

''''Set Filename Parts
TimeStamp = Format(Now, "dd-mmm-yy h-mm-ss")
TempFilePath = Environ$("temp") & "\"
TempFileName = EmpName & "'s Daily Completed Assignments " & ThisBook.Name & TimeStamp

If Val(Application.Version) < 12 Then 'XL 2002,3
FileExtStr = ".xls": FileFormatNum = -4143
Else 'Xl >=2007
FileExtStr = ".xlsx": FileFormatNum = 51
End If

''''Set Outlook Variables
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
MailTo = "Sups"
Subj = EmpName & "'s Daily Assignment Production"
strBody = "Hello," & vbNewLine & vbNewLine & _
"Here are today's cases I have touched and/or completed" & vbNewLine & _
"Thank you"



'**** ACTIVE CODE ****
'On Error Resume Next 'Uncomment when all code tested

''''Copy Data
With Src
.Rows(1).AutoFilter
.AutoFilter Field:=15, Criteria1:=">=" & Date
.Copy
End With 'Src

''''Paste Data
With Dest
.PasteSpecial Paste:=xlPasteColumnWidths
.PasteSpecial Paste:=xlPasteValues
.PasteSpecial Paste:=xlPasteFormats
Application.CutCopyMode = False
.SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum
End With 'Dest

''''Send temp book to bosses
With OutMail
.To = MailTo
.Subject = Subj
.Body = strBody
.Attachments.Add TempBook.FullName
.Send
.Close SaveChanges:=False
End With 'OutMail

''''Clean up
Set OutMail = Nothing
Set OutApp = Nothing
TempBook.Close SaveChanges:=False
On Error GoTo 0 'Disable Error Handling.

'The above code looks like it is for all employees.
'Do they all use the Master Workbook?
Windows("Master.xlsm").Activate
ActiveSheet.ShowAllData
Selection.AutoFilter 'Replace "Selection" with Range.

GoSlow 'Reset application Properties
End Sub


These two functions are used in almost all procedures, and I am a lazy typist :D

Function GoFast()
With Application
.ScreenUpdating = False
.EnableEvents = False
.DisplayAlerts Alerts = False
End With
End Function

Function GoSlow()
With Application
.ScreenUpdating = True
.EnableEvents = True
.DisplayAlerts = True
End With
End Function

Love the code! I'm definitely going to add this to my workbook on Monday and I'll let you know how it works. I do have a question regarding the log-in name. Would it be some type of special code or would I literally just use their windows log in name in the code like 'log in "lname5"? If that's the case it would be so much easier for me to use in the coding!

Thank you for the help!

[SamT]

Environ$("USERNAME") returns the string that the User logged on to the computer with.


Would it be some type of special code or would I literally just use their windows log in name in the code
You do not use their name in the code. The Variable EmpName is used instead. This is assuming that each employee's computer is emailing their own work report.

There is a programming concept called "Magic Numbers" which are values that have no meaning, such as "-4143" and "51." In the above code please replace the If...Then...Else statement with this code


If Val(Application.Version) < 12 Then 'XL 2002,3
FileExtStr = ".xls": FileFormatNum = xlWorkbookNormal
Else 'Xl >=2007
FileExtStr = ".xlsx": FileFormatNum = 51
End If

Excel 2002 doesn't have the Constant for .xlsx files, so you will have to replace the "51." In VBA, press F2, or select Menu >>View >> Object Browser. In the upper left corner, you will see two boxes, the bottom one will be empty.

Paste or type ""XlFileFormat" into the bottom box and click the Binocular, (or other search,) icon. in the right hand pane the list of all members of XLFileFormat will appear. If you click one, in the bottom Left hand corner of the Object Browser will be its value. look for a value of 51.

[Blade Hunter]

I do a few time based reports and I refuse to use application.ontime Have you considered just having the code fire as an auto_open macro then setting the spreadsheet as a scheduled task in a safemode copy of excel?

I have a VM that all mine sit on which completely removes the reliance for my machine to be on and logged in. All our daily reports fire off early in the morning before anyone is at work all as scheduled tasks.

[Pancakes1032]

That's actually not a bad idea Blade Hunter! However, this is a shared workbook that is constantly opening and closing so would that cause the code to be fired every single time it is opened?

[Blade Hunter]

sub auto_open
if Environ$("computername") = "Your VM Machine Name Here" then
'Your code here
end if


Problem solved, VM opens it it auto runs, any other machine opens it it does nothing :).

[SamT]

I have a VM that all mine sit on which completely removes the reliance for my machine to be on and logged in. All our daily reports fire off early in the morning before anyone is at work all as scheduled tasks.

When you say VM, do you mean "Virtual Machine?" A software program that emulates a computer, but is only running in the memory of a "real" hardware computer.

If I understand correctly (doubtful) that means that you have an allways on computer (probably running Windows) that has a VM Program (emulating another Windows system that has Excel installed,) and you transfer all Daily report workbooks to that Excel (running in a virtual Windows inside a "real" Windows.)

@ Pancakes,

The code in #5 is supposed to be run in each employee's Log Book. I personally would trigger it with

Sub Workbook_BeforeClose(Cancel As Boolean)
Cancel = blah 'Depends on SendLilly
SendLilly
End Sub

That should resolve all but two issues, ( at least in my imagination.)

[Blade Hunter]

My VM (yes Virtual Machine) is on one of our main servers, our servers are always on which is why I use a VM, this way I don't need my personal machine running.

IT set it up for me and sent me the RDP details to log in to it.

Only thing different from a normal machine for this purpose is that the password is to never expire, it causes problems when it tries to run reports on an expired account.

I have pretty much always set my reports like this, that way they run over holidays and sick days without impacting the business.

[Pancakes1032]

Hi All,

Sorry I'm all late to the vm discussion. Work has been pretty crazy lately, plus holiday shopping. Thanks for the code Sam, there were a couple of errors though. I'm sure you were just testing me ;)


Function GoFast()
With Application
.ScreenUpdating = False
.EnableEvents = False
.DisplayAlerts Alerts = False <---Had to remove the extra Alerts
End With
End Function



Set Dest = TempBook.Sheets(1).Range("A1")

I had to change With Dest, since Dest was only referring to the Range("A1") and the .SaveAs was erroring.

''''Paste Data
With Dest
.PasteSpecial Paste:=xlPasteColumnWidths
.PasteSpecial Paste:=xlPasteValues
.PasteSpecial Paste:=xlPasteFormats
Application.CutCopyMode = False
.SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum
End With 'Dest


@Blade Hunter,

sub auto_open
if Environ$("computername") = "Your VM Machine Name Here" then
'Your code here
end if

How would I know my computer name? Would it be my windows log in or the HP serial or product #? I'm not that pro at all this programming, it's all a facade haha

[SamT]

How would I know my computer name?Right Click the "My Computer" icon and select "Properties."


Sam, there were a couple of errors though. I'm sure you were just testing me Not really, but I can neither Compile it on Excel 2002, nor test it without data, so I just don't try and miss a lot of errors.

BUT MY LOGIC IS GOOOOD! :help

Which reminds me: Use the VBA Menu >> Tools >> Options >> Editor Tab and check everything in the Code Settings Frame, then manually add "Option Explicit" to the top of all your existing Code pages. That will now be there on all new pages

While you are there, on the Editor Format Tab, you can change the colors of various types of VBA Code Words. I changed the Identifier, KeyWord and Comment colors, YMMV.

On the General Tab, I like to check the "Break On All Errors" box.



So I've been reviewing all of your new code that I can find, which isn't that much. Is it possible for you to share all the latest versions with me?

[Pancakes1032]

Right Click the "My Computer" icon and select "Properties."

Not really, but I can neither Compile it on Excel 2002, nor test it without data, so I just don't try and miss a lot of errors.

BUT MY LOGIC IS GOOOOD! :help

Which reminds me: Use the VBA Menu >> Tools >> Options >> Editor Tab and check everything in the Code Settings Frame, then manually add "Option Explicit" to the top of all your existing Code pages. That will now be there on all new pages

While you are there, on the Editor Format Tab, you can change the colors of various types of VBA Code Words. I changed the Identifier, KeyWord and Comment colors, YMMV.

On the General Tab, I like to check the "Break On All Errors" box.



So I've been reviewing all of your new code that I can find, which isn't that much. Is it possible for you to share all the latest versions with me?

The workbooks that I attached on one of my other threads that I believe you had viewed is pretty much everything that I am using now. The only thing that has changed is the send email code. I'm now working on transitioning into more of a database project. I'm not sure which way I should go. I'm not a pro programmer like yourself, so my ability is limited. I also work for a company with limited funds, so all programs I use would have to be whatever is already available to me, which is Microsoft Office 2010. I was thinking of doing mainly Access databases since that's what I have and I can create and manage those fairly well. Is there any other approach that you feel may work better?

Also thanks a bunch for the Computer name finding advice! :)

[Blade Hunter]

How would I know my computer name?

As SamT said you can look at the computer name or you can go into the VBE, bring up the immediate window (CTRL-G) and enter this:

?Environ$("computername")

Hit enter and it will give it to you, you can then copy and paste it where you need.

Cheers

Dan

[SamT]

Lilly,

You don't have enough data to need any other Database Front end than Excel, as simple a DB Engine as it is.

Access is a good Database front end for any office that uses Data like yours does.

You will have to learn Access's Application Object Model. Excel's is so intuitive that you don't really realize what you are learning, but the Object Model is what VBA is all about.

You will need to learn basic database design as well as learning Access and Excel.

Remember what out first conversation was about? Developing a complete plan for what we were going to do.

Before we actually started to do anything at all. There is a might good reason I insisted we do that first.

Have you done that with this new system? Even if it is just you explaining it to your significant other until you see everything that needs to be done?

Realize it or not, you have actively taken over the Project Architect role as well as becoming the Lead Programmer. I strongly suggest that as Project Architect, you decide right now, what path you are taking to get this Project in production and quit dilly dallying around about whether or not your are "transitioning" to Access. Do it or don't, but decide now.

When you make that decision, show us your plans. I showed you mine:VBA code to transfer data to different workbooks (http://www.vbaexpress.com/forum/showthread.php?51020-VBA-code-to-transfer-data-to-different-workbooks)

[Pancakes1032]

Lilly,

You don't have enough data to need any other Database Front end than Excel, as simple a DB Engine as it is.

Access is a good Database front end for any office that uses Data like yours does.

You will have to learn Access's Application Object Model. Excel's is so intuitive that you don't really realize what you are learning, but the Object Model is what VBA is all about.

You will need to learn basic database design as well as learning Access and Excel.

Remember what out first conversation was about? Developing a complete plan for what we were going to do.

Before we actually started to do anything at all. There is a might good reason I insisted we do that first.

Have you done that with this new system? Even if it is just you explaining it to your significant other until you see everything that needs to be done?

Realize it or not, you have actively taken over the Project Architect role as well as becoming the Lead Programmer. I strongly suggest that as Project Architect, you decide right now, what path you are taking to get this Project in production and quit dilly dallying around about whether or not your are "transitioning" to Access. Do it or don't, but decide now.

When you make that decision, show us your plans. I showed you mine:VBA code to transfer data to different workbooks (http://www.vbaexpress.com/forum/showthread.php?51020-VBA-code-to-transfer-data-to-different-workbooks)

I am 100% transitioning my model into an Access database. I already know how to use Access, I just haven't used VBA within Access yet. I have only done basic tables, queries, userforms, and reports. I know that Access can do much more. I have already started building the foundation for the project, like what departments do what, what exactly I need Access to be able to do for those departments. I just have it in writing, I haven't actually started creating it yet. I will create a Project Architect plan and show it to you soon. I have a ton of work to catch up on first, plus holiday events. I should have it done by next week. I'll let you know, thank you!

[SamT]

I am 100% transitioning my model into an Access database. I already know how to use Access, I just haven't used VBA within Access yet. I have only done basic tables, queries, userforms, and reports. I know that Access can do much more. I have already started building the foundation for the project, like what departments do what, what exactly I need Access to be able to do for those departments. I just have it in writing, I haven't actually started creating it yet. I will create a Project Architect plan and show it to you soon. I have a ton of work to catch up on first, plus holiday events. I should have it done by next week. I'll let you know, thank you!

That is excellent news.

I am sorry to lose you, as I won't be of any help to you with Access, and I see that all the work in Excel is done but the Queries.

I am wishing you very good holidays and a very profitable new year.

Sam