Posts by ikith

    Re: Setting date variable, so I can open files in dated folders using a macro

    Quote from Kenneth Hobson;776326
    Dim dteProcess As String
      'dteProcess = "8-24-2016"
      dteProcess = Format(Date, "m-d-yyyy")
      Workbooks.Open Filename:="C:\Users\mearle\Desktop\" & dteProcess & "\act vs theo.xls"

    This seems to be pulling in the current date regaurdless of what dteprocess= is set to.

    Edit figured it out, I had to move dteprocess= to after the format set.

    Edit2: This worked like a dream, thank you so much! This is going to save me tons and tons of time on having to scroll top to bottom just to change 2 dates for the input and output. I'd donate to you if I were able at this time, but again thank you!

    I'm trying to set a date variable (example 8/21/2016) so I can open a file using a date.

    Every day I have folders named for the previous day so for example today I have a date labeled 8/24/2016 to pull and process some reports, I have the macros set up nicely but I have to change the following code in the macro to reflect the date:

    Workbooks.Open Filename:="C:\Users\USERNAME\Desktop\8-24-2016\FILENAME.xls"

    ActiveWorkbook.SaveAs Filename:="C:\Users\USERNAME\Desktop\8-24-2016 Out\FILENAME.xlsm" _
        , FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False

    What I'd like to be able to do instead of having to change both date sections is to have 1 section that I change and it impacts both dates for the open and save part of the macro. I've tried using:

    Dim dteProcess As Date
    Set dteProcess = '8/24/2016'
    Workbooks.Open Filename:="C:\Users\mearle\Desktop\(dteProcess)\act vs theo.xls"

    With no avail, however this seems to be what google search is suggesting.

    Can anyone point me in the correct direction on how to do this?

    Re: Compare GUID and Time to filter

    Quote from holycow;718730

    If you attach a sample of the export file we can make sure GUID and Time are next to each other.

    Here is a sample layout:

    As you can see GUID is in A and Time is in H and the fields between are usually populated with a lot of data. Its no problem for me to re run my query with a slight modification to --fields to have guid and time next to each other however I also have to space the data out after C, which isn't an issue at all :) as this does what I need it to do.

    Edit: I'm comparing my old way of doing this and the script and the old way is giving me 5470 results while the script is giving me 3396 curious if something is going on.

    Re: Compare GUID and Time to filter

    Quote from holycow;718699

    Provided the data is in the same order as your example, then attached will work

    His works perfect, and only requires me to adjust the output of my export to have GUID and Time next to each other, I did have to adjust it to accomidate the 107411 cells but that was relatively easy, thank you!

    I've been trying to solve this issue for a couple of hours now, either from the mongodb side or the csv side, it seems like it will be easier to do it from the csv side of things.

    What I am trying to do is filter a column called guid by another column called time. The guid field has multiple duplicates and the time is unique per guid.

    Sample data:
    GUID, Time
    6, 1
    6, 2
    6, 3
    7, 4
    7, 5
    7, 6

    The output needs to be:
    6, 3
    7, 6

    Eliminating the duplicate guids by the most recent time.

    I know I can kind of do this by sorting the guids then adding a level and sorting time by greatest then eliminating duplicates but when comparing the data I found that it actually eliminated the wrong duplicates and I had data that was older than the most recent.

    I don't mean to seem like I'm putting this work on someone else, I am also trying to figure out how to do this on my own.

    Re: $60 USD csv/xlsx to kml w/ custom icons in the kml

    Quote from Wigi;716403


    Here's a website, including a file and VBA-code.
    Probably this is what you need, but I don't know if custom icons are possible.
    You might need to choose from an existing list.

    I have almost no experience with the KML files so I'd suggest to first try this.

    Theres no website attached :\

    Re: $60 USD csv/xlsx to kml w/ custom icons in the kml

    Quote from Wigi;716400


    Is this code for a one-off exercise, or do you need to convert Excel files regularly?

    Regularly but the format will be the same each time, it's exported from a database in the same format each time, the output is CSV but I add the date and age fields manually from the formulas in the spreadsheet and usually save it as xlsx.

    See OP for updated information.

    Looking for a VBA within or outside of Excel to convert a sheet to kml with custom icons depending on a fields data.

    Sample data provided.

    The icons need to be as follows (I don't have custom ones made at the moment still working on that part on my end)
    10+ Age = 10plus.jpg
    20+ Age = 20plus.jpg
    80+ Age = 80plus.jpg
    90+ Age = 90plus.jpg
    140+ Age = 140plus.jpg
    150+ Age = 150plus.jpg

    All of the data also needs to stay in the KML as a descripction.

    Having a bit of trouble, I'm trying to take data in a sheet from SQL and send it to a MSSQL database using a script I've found some code that another user that was trying to do the same thing used (Located here:…92-4950-8844-9e65f79c9850) and I have made adjustments so it can get to my server, but when I run it I either get:
    "An unexpected error occured
    Number: -2147217900 Desc: Could not find stored procedure 'dbo.headcountstest" or I get:
    "an unexpected error has occured
    Number: -2147217887 Desc: Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done."

    When removing the rest and using a different script to test the connection, the connection says adOpen, which I assume mean everything is good.

    Here is the script I'm using:

    Any help is greatly appreciated.

    I'm stuck, I have a spreadsheet with date spanning across the top and vendor as well as count data running down the side for each day.

    What I need is to combine the date and time and move the data accordingly.

    For example I have this data:

    And I need to move the data like this:

    These are just examples, the real sheet is about 1 year and on each day it runs from 09:00 to 04:00.

    Re: Compare a row of cells to another worksheet and copy data depending on cell match

    Re: Compare a row of cells to another worksheet and copy data depending on cell match

    Modified my code a little, it's actually not working gives an error of:
    Run-time error '1004':

    Method 'Range' of object '_Global' failed

    Code is now:

    Re: Compare a row of cells to another worksheet and copy data depending on cell match

    No the entire row doesn't need to match, just the serial number.

    So what is supposed to happen is if a serial number on rocketNew matches a serial number on rocketOld copy the CEMS ID over to rocketNew.

    Edit: I phrased that wrong, it should only be checking the serial numbers, even if there is data that is the same in the row, I only want it to check serial numbers because the other data will change.

    Edit2: So I've kinda adapted this script to highlight what it finds as matches from rocketOld to rocketNew possible to adapt this to look once cell over from the matches and paste the CEMS ID to rocketNew instead of highlighting?

    What I'm trying to do is make a script that depending on what button pressed will compare a row on two sheets (For example rocketOld and rocketNew) then depending on if it finds a match will copy data from rocketOld to rocketNew from one cell to the right of the compared row to the right side of the row that was matched.

    I've tried googling around to find a script similar that I can adapt but no luck.