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."

    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:

    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:


    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 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.

    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

    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?

    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.


    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:

    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", _
    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?