Recent content by rmikesmith

Diane, In the VB Editor, press F2 to display the Object Browser (or View|Object Browser). Select the object (left pane) then a property, method or event (right pane) then right-click and Help for help on that item. Regards, Mike

Diane, Try Set wkbTarget = XLApp.Workbooks.Open(Filename:=sPATH & sFILE, UpdateLinks:=True) or set UpdateLinks to False, as the case may be. Regards, Mike

I added these for my testing only since I didn't have access to your environment. I had to assume your code was assigning values elsewhere, hence my instruction to remove my assignments in the procedure. I'm not exactly sure what you mean by this question. The path is not used to detect an...

I suppose *easy* is in the eye of the beholder, but Chip Pearson, MS Excel MVP, has a very good explanation + working code to manipulate objects in the VBE, including a specific example to list all modules in a VBProject. Here is a link to the page: http://www.cpearson.com/excel/vbe.aspx...

DianeA, Just add an On Error Resume Next statement before the GetObject line. I only tested my code with Excel already running, so missed it. If you are using other error trapping be sure to reinstate this after the GetObject/CreateObject section. Regards, Mike

No, when you set a reference to an external library (ADO in this case), you have access to all of its properties/methods. An ADO reference I use indicates the following: Note: This property (Sort) can only be used if the CursorLocation property is set to adUseClient. Is that the case in your...

Diane, I think you were on the right track but didn't go quite far enough. The main issue with your IsOpen function is that no actual reference to an instance of Excel is set. I was able to duplicate that execution skips the entire loop. I have rewritten your procedure to streamline things...

Or Sub SaveSheetsToFile() Dim ws As Worksheet Dim MName As String Dim MDir As String For Each ws In ActiveWorkbook.Worksheets MName = ws.Name & ".txt" MDir = ActiveWorkbook.Path ws.Select ActiveWorkbook.SaveAs Filename:=MDir & "\" & MName, FileFormat:= _ xlText...

Is the worksheet name a valid filename? Are there write-access restrictions on the destination drive/folder? Regards, Mike

bodmin, Any feedback on HughLerwill's suggestion, which should do what you want? Ah, the cruelty. Regards, Mike

Try single-stepping through your code to if this error is occurring first time through the loop or otherwise. Or, select Debug when the error occurs and determine which worksheet is being referenced. Either way, inspect your variable values to make sure they are valid. I just ran a modified...

Try this instead: Private Sub CommandButton2_Click() Dim ws As Worksheet ThisWorkbook.Save For Each ws In ActiveWorkbook.Worksheets MName = ws.Name & ".txt" MDir = ActiveWorkbook.Path ws.SaveAs Filename:=MDir & "\" & MName, FileFormat:= _ xlText, CreateBackup:=False Next ws...

I don't believe those object libraries are redistributable so at the very least, there would be legal issues, if not technical ones. So, yes, I think your only options are upgrading or selecting a different approach. If you explain what your macro currently does, someone can probably point to...

tranpkp, Skip & Geoff raise a valid point. However, I present the following corrected function if for no other reason than I felt compelled to make it work: Function ColumnNumToAlpha(ByVal ColNum As Integer) As String Dim sTmp As String Dim iDiv As Integer Dim iMod As Integer If ColNum <...

PHV, Nice one. I'll tuck that away for future reference. tranpkp, Here is a function that doesn't use Excel's object model but does require integer division (\), modulus (mod) and the Chr() funciton: Function ColumnNumToAlpha(ByVal ColNum As Integer) As String Dim iDiv As Integer Dim iMod...