Posts by homegrownandy

    I have a spreadsheet with date values in row 1 and in column A i have a list of jobs. We have thousands of entrys on this spreadsheet, which shows work we carried out and when.

    In every cell with a value (not blank) I would like for that to be pasted on a new sheet, with the accociated date (row one) and associated job name (column A) to be pasted in the cells next to it.

    It would take me weeks to go through all of this data manually, with each one checking dates and names. So if i have this in a table with the name/date/note all in a list format I can upload them to the database.

    The dates are all now text values, so copying them isnt an issue.

    Hope someone can help me put together some code.
    Edit: This is some sample data just showing what exists and what I want. I would like the new data to be on a new sheet, and in this example the data is far less than whats on the actual data.


    Re: Method or data member not found

    Thanks for the replies, hope you had a good weekend.

    Firstly I paid someone a few months ago to make this code, and it runs fine in excel. I cant get hold of the guy now. I do have some limited VBA knowlege but you can probably assume I have none.

    I didnt want to fill up the thread with code that wasnt causing the error but it may help if you see everything.

    This is from the excel document. (without the search functionality for site name)

    In access, I'm using debug print to check the value of the folder its searching and thats working. (this took me a few hours in itself) This is whats assigned to the button click.

    Then this is module2 on the database:

    Option Compare Database
    Public Const SW_SHOWNORMAL As Long = 1
    Public Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" _
      (ByVal hWnd As Long, ByVal lpOperation As String, ByVal lpFile As String, _
      ByVal lpParameters As String, ByVal lpDirectory As String, _
      ByVal nShowCmd As Long) As Long

    Pike: I'm not 100% how this works but im assuming that value is uupdated when the keyword test is found in that folder.

    1. If thats not declared in the code shown here then no. I wish I could know the answer to the question you ask.
    2. I thought exactally the same, but when it wasnt breaking I just left it like that.
    3. I'm happy to use subs or any method if it works. This is what was supplied to me.

    Note: Even leaving the code exactaly the same (without the folder search.. but with module2) the error remains in access.

    Compile error: Method or data member not found.

    Function Print_TestPlan(strTestPlanName As String) --- this is highlighted yellow

    Application.DisplayAlerts = True --- this is highlighted blue

    Ive messed about referencing the DAO object library instead of the access one. Nothing seems to resolve the issue.

    The code I have shown does work for the survey but not the testplan. I have no idea what i can do to fix this now as I've looked at everything I can think of/have come accross searching the internet.

    Could someone help me here?

    This code does work "as is" when run through excel.

    Thanks in advance


    Re: Using multiple variables in VBA

    I was concerned about the formatting. I didnt actually do this code Im a bit clueless, but ill have a play and post an update on monday. This is what i want to achieve but usually I just break code.

    .Body = "Hardwired signals: " & ws.cells(lngRow, "I") Notes: & ws.cells(lngRow, "j")

    I have some code that imports calendar events to outlook.

    I would like to include within the body text a variable or possibly multiple variables depending what information we recieve.

    Heres the code:

    i want to change this:

    .Body = "Hardwired signals: "

    I want to keep the .Body = "Hardwired signals: " but then also include information from cells next to it. (column I and J for example.)

    I hope someone can help. Thanks.

    Re: vba to send email

    Bit of a long shot here but maybe you know. When it sends this way it comes on my calendar as accepted. but on the shared calendar its no response. If i click the send button myself it works on both.

    Re: vba to send email

    Thanks for the reply.

    Apparently it does need to open first then send. Thanks for the help though :)…ting-request-using-rules/

    Im using the code here. Or specifically im using:

    Thats all well and good. But how do I send the response? haha


    I have a document we use for generating estimates for the customer, I want to automate some of the copy/pasting and the PDF creation.

    As you can see in the attachment. The way this all works is fine, but im open to suggestions to improve it. The thing I need help with is the summery page. Where it says line 1, and line 1 cost breakdown, I want that information to be populated when a button is pushed.

    *A "line" on the summery page represents a sheet in the document. 1/2/3 are examples of the sheets but they could be named anything.
    * Information to be copied over is anything selected in the table, from columns B,C,D and J (these have the same title as the summery page.)
    * Too add to confusion; there are two sections to the table. We have parts and Labour, all of these need to be copied over if there is a selection in that row. Using "Qty" column D could be a good check. Anything in this column with a value is what we want moved.
    * I've just thought of this now, but moving cost index to sheet 2 could help (so it looks at sheet 3 onwards and this page doesn’t need to be excluded).
    *where we have on the summery page "Line 1 Cost Breakdown" this can say "sheet name Cost Breakdown" (whatever that sheet name may be.
    *We are not limited to three sheets, there may be a lot more. Once line one information is added i would like for it to add line 2 and so on.

    I hope I've explained that clearly enough to get some advice. Or even better If someone has the time to put something together for me. I would be very greatful.

    Thank you. Andy,

    Re: Uploading incoming data to outlook calendar.

    Haha, the first page doesnt crash mate, that imports them within seconds. Thats what i was after, its the second page with apointments that crashes. Microsoft home and business 2013 is what im using, I should have said.

    "microsoft outlook 16.0 object library" is missing, doing as you sugested didnt fix it. I'll google is a bit and see what i find. I spent a few hours trying to figure out why no VBA would work at all with outlook. Same on another machine im using, but after deleting a VBA file in outlook they seem to be working okay again.

    Thanks for the help so far, Its crazy what you can do in half an hour.

    Re: Uploading incoming data to outlook calendar.

    Thanks a lot for that, the way I had it was making it cross over two days and was also showing time in the subject. This is a lot better than my attempt. Using the other code you gave, I was able to manipulate the categorys new sites went into. Now I get a message saying "Error in loading DLL". If you have done this to protect your code thats fine, but could you just change that category to 'red' that way its easy to see anything new for us to deal with.

    Also i see you made a second page showing existing apointments, Its a good idea but when clicked outlook crashed. No error is shown. I dont mind not having this though.

    thanks so far! its amazing haha.

    EDIT: also having reminder set to none would be good. Or being able to see the options you gave me in the last one.