Posts by QBPaladin

  • Copy & Update Rows To Another Worksheet

    Re: Copy & Update Rows From One Sheet To Another When Saving


    Hi Mac. After moving the macro into my real worksheet, I'm running into a problem...


    The real sheet has additional columns in it. I think that's making the Offset functions not work correctly. New lines on the order page are moving over fine, but updated lines are being treated as new, and I can't tell which columns are being checked looking at your macro. Can you walk through an explaination of what the loops are doing, especially how the Offsets are set up?


    For-next loops I can kind of figure out from my Applesoft basic days (I'm old), but I've never seen a Do-while loop.


    Also, it's pretty slow on my real worksheet. I have over a year's worth of historic data on it already, and I think the loop is looking though all of it for changed rows. How can I modify the macro to get the last date entered on the Orders page and restrict the loop on the Historic page so it only looks back 30 days?

  • Copy & Update Rows To Another Worksheet

    Re: Copy & Update Rows From One Sheet To Another When Saving


    Quote from Macropheliac

    Take a look at the attachment. Is that what you need? If this is it, then I'll explain how to run the code when saving.


    Mac


    Mac, you're a genius!


    Everything seems to run great except one thing. When the data is wiped on the Orders page and the macro runs with a blank sheet (which shouldn't ever happen...but you know it will once end users start working with it), it copies the header row over to the Historic page. Other than that, if you can explain how to make it run when saving, you've solved my problem!

  • Copy & Update Rows To Another Worksheet

    Re: Copy & Update Rows From One Sheet To Another When Saving


    Here's a sample with junk entries, but it shows what the columns are and what the sample data looks like. In this sample, the data for February 1 exists on both the Orders sheet and the Historic sheet (had to change the sheet name because History by itself was a reserved word), but the data for February 2 is only on the Orders sheet.


    I need a macro that will copy all the new rows (February 2, in this case) to the Historic page, as well as updating any existing entries on the Historic page that have changed on the orders page (by having the Ship Date filled in, for example). Of course, the macro has to be able to get the new entries each day, not just February 2.

  • Copy & Update Rows To Another Worksheet

    Hopefully someone can point me in the right direction, since I'm sure this problem has been asked to death already, but I just can't find the answers...


    I have 2 worksheets in a workbook: "Orders" and "History". Both sheets contain the exact same columns. The "Orders" page data gets wiped clean at the start of each month. What I'm looking for is a macro solution that will copy any new rows and update any old ones that have changed on the "Orders" page to the "History" page, so we can keep a running history of sales. Ideally, I'd like the macro to fire only when performing a Workbook Save or Workbook Save As.


    "History" is in date order, so adding new entries from "Orders" should be easy, just by adding rows to the bottom. Updating existing rows will be harder, because I want it to update if the row has changed but not be deleted or changed when the "Orders" sheet is wiped clean the 1st of the month. Any ideas?


    Additional:


    The columns in both sheets are:


    Order Date
    Ship Date
    Order Number
    Customer
    Item
    Qty
    Amount


    Because each item gets a seperate line, there will be rows that have identical Order Date, Ship Date, Order Number, and Customer entries, so I suspect I will need to compare multiple columns when deciding if one has changed (the only column that *should* change after initial entry on the orders page is the Ship Date, which would initially be left blank when the order is first entered).

  • Passing date variables to SQL

    Re: Passing date variables to SQL


    I am convinced it is a date formatting problem, but I'm not sure how to deal with it.


    The ODBC driver is configured to treat any column heading ending with "DATE" as a date, so the Query Builder won't let me put anything but a validly formatted date in the Parameter. That would seem to preclude the possibility of converting the user-entered date into the correct format and passing that to the Parameter.


    So, if the date formated in SQL as {d '2005-05-01'} works hardcoded, is there not some way using VBA to take the user-entered date from a cell and convert it into a text string formatted as above and placing it into the query in the correct location? Essentially, it would be building the SQL statement each time a macro is run.

  • Sorting Rows in Pivot Table

    Re: Sorting Rows in Pivot Table


    Is there a particular reason you are using VBA to handle the sort rather than the PivotTable's sort feature? Right-click on the column heading in the pivottable you want to sort by and select Field Settings-Advanced and pick your sort order.


    I'm sure there is a way to use VBA to do it, but if you can use the built-in feature, that seems like the simplest solution.

  • Passing date variables to SQL

    Re: Passing date variables to SQL


    SQL
    SELECT IC_ITEM_LDGCARD.TRANS_DATE
FROM IC_ITEM_LDGCARD IC_ITEM_LDGCARD
WHERE (IC_ITEM_LDGCARD.TRANS_DATE=?)
ORDER BY IC_ITEM_LDGCARD.TRANS_DATE


    Ok, I pared it down as you suggested and the SQL looks like you are saying it should look. Unfortunately, I am still getting the Conversion Error after entering the date into [xxx].

  • Passing date variables to SQL

    Re: Passing date variables to SQL


    I'll try it in the morning Carlmack. Without the ODBC and data store at home, I can't mess with it. Let me see if I understand what you're asking me to do though...


    I should Query for just the TRANS_DATE field and only use one parameter, like [StartDate] and have it query for >=[StartDate]?

  • Passing date variables to SQL

    Re: Passing date variables to SQL


    Quote from carlmack

    After putting the parameter in and before returning to Excel can you press the SQL button and cut and paste the SQL code here.


    SQL
    SELECT IC_ITEM_LDGCARD.TRANS_DATE, IC_ITEM_LDGCARD.ITEM_NUM, IC_ITEM_LDGCARD.WAREHOUSE, IC_ITEM_LDGCARD.QTY_CHANGE
FROM IC_ITEM_LDGCARD IC_ITEM_LDGCARD
WHERE (IC_ITEM_LDGCARD.TRANS_DATE Between [StartDate] And [EndDate]) AND (IC_ITEM_LDGCARD.TRANS_TYPE='P') AND (IC_ITEM_LDGCARD.WAREHOUSE='SC')

  • Passing date variables to SQL

    Re: Passing date variables to SQL


    The data source has the date stored in 2006-05-01 format, including the dashes.


    Edit: Well, I say that. When I view it using Report Writer, that is how the data appears. However, the ODBC is set to convert from SSIJ format, so it may be stored in a Julian format and Report Writer's data dictionary is also converting it to that format before I see it. Using vi to look at the data file on the Unix server, it appears to be stored in some kind of Hex format.

  • Passing date variables to SQL

    Re: Passing date variables to SQL


    Carlmack - I have tried all of the following:


    5/1/06
    5/1/2006
    05/01/2006
    2006/05/01
    20060501
    May 1 2006
    May 1, 2006
    38838 (Julian date)


    All produce the same error except the Julian date, which it does not accept as a valid date entry.


    Batman - I changed the Parameter to the "Customer Name" column and called the Parameter [CustName]. The query executed, but it did not prompt me to enter a value for the Parameter at runtime.


    Edit: Changed the Parameter to [CName] and it prompted properly. Not sure why [CustName] failed, but yes, it appears other Parameters do work and only the date is causing an error.

  • Passing date variables to SQL

    Re: Passing date variables to SQL


    It is the built in Query Editor, not an add-on. Following Batman's directions isn't working because I can't get back to the sheet after changing the hard coded dates to parameters. At that point, it prompts me to enter the dates when the query is executed, which I do, and it gives me the Conversion Error, dropping me back into the Query Editor.

  • Passing date variables to SQL

    Re: Passing date variables to SQL


    Well, I got as far as it prompting me for the variables. After both variables are entered and I tell it to return data to Excel, it gives me a "Conversion Error". Likely it is having an issue with converting the date formats, something having the #'s to either side of the hard-coded dates took care of.


    I think you're on the right track, but we're not there yet.


    Clicking for Help on the error only tells me it's a Driver Error, with no other useful information.

  • Passing date variables to SQL

    I am trying to write a query to pull data using Basis ODBC from our accounting system into Excel. I want to limit the results to a specific range of dates. I can hard code the start and end dates into the Critirea in QueryBuilder and get the results I want, but I would like to enable my users to enter variable Start and End dates in cells in the Workbook so they could select ranges at runtime.


    I currently have a StartDate named range in cell C2 and and EndDate named range in cell D2. The data columns would contain fields for "Order Date", "Customer Name", "Order Total" and the like. I want to be able to limit by "Order Date" using the StartDate and EndDate variables. Any help would be appreciated. If you post code, please comment it fully. I am a very novice SQL user.

  • VBA : Creating a pop-up or e-mail warning

    Thanks for the suggestions.


    MHabib: This still leaves me with the same problem, it is dependent on someone going and looking through the pay list, which doesn't happen every day. I want something that is independent of that, either a popup or e-mail that is triggered when the file is opened.


    Roy: Ok, I can put the addresses in Excel fields, but how would I access them in my script?


    shades: I probably could use an Outlook form somehow, but I know just enough about Outlook to set up my spam filter and that's about it. I really need an answer that is in Excel if possible.