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:

Public Sub accessDB()    Dim cnn As ADODB.Connection
    Dim rst As ADODB.Recordset
    Dim data As Variant




    Set cnn = New ADODB.Connection
    Set rst = New ADODB.Recordset
    
    cnn.Open "db", "user", "pass" '  I'll be using the appropriate ones
    rst.ActiveConnection = cnn
    rst.CursorLocation = adUseServer
    
    rst.Source = "SELECT * FROM Table_Name"




    rst.Open  ' ERROR here
    Cells(1, 1).CopyFromRecordset rst
    rst.Close




    Set rst = Nothing
    Set cnn = Nothing
End Sub

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

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)

Re: Reverse Order Of List

Oh, duh. I'm an idiot.

Simple one, probably: I want to mirror cells vertically, so if I have:

7
6
5
4
3
2
1

and want:

1
2
3
4
5
6
7

How would I do that? Thanks.

Re: Get Closest Value In List

@Parsnip

That works! Thanks a lot, my mind was toast.

Sicarii

VLOOKUP is easy, but it doesn't do what I want in this case without a lot of extra work

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.

Re: Combo Boxes

Hmm, I guess I missed the list box before. That does exactly what I want, though. Thanks a lot!

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

Re: Mirroring Two Sheets

That's cool. Is there a way to get it to work if the ranges aren't the same, i.e. Sheet 1 A1:E2 and Sheet 2 C2:G3 ?

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?

Re: Array Multiplication

Yeah, I had some good tutelage. . It sure beats using something like A2*SUM(B10:B14)+.... 14 times over. Thanks again

Re: Array Multiplication

I used SUMPRODUCT for each row/part number times the matrix of 4 year production

Re: Array Multiplication

I got it with SUMPRODUCT, thanks a lot! Very awesome

Re: Array Multiplication

This is a generic stripped down version (there's more part numbers, assemblies, etc.) but the layout is the same, I'm messing around with SUMPRODUCT

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.