Posts by bigblu89

Re: FileSystemObject: User-Defined Type Not Defined

Thanks, that fixed it for 2 of 3 macros. On the third I am getting another error message: "Run-time error 424: Object Required." This macro worked just fine before I imported the other 2 macros referenced above into this workbook.

Re: Macro Fails After Moving To Different Workbook

Remember that the macro fails at the sixth line:

Dim FSO2 As New FileSystemObject

This is before the line you reference above. Why would the program hang at the sixth line if the problem was later on at the line you reference? In other words, I hit "Debug" when the error occurs and it highlights the sixth line. Isn't this the start of the problem?

Re: Macro Fails After Moving To Different Workbook

Quote from Macropheliac

Well, the first thing I notice is dName2 should be declared as Range and not as String. Try that and see what you get.

I changed it and got same error. Note that it worked fine before so I am curious why it should be "range" rather than "string"?

Re: Macro Fails After Moving To Different Workbook

Okay, here's the code. Also note: All 3 macros fail for the same reason: "Compile Error: User-defined type not defined."

Private oApp2 As Object


Sub Update2() 
    Dim dPath2 As String
    Dim dDest2 As String
    Dim dCondition2 As String
    Dim dName2 As String
    Dim FSO2 As New FileSystemObject
     
    dPath2 = Range("Source_Folder")
    dDest2 = Range("Destination_Folder")
    dCondition2 = Range("DAT_Picture")
    dName2 = Range("DAT_Ppt")
    
    FSO2.CopyFile dPath2 & "images/" & dCondition2, dPath2 & Range("DAT_Link")
  
    Set oApp2 = New PowerPoint.Application
    oApp2.Visible = True
     
    With oApp2 'Open PowerPoint presentation as defined by path/name variables
        .Presentations.Open Filename:=dPath2 & dName2, ReadOnly:=msoFalse
        .ActivePresentation.UpdateLinks 
        .ActivePresentation.Save    
        .ActivePresentation.Close   
    End With


        FSO2.CopyFile dPath2 & dName2, dDest2 & dName2 


    oApp2.Quit
    
    ' Clean up
    Set oApp2 = Nothing


End Sub

Re: Macro Fails After Moving To Different Workbook

I can post it but first here are some more details.

The Subroutine and the 2 subs called as shown above were in WB1.xls and worked just fine. They had a worksheet (WB1Sheet1) which had a formula which was linked to WB2.xls.

To simplify matters, I moved the 2 modules and into WB2.xls as well as WB1Sheet1. This is when I got the error running the code shown.

So do I need to post all 3 modules?

I had a working subroutine which I moved to another workbook (rather than linking to it and having both workbooks open at the same time).

Now I get "user-defined type not defined" when it runs. Here is the beginning of the code:

Global oApp As Object


Sub UpdateForecasts()
Call Update1
Call Update2
End Sub
 
Sub Update1()
    Dim dPath As String 
    Dim dDest As String 
    Dim dCondition As String
    Dim dName As String 
    Dim FSO As New FileSystemObject

The last line of code is where it hangs.

Re: Rename Files & Copy Them

I did a quick search for "FileSystemObject" and found an example which fixed my error.

[COLOR=blue]Dim[/COLOR] FSO [COLOR=blue]As[/COLOR] [COLOR=blue]New[/COLOR] FileSystemObject

I then replaced "FileSystemObject" with "FSO" and it worked fine.

Thanks to Dave and others for having this great forum.

Re: Rename Files & Copy Them

I am using the FileCopy method and am not sure what to use as the FileSystemObject parameter. I am getting run-time errors (424). Here is my code:

Global oApp As Object
 
Sub UpdateTomPpt() 'opens PowerPoint ppt and run a macro...
    Dim dPath As String 'Folder of presentation.
    Dim dDest As String 'Destination Folder.
    Dim dName As String 'Presentation name
     
    dPath = Range("Source_Folder")
    dDest = Range("Destination_Folder")
    dName = Range("Tom_Ppt")
    
   'Open PowerPoint and make visible
    Set oApp = New PowerPoint.Application
    oApp.Visible = True
     
    With oApp 'Open PowerPoint presentation as defined by path/name variables
        .Presentations.Open FileName:=dPath & dName, ReadOnly:=msoFalse
        .ActivePresentation.UpdateLinks 'Update links to Data_Acquisition.xls
        .ActivePresentation.Save    ' Save the presentation
        .ActivePresentation.Close   'Close the presentation.
      
    End With


        FileSystemObject.CopyFile dPath & dName, dDest & dName 'Copy Ppt to shared folder


    ' Clean up
    Set oApp = Nothing
End Sub

Thanks.

Re: Linked Images Won't Update

In doing a bit more research, I figured out that it won't work because OLE Server is not installed with Office 2003.

So I used Insert | Picture | From File and changed the Insert button to Link to File. This worked once I had changed both images (the old one and the replacement one) to 1024x768. I also found the FixLinks utility from http://www.pptools.com helpful.

I want to change an image and have the new image appear in a PowerPoint slide. I tried this and it does not work:

I selected and copied a jpg file from Windows Explorer: sport.jpg

Inside PowerPoint, I selected Edit | Paste Special | Paste Link | Package Object to insert sport.jpg into the slide.

After closing the presentation, I copied another file and named it sport.jpg in the same directory. When I reopened PowerPoint the previous sport.jpg appeared. I also noticed that the Edit | Links list excludes this picture from the list (only links to Excel cells appear and they work properly).

Any thoughts on why this is not working would be appreciated. Thanks.

Re: Rename Files & Copy Them

Thanks Dave. Either one will do the trick. Just a few days ago I downloaded the help files from Microsoft and now I put them to use. Thanks!

I have a formula which results in a sport name ("football", "baseball", or "basketball") as the cell contents.

I have image files called: football.jpg, baseball.jpg, and basketball.jpg.

I want my Excel macro to copy the selected sport's file to another folder and rename it "sport.jpg.". Like this:

Source folder: c:\sports\baseball.jpg
Destination folder: c:\selection\sport.jpg

Any ideas on this? Thanks in advance.

Re: Enable Auto Refresh Of Web Queries When File Opened

Oops. I just realized that I need to input all the domain names inside the code. Just proves why I don't call my self an expert. http://www.ozgrid.com/forum/images/smilie/smile.gif

Re: Enable Auto Refresh Of Web Queries When File Opened

I tried the code suggested above and it did not work. It still prompts me to "[COLOR="Red"]Enable Automatic Refresh[/COLOR]"

In addition, it prompts me to [COLOR="Red"]Enable Macros[/COLOR] which would remain as a problem after I fix the macro to allow autorefreshing without a prompt.

Any other ideas out there?

Re: Enable Auto Refresh Of Web Queries When File Opened

Thanks for the macro code...I'll give it a whirl now.

And thanks to the other guy for reminding to change my security back to Medium!

-Richard

Hi!
I have a special use PC which will power a projector 24/7. When it reboots I want it to load Excel, open a spreadsheet, and then connect to the web to get data using [COLOR="Red"]web queries[/COLOR].

My problem is that I get a dialogue box each time the spreadsheet is opened: "[COLOR="Red"]Enable Automatic Refresh.[/COLOR]" I need to remotely reboot the PC from time-to-time so I can't click the button.

I have set my security levels to low and that does not help.

Thanks,
Richard

Re: Excel to Word very slow using DDE

Thank you very much. I tested your file and it works very quickly as advertised. Then I imported my data table into the workbook...40 mb's...and the program quickly bogged down as before. I am still using your test file; I just turned a 55kb workbook into a 40mb pig (or should I say hog?)

The data worksheet is 40mb: 26 columns x 365 rows and full of =index and =Vlookup formulas. 7000 cells and each with =IF's so that the cell contains "" if the looked up value is null. One column links to a worksheet where each entry is well over 255 characters. Each of the 26 columns is linked to a separate workbook and some are very large.

I thought that the problem might be related to automatic calculation so I added code to change to manual calculation prior to the exportation to Word. This did not make any difference.

I then deleted 13 of the 26 columns which cut the wait time down to a minute. Then I cut it down to 6 columns and it took 9 seconds. Obviously, I need the data so do I have any alternatives???

Re: Excel to Word very slow using DDE

Well, I installed Word 2003 and Excel 2003 and it still runs slowly. Not as slowly but still unacceptable. One difference between your example is that mine uses 22 VLOOKUP's to populate itself. Also, I hide all rows where the VLOOKUP returns "" so the export process needs to handle this as well.

I suppose I could copy the calculated range to another range as values and then copy the values to Word. Another experiment would be to delete the hidden rows and try it again; or just unhide all rows and give it a go. We'll see.

The good news is that I now have the latest versions on my computer.

I have a spreadsheet which creates specific data for each day of the year one day at a time in a 2 column worksheet (by 10 to 15 rows which varies by day). I want to export January 1's data to a Word document, then generate January 2's data and export it to the same document, and so on for the entire year.

I use the following code to make it work, but it takes about 4 minutes per day which is unacceptible (I could do it manually far quicker). During this time Excel takes up 100% of the CPU. Here is the code:

Sub Copy2Word()
'
' Copy2Word Macro
' Macro recorded 11/7/2005 by Richard Macro to open Word
'
' Keyboard Shortcut: Ctrl+Shift+W


channelNumber = Application.DDEInitiate( _
    app:="WinWord", _
    topic:="C:\Docs\John_Smith.DOC")
Set rangeToPoke = Worksheets("DataInput").Range("Print_Area")
Application.DDEPoke channelNumber, "\EndOfDoc", rangeToPoke 


'The following code will be added which will add a blank line between each 
'table in the Word document; currently each table is appended to the bottom 
'of the previous day which causes some page break issues. Then save and 
'close once all the days are completed.


'Selection.EndKey Unit:=wdStory    'Go to bottom of document
 '   Selection.TypeParagraph       'Insert paragraph
'Application.DDEExecute channelNumber, "[FILESAVE]"
'Application.DDEExecute channelNumber, "[FILECLOSE]"


Application.DDETerminate channelNumber


End Sub

I use Word/Excel XP BTW. Something is hogging the CPU here. I saw WillR's OLE posting elsewhere on this forum; since this DDE approach works I'd like to find a way to speed it up. If DDE is hopeless and OLE is the way to go then any help along this path would be appreciated. Thanks.

Re: Print to appended PDF file

Thanks for the pointer. I found it and it seems a bit daunting for me. Plus I'd still have to solve the problem of too many pages...each day's data takes up about a third of a page.

So I think I'll try a different route.

channelNumber = Application.DDEInitiate( _
    app:="WinWord", _
    topic:="C:\docs\filename.DOC")
Set rangeToPoke = Worksheets("Sheet1").Range("Print_Area")
Application.DDEPoke channelNumber, "\EndOfDoc", rangeToPoke
Application.DDETerminate channelNumber

This will copy the range into an existing Word document. Once all days have been "exported" in this manner, I'll create a PDF from the resulting Word document.

After the above code runs, I need to save the Word document and then close it so it can be appended to by the next day of data. Is this forum the right place to get this kind of help? Should I start a new thread?

Thanks,
Richard