Posts by Zygomatic

    Re: Recover Add In Modules?


    No messages for the add-in. I was worried about 2 other workbooks that were corrupted. I had to disable all macros and copy all sheets and modules to new files. I thought I was back up and running at that point. Then I think I tried to run some code in the add-in and Excel was complaining that it didn't exist, so I checked the add-in, but there were no modules in it at all, just one sheet object and ThisWorkbook object.

    Re: Recover Add In Modules?


    Good point, I never thought a crash would wipe out just the modules of an add-in. All the other add-ins are untouched.


    All cloud storage sites are blocked at work.

    Re: Recover Add In Modules?


    Yeah an Excel backup would be too convenient. It boggles my mind because now most Microsoft programs save a bunch of temp files and stuff, but I can't find a shred of code from the add-in. As far as a personal backup, hindsight is 20/20. I've started to rebuild and I've scheduled a backup daily. Usually everything I do at work is on a network drive and is backed up anyway, but of course the Add-Ins have to be saved locally. I haven't sent the entire add-in, it's mostly things I use. I did find some snippets, but not close to what I had.

    I was working with Excel last week and as Microsoft programs tend to do, it stopped responding. I restarted it and got stuck in an insanely long loop of Excel trying to recover workbooks and crashing. I recovered my workbooks as far as I can tell, but I noticed an Add In I created no longer has any modules in it, and the file size is down to 7 KB. I can't find any functions or subs or modules I had in there (many many months of creating/collecting, etc.). Many of my workbooks depend on the modules in that Add In. Does anyone know of a way to get the data back? Where Excel may have backed it up, etc.? Anyone that could help will save me a lot of grief. Thanks.

    Re: Cross Reference Excel Sheet To ODBC


    I didn't follow at first, but some Googling has gotten me a bit further. It seems like I'm connecting, but I can't seem to access either table in there. I get an error saying the object doesn't exist referring to the table name. I used the same names that the tables are named in Access when I usually access them.


    I've got something like this. I know it's not fully correct yet:



    Any pointers? Rookie mistakes? Thanks.

    Using Office 2007.


    I have a very convoluted task to do and I've automated some parts, but I'm always looking to do more. The main part I'm trying to figure out now is I have an Excel sheet with part numbers. Our usual process is to import the table into Access and cross reference it (join the tables via query) with 2 tables on an SQL server via ODBC, then export it back to work the data in Excel. I'm just wondering if there's a way to automate this. I'm not too familiar with Access at all, and I could probably kludge something together if Access had a macro recorder. I use a similar process to join 1 table to another later, but they are local files (I'm sure there's a way to do this in Excel, but I don't know of one yet). Any help anyone has is greatly appreciated. Thanks!

    Re: Excel Save As Macro with file name derived from date in cell


    Code
    Sub SaveIt()
    ThisFile = "MechanicHours_" & Format(range("y4),"yy-mm-dd")
    ActiveWorkbook.SaveAs Filename:="C:\Users\Front Desk\Documents\Mechanics\" & ThisFile
    End Sub


    What kind of error are you getting? You are missing a closing parenthesis for your Format function (added in code above)

    I have a list of standard resistor values on a separate tab and I want to grab the closest one (either lower or higher, whichever is closest). If I calculate a number to be 8.9, I want it to grab 9.1 instead of 8.2 (which is what it's doing with VLOOKUP). Is there an easy way to find the minimum distance or something? Thanks.

    I want to create a form and use VBA to make a combo box list all the sheets in a book. Is it possible to select multiple entries like in HTML? I would like the user to be able to select the sheets they do not want to delete before a macro runs. Thanks

    Is there a way to mirror two ranges of data on different sheets - i.e. if I update one sheet, it updates on the second, without using formulas, macros or anything overly complicated?

    I have a parts list of screws in a bunch of different assemblies. Assemblies are across the top/columns and the part numbers are listed vertically in rows to create a matrix of the number of parts for all assemblies. I also have a list of how many assemblies are to be produced in the next 20 years. I want to calculate how many of each screw will be required in the next 4-, 8- and 12 years. I just calculated it manually by summing the sum of 4 years' production times the number of screws for each assembly, but this turns out to be a nasty long formula. I know there has to be an easier way, but anything I've played with doesn't work correctly.