Vba: Open Notepad, Paste Array But Dont Save To Disk

Hello,

I have a number of different files that I often need to run a macro on. In order for me to do it on the 75-100 files I have at any given time, I need to open one, run the macro, close and save, then open the next one.

Is it possible to write a macro that will start with the first file in a folder, open it and update links, run a macro, save and close, and open the next file in the folder until it has open all the files in the folder.

I have experience with creating macros that reference different workbooks, but not sure how to go about opening files with different filenames (without referencing the exact filename).

I'd like to be able to have basic code for opening, saving and closing, opening next file, saving and closing, etc. and input the macro I'd need to run in each file in the appropriate location. Is this possible? Any help is greatly appreciated!!

Thanks,
Jason

I'm trying to open a pdf file from within excel vba. I have tried using the followhyperlink method but adobe acrobat opens very briefly then immediately closes Code:

Sub OpenPDF()

'Dim pdf As String

    On Error Resume Next

    'pdf file to open
    pdf = "K:\PDF\mypdf.pdf"

    'open the pdf file
    ActiveWorkbook.FollowHyperlink pdf

End Sub

So then I tried to create an instance of acrobat by setting a reference to the acrobat object but I can't get this to work either!

The code I'm using is Code:

Sub OpenPDF()

    Dim pdf As AcroPDDoc
    Dim strPDF As String

    Set pdf = CreateObject("AcroExch.PDDoc")
    
    'pdf file to open
    strPDF = "K:\PDF\mypdf.pdf"

    'open the pdf file
    pdf.Open strPDF

End Sub

Any ideas what could be wrong with either approach?

Thank-you

I have an excel form, I want my users to be able to open the form, fill in appropriate data, then click the save button. When the button is clicked, the data in the form should be saved to a new file and then the original form should be reset (clear all values) for a new set of data entry. When the data is saved, I want the new excel sheet to be saved to a location specified within the macro so the user doesn't have any options and I want the title of the new file to be the value of one of the cells.

I'm new to macros and would appreciate any help this board can offer. Thanks...

I did a bit of browsing on this problem. Found others suffering the same but haven't found any conclusive answer yet.

Every so often when I attempt to save a file, (including save as), Excel won'r let me. By won't let me I mean:

using Save doesn't appear to do anything using Save As doesn't either do anything, the dialog is not displayed and if I am doing via the File menu then the File menu is exited and the previous ribbon tab is displayed (i.ethe one I was on before clicking 'File') if I close the workbook I am prompted to save, close without saving or cancel. Clicking save just invokes the same msgbox again. I can't work out when it goes into this mode. Some days I can work without this problem, other days I encounter this 2 or 3 times.

The only thing I could suspect was I think this started around about the time I installed xlDennis' code library. I have uninstalled the addin and so far so good, but I cannot categorically say that this was the cause.

Anyone have any idea?

Cheers
Jon

Edit: I have read this: http://support.microsoft.com/kb/271513
Doesn't seem to cover the issue I describe

We have a number of Excel users in our office who cannot copy and paste
between Excel workbooks. They can copy and paste between worksheets. When
you highlight the section to copy and then go to the new workbook both the
paste
and paste special are "grayed out". This is true whether you right-click the
mouse, go to the edit menu, or use control keys. This occurs with any data
type and the most simple workbooks. I have seen some suggestions here but
none have worked for this particular problem. I have reset the menus and
renamed the .xlb files and neither helps. You can open the clipboard and the
paste will work, but there is no paste special option. Any help would be
greatly appreciated. Thanks!

I need help desparately. Last night I spent several hours working on an
Excel 2002 spreadsheet and it has disappeared.

I saved it routinely as I was making many changes updates etc. I opened
original file --stuff.xls --- made changes and saved it as again as
stuff.xls. I have done this several times before.

Today, when I go to open stuff.xls, it is nowhere to be found!!!!! I have
done a search, for any file modified since Thurs and nothing has appeared. I
have checked the recycle bin, this did not go into auto recover nothing of
the like. I am just traumatized, because I have NEVER had anything like this
happen before.

I even thought perhaps I didn't save it as often as I thought, (I know that
I did) but I remembered that I saved it at least once and I can't even find
an Excel file that has been modified since Thurs!!!

Does anyone have any idea what could have happened to this file?? BTW, I
have prayed, rebooted, run spybot, called witch doctor, all with no success.

I have never had a file to just disappear with no trace. Any help would be
greatly appreciated.

Thanks in advance---

I have a co-worker's file that he is having trouble with. He is using Excel 2000 SP3. When copying a cell with a formula in it of "=D6+C6" and pasting it into the next cell down, it will display the same value in the cell as the calculated value from above, but has the correct formula displayed in the formula bar of "=D7+C7".

Example:
A1: 50
A2: 10
B1: 60
B2: 20

A3: Formula: =A1+A2 Displays: 60
Right click A3, Copy, right click B3, paste
A3 displays 60

When I click save, it will change the display value to 80.

I am trying this on his workstation and mine. Mine has Office 2010, so I think there might be an issue with the file itself.

Also, not just copy and paste. I can also just click the top cell after filling in the formula and then drag the bottom right of the cell downward and it will do the same of filling in the correct formula, but have the incorrect value.

I know that I could get him to just click save each time before really looking at the results, but that is just a band aid to the problem.

Any ideas how to fix this?

Hi all,

I've had a long search through your pages to see if this question has been answered before but having browsed through about 50 pages worth of threads I couldn't see anything, but if I am repeating prior information I do apologise.

I've written a macro that is relatively simple. It just takes some information in one format, rearranges it, adds some formatting and performs some calculations. Nothing incredibly fancy but it works fine on my computer.

Now, I need to share this macro with some other people, so basically I've just sent that excel file on to the people that need to use it. Should be fine and in most cases it is, however there is one user who although they can open the file, can't seem to get the macro to run properly.

It seems to get a small way through the macro but then stop with no error messages or any sign that it hasn't completed properly.

I have checked Macro Security level and that is the same as mine, Tools - Add-Ins is the same, In Visual Basic, Tools - References is the same as mine. It is the same Operating system and the same version of Excel.

I have even signed into this person's computer as myself (it's a big company network thing) and tried to run the macro and it works fine, so there is nothing wrong with the hardware.

I've googled and searched and tried everything I can think of but I'm no closer to solving this problem, so if anyone has read through this wall of text and can come up with a possible solution, that would be greatly appreciated to save me from tearing ALL my hair out!

Thanks very much for your time.

I know this question has been asked a bajillion times, so I apologize for the redundancy.

I am working with an Excel spreadsheet and saving it as a .csv file in order to upload to an application that parses out the .csv data as transactions. The system requires .csv files, so this is how I need to save my doc (with this extension). I have been successful at preventing Excel from coverting that long number into scientific format. I have saved as a TXT file, pasted the longer number and it displays correctly. That is all good. But I have to save as a .csv. So if I do that, close the Excel window, and then open again (as the .csv file), the numbers are back to being displayed in scientific format. I have tried creating an Excel doc from scratch and entering text in Text format, to see if this created a cleaner file. But again, the second I save as .csv, close the window and then open that file up again, that dang scientific format is back.

Does anyone have any idea of how to work around this? Once I have successfully gotten the numbers to display as the long-chain number, how can I get them to "stick" so that they don't revert back to scientific format when I reopen the file?

Thanks so much for your help!

Hi,

I use filters a lot at my work. One thing I've noticed is that if I have something filtered and want to copy and paste only what I see from one column to another column right next to it, it will copy what is filtered, but then paste into the unfiltered portion.

Is there some way to copy only what I see when it's filtered, THEN paste only onto the adjancent cells which is also filtered?

For example let's say i have each of the number below in a cell (Fig 1). And then I hide rows 2 -3, so only 1 and 4 is showingI only see 1 & 4 in column A. I want to copy and paste 1 and 4 to the next column over one row so it looks like Fig. 2 when unhidden. Not like Fig. 3.

Fig. 1

1
2
3
4

Fig. 2

1 1
2
3
4 4

Fig. 3
1 1
2 4
3
4

I have a file that became too big due to phantom bloat, unused range saved by Excel and all that kind of reasons. Thank to previous posts on that board tackling that issue, I was able to find how to proceed to reduce the file back to its normal size.
But I did so in a beta file (test file). The real file has become so big (103MB!) that Excel cannot even open it anymore! The file contains archive info that we do not have anywhere else.
Is there anyway then to open the file or to reduce its size without opening it (through magics...)? I just honestly don't know how to retrieve that info before deleting that file.

Thank you very much for your help.

I did a search in here but didn't find an answer so here goes nothing...

I have a spreadsheet with data from A1 to H1 down to A275 to H275. I select all of it, Copy, go into the Sheet2 tab and select A1, Paste Special -> Transpose and it give me the following error message:
Quote:

The information cannot be pasted because the Copy area and the paste area are not the same size and shape. Try one of the following:
- Click a single cell, and then paste.
- Select a rectangle that's the same size and shape, and then paste.

For the life of me, I can't figure out why it's not working! I have done this many times with other spreadsheets. Has anyone ever experienced this? If so, were you able to resolve it and how?

Many Thanks,
Mike

Hi guys,

Looking for some help and would appreciate your help. I want to prevent people cutting/copy/pasting on a spreadsheet I have developed. A couple of users keep doing so which in turn knackers my formulas. There will be occasions where I will need to be able to use these functions for maintenance and updates. Ideally I would like have a private marcro which I can run to enable these features as and when needed.

I've been at this all morning and feel like I'm going round in circles. I've tried various bits of code (sourced via google etc) & have ended up with a whole manner of outcomes but not the one I want.

To summaraise what I am looking for is some vba code which will do the following Disable cut,copy and paste when sheet is opened Enable cut,copy and paste when closed Have a macro which when run will allow me to cut/copy paste so as to implement updates when necessary.
Can anyone help?

Thanks in advance

I'm having a problem in a workbook with several ActiveX command buttons. I had been using the form control buttons to run macros, but the boss wanted each button to have it's own, different color. So I removed the form control buttons and created new ActiveX command buttons. I got into the button properties and set the background colors. I added the _Click code to run the macros when the user clicked the buttons.

All of the buttons were working fine. Then I saved and closed the workbook and went to lunch. Now when I open the workbook, the buttons don't work! When I click them nothing happens. They appear frozen. They don't even seem to click. No error message. Nothing.

If I right-click the button in Design Mode and select Properties, I get sheet properties not the button properties. I can't seem to locate the command button properties any longer. I still see the button name "cmdButtonGetInfo" and "=EMBED("Forms.CommandButton.1","") in the name box and formula bar. The odd thing is if I create a new button it works fine until I save and close the file. When I reopen the file none of the buttons work.

It's like the buttons are being disabled when I close or open the file. Any suggestions?

When using the copy and paste function it is extremely slow to paste the
result. Also the file size seems to be increasing to a much larger size for
no reason.
Any feedback would be appreciated.