Recent content by Purdue2479

Below is the solution I came up with. Option Compare Database Option Explicit Dim oBook As Excel.Workbook Dim oSheet As Excel.Worksheet Dim oApp As New Excel.Application Dim lRow As Long Dim lastrow As Long Dim sParts() As String Public strCriteria As String Public strStore_Type As...

I am using the below code to insert a recordset into Access from Excel, insert total rows at each change in column A, and apply greenbar formatting to the specified range of data. What I want to do is to insert sum calculations into the subtotal rows, but do not know how to dynamically sum each...

Thx. I ended up using the following: Function Get_Yr() Dim i As Integer Dim strText As String Dim strYr As String Dim iPositions() As Integer Dim iStart As Integer Dim iLoop As Integer strText = strCriteria iLoop = Len(strText) - Len (Replace(strText, "*", "")) - 1 ReDim iPositions(iLoop)...

I am trying to use the following code to parse out the year from a string, but only want the first and last instances. Example: I would want the result to be "2007-2009" from the string below "Tbl_Payment_YTD.[Rebate Period] Like " & Chr(34) & "*2007" _ & Chr(34) & "Or...

The code was erroring on the following With statement: With oApp.Selection.Borders(xlEdgeLeft) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With I had to add a reference to the oApp object in each With statement. I also...

I finally got it to work. Below is the modified code. Option Compare Database Option Explicit Dim oBook As Excel.Workbook Dim oSheet As Excel.Worksheet Dim oApp As New Excel.Application Sub Export_Qry() Dim db As DAO.Database Dim rs As DAO.Recordset 'Set db =...

I am using the below code to export query results from access into excel and format the spreadsheet. The code is causing an error after the first run. I've read the microsoft article http://support.microsoft.com/default.aspx?kbid=178510, but I am still at a loss. The code errors on the first...

After doing some testing, I discovered that the export from access to excel does not modify the formatting in excel. Below is what I used. Sub Export_Qry() Dim db As DAO.Database Dim rs As DAO.Recordset 'Set db = DAO.DBEngine.Workspaces(0).OpenDatabase( _ "C:\database.mdb")...

I am trying to use the below code to export a query from access to excel. I would like to modify the code to paste the data "as values" into an already created excel workbook with formatted columns. I'm not sure how to point to the workbook or keep the excel column formatting when exported...

JoeyY, That worked like a charm! Thanks!!! I've been stuck on this for over a day now. Thanks to everyone else that also responded. *Just noticed that it will not work when making multiple selections in the list box. :-(

I can't even get the below to work when just running the query manually using ReturnStrCriteria() as the criteria. Function ReturnStrCriteria() As String strCriteria = "Like *" ReturnStrCriteria = strCriteria End Function

When I add a watch to strCriteria it appears as "Like "*2007"" Yes, the Rebate_Period field also includes the month (i.e. January_2007) ----- I tried using the parentheses and singles quotes, but I am still unable to get the query to return results. Is this not possible using a function?

I am trying to pass a global variable to criteria in a query using the below code, but the query is returning no results. I have the function ReturnStrCriteria() included in the query's criteria. When I manually put the criteria into the query (Like "*2007"), it returns results. Not sure what...

I ended up taking a different route. Below is the solution I used. Function Get_Signature(ByVal sFile As String) As String Dim fso As Object Dim ts As Object Set fso = CreateObject("Scripting.FileSystemObject") Set ts = fso.GetFile(sFile).OpenAsTextStream(1, -2)...

I am trying to use the below code to send an email automatically with the signature included. Any suggestions on how I can merge these two pieces of code? Currently, When I run them, it sends the email with the attachment but I cannot get it to include the signature in the body. Thanks. Sub...