Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

(80% Discount Ends Soon!)

Close Window (X)   
Excel VBA Course
[80% Discount] Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

(80% Discount Ends Today!)

Excel Macro To Open, Print Embedded Pdf And Word Files

Am trying to open, print and close embedded objects as part of a macro to print all of workbook including embedded files. I have the following which works ok if the embedded documents are only word files but I have been unable to adapt it to print PDF files. Is anybody able to offer any ideas??

Sub testprint()
Dim ChartList As Integer
Dim X As Integer
' Variable chartlist stores a count of all embedded charts.
ChartList = ActiveSheet.OLEObjects.Count

' Increments the counter variable 'X' in a loop.
For X = 1 To ChartList
' Selects the shape.
ActiveSheet.OLEObjects(X).Select
Selection.Verb Verb:=xlPrimary
' Makes chart active.
ActiveSheet.OLEObjects(X).Activate
' Prints one copy of active chart.
Set WordApp = GetObject(, "Word.Application")
WordApp.Activedocument.PrintOut
Next
WordApp.Quit
End Sub


Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons 50+ Hours of Instruction 200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

(80% Discount Ends Soon!)

View Course




Similar Topics







Hey guys,

I'm trying to write a macro which prints to PDF and saves the file name as the contents of a cell. I've been looking through all the posts currently on this forum to get something working. I'm using the following code -

Sub PrintPDF()
Filename = "C:\Documents and Settings\samb\My Documents\" & ActiveSheet.Range("Z1").Value
SendKeys Filename & "{ENTER}", False
ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
"Adobe PDF:", Collate:=True
End Sub

The macro prints to PDF, but then it stops at the Save As stage, where I have to manually enter the name of the file and click Save. I want the macro to automatically name the file with the contents of cell Z1. I then want it to then automatically press enter. Any ideas where I'm going wrong? Any help would be much appreciated!


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 added a print button to my userform

Code:

Private Sub btn1_Click()
UserForm1.PrintForm
End Sub


This prints out the userform as I would like, however it autoprints to the default printer and doesn't allow any printing options so I can't select to "print to one page". So as of now it is only printing out half my form and cutting off the rest.

Any suggestions?


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!


I have a VBMacro Excel file loaded on a Server that numerous people access. A Macro in this file creates a Copy of a specific Sheet within the Active Workbook and I want to Save it to the individual's Desktop.

How do I find out what the current User's desktop folder path is each time the Marco is run by a different User?

Example User's path: 'C:\Documents and Settings\jfarc\Desktop'

Where 'jfarc' is the name of the current User which, will of course change with every different User that runs the Macro.

Also, is there a way to pull out of Excel what is the current User's 'Options | General | Default File Location' entry? Which may differ from the above directory.

I am familiar with and use the following coding for Opening/Saving files to the current directory of the opened workbook, but it only gives the path of the existing Excel workbook and not the current User's Directory Path:

Dim wbThis As Workbook
Set wbThis = ThisWorkbook
ChDir wbThis.Path


Hi, I'm trying to get some macros to combine data from two open workbooks, and I can't figure out the commands to switch from the active workbook to the other open workbook and back. I'm new to VBA and just learning the ropes. All I can figure out is how to switch to another workbook with an exact name. If I record the macro to go to a recent file, for example Window > 1 (filename), the code that I get in VBA is Windows("filename").Activate. This doesn't translate to when I have two different workbooks with different filenames open.

Make sense? I'm a bit confused myself. Maybe just a list of basic workbook-switching techniques or commands would be useful.

- Michael


Workbook A has a cell that gets data from workbook B as an external link.
It does this using the indirect function because it needs to concatenate the
path and filename from other cells.

Problem is that if workbook B is closed, the cell in workbook A shows #REF!.

So can indirect work using closed external files or must the external files
be opened?

Is there a clever way of making that work?

Thanks!





I would like to copy a small table from Word into one cell in an Excel
worksheet. The first column of the table is a list of numbers. I tried
converting the table into text with manual line breaks and tab stops to
divide columns and rows, but that didn't solve my problem.

Excel pastes the data into several rows. When I try to merge them, I
get a warning that the selection contains multiple data values, and
merging into one cell keeps the upper-left most data only.

What I tried that didn't work:
* Formatting the Excel cells as text before pasting the data.
* The various options for "Paste Special." The closest I got was
inserting the table as a Document Object, which could be a workaround,
I guess.
What I am saving for when all else fails:
* The obvious solution of copying row by row into one Excel cell.

The data in the table is information about my dad's medications. I
would like to have reference charts of how to identify the strength of
each tablet by its color and markings. I got the info from the
manufacturers' websites and entered it into tables in Word, which I
would like to copy into a more comprehensive file I am creating in
Excel. The first column of each table is the strength of the tablet,
entered as 1 mg., 2 mg., etc. The subsequent columns describe the
shape, color, and markings. There are 3 tables, each with about 4-5
rows.

Is there a way to copy each one - whether as a table or as text - into
a single Excel cell without losing data?

Many thanks.




Corporate edict.

I have a worksheet that is locked and protected now, except for cells in a certain collumn. I have named the cells in that column "MS96A".

If a user enters a date in a cell or range of cells anywhere in the column, the changed cells also need to be locked and protected (Once they enter a date, it is not allowed EVER to be changed again. Corporate requirement! *Shrug*).

What I am looking for is this. If the user selects that cell again, they will get the usual pop-up message, "The cell or chart that you are trying to change is protected..."

I think I am close, but I am getting an "End If without block If" error on the If Clause.


Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim MRange As Range
Set MRange = Range("MS96A")
' If Not Intersect(Target, MRange) Is Nothing Then For Each cell In MRange Sheets("Sheet1").Unprotect Password:="temp"
cell.Interior.ColorIndex = 3
cell.Font.Color = vbBlack
Selection.Locked = True
Selection.FormulaHidden = False
Next cell
ActiveSheet.Protect Password:="temp", _
DrawingObjects:=False, _
Contents:=True, _
Scenarios:=False
ActiveSheet.EnableSelection = xlUnlockedCells
End Sub


I have a spread sheet that prints in duplex no matter what I do. I have made sure the printer duplex function is turned off but yet it insists in printing duplex. It is only this one workbook other workbooks print fine.
I hope you have an ansewer.


Hello, what I would like to do is take a portion of a sheet..... So the cells I want to mirror are in the proposal sheet cells B32 x F51. And then mirror the exact duplicate copy to another sheet. The other sheet should have those same values at A20 X G39. What I am trying to do is have a sheet that has skus, prices, and so forth and then when I add,delete, merge, color, or do anything to the cell on the "proposal creation" sheet I want it to mirror only a specific cells to another sheet that is "salesman copy" that we will print to clients. So this way ont he proposal copy a salesman can insert rows or even change the color of the cells and then have it mirror exactly the same on the "salesman copy" which is what we will print for clients. Thank you.

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!




Okay, so I tried (for hours) to find a solution to this and have finally gotten frustrated to the point where i think asking here will be my best bet.


Basically I want to export data from excel worksheets to various places on a word document I had. I have created a word template as well as bookmarks for that template, as that seems to be the recommendation for performing such a task with excel.

My problem is ... Now what?

I basically have 1 constant worksheet that contains all my affiliates info (Name, Address etc), which i'd like to use to populate some bookmarks in my word template, and then i have another worksheet for the previous month, indicating sales etc for that affiliate. I intend on adding a new worksheet every month that i would use to create new invoices.

Furthermore, i'd really like to be able to update a new file (or perhaps a new page on the same file) for each company with the use of a button, similar to the one's you'd see in an access form.

Can anyone help? Let me know if you need me to clarify.

Thanks!

Matt


I am trying to use the following Access VB code to rename all the files from *.aqi in a directory to *.csv:

Code:

Sub test()
Name "C:\myfolder\*.aqi" As "C:\myfolder\*.csv"
End Sub


The problem is that VBA does not accept wild cards (at least as given here).

Any suggestions?

Thanks

abe


How do I get the colors to change automatically when I use a drop down list.
Per say here is what I am trying to do

If a certain "word" is used from the drop down list I want the background of
that cell to change its color to "green." And if later I change the "word" to
another from the drop down list, it will change it's color to a specified
color.

The drop down list that I use was created from cells that have the colors
already in the "word", but I do not know how to make the list show the colors
so it puts the word & color automatically in the drop down list to the cell
with the drop down (if that makes any sense).

If there is a way, please spell it out simple enough for me to understand,
as I don't know fully the capabilities of formulas or vba.



I am a very advanced Excel user so this is a new one for me.

When I open any spreadsheet (new or already populated) the black box that normally frames the active cell is missing (showing which cell you have selected). Likewise when I 'select all' the sheet does not become shaded.

In addition:

Can not fill series
Can not change cell format (specifically to show Currency or Accounting w/$ symbol)

And I am sure there is much more that I have not found yet. I have not seen a single thread on this topic anywhere on the internet.

Help!


Hi, hope someone can assist, i want to add some arrow syimbols in to a formula, so that when the formula works out it shows an up arrow for higher or a down arrow for lower, i am using this formula at the moment because i dont know any better but it works, putting in the word up or down:

I have entered the following formula in to a cell: =IF(S3<R3,"DOWN","UP") it works however i want to replace the word Down and up with the relevant arrows symbol. How do i do this?

Thanks


I'm working on an Excel file that it's size is about 90 M.B and that makes it very slow to deal with ... is there anyhow to make it faster ???
i use in it alot of formulas and conditional formats
thank u


If I want to activate a workbook, I would do this:

Code:

Windows("File.xls").Activate


But what if the File.xls was a variable named File?

I have tried this:

Code:

Windows(File).Activate


Without success. Please advise.


Hi all,

The following code is placed in workbook 'A' and is used to open workbook 'B'. These workbooks will now always be housed in the same directory and i want to change the code to use a relative path reference by determining the path of workbook 'A'. here's what I had:

Sub income_statement()

Application.WindowState = xlMaximized
Application.Workbooks.Open "C:\Documents and Settings\.....\workbook B.xls", UpdateLinks:=xlUpdateLinksAlways

End Sub

could you suggest how to change this to use a relative path reference?