Free Microsoft Excel 2013 Quick Reference

Using excel vba to open, save as, and close embedded pdf files

I am currently trying to write a macro which will open an embedded pdf file in from excel, open it in acrobat reader, automatically save a copy of it into a predesignated folder, and return to excel to repeat the process. I can't seem to retain control over acrobat after I open the embedded file. Does anyone have any ideas? Code is posted below.

Sub ExcelCopySaveExistingPdf()
    Dim MyDir As String
    Dim strPath As String
    Dim PDApp As AcroApp
    Dim PDDoc As AcroPDDoc
    
    MyDir = ActiveWorkbook.Path
    strPath = MyDir & "Sample Touchpoint"
    
    On Error Resume Next
    
    ActiveSheet.Shapes("Object 23").Select
    Set PDApp = GetObject("Object 23", "Acrobat.AcroApp")
    Selection.Verb Verb:=xlOpen
    'Set PDApp = Selection
    If PDApp Is Nothing Then
        ' PowerPoint is not running, create new instance
        Set PDApp = CreateObject("Acrobat.AcroApp")
        
        Set PDDoc = PDApp.GetActiveDoc
    End If
    On Error GoTo 0
    'GoTo Line25
    On Error Resume Next
    If PDApp.Show = True Then
        ' There is at least one open Acro-doc
        ' Use existing Acro-doc
        Set PDDoc = AcroApp.ActiveDoc
        
        
    Else
        ' There are no Docs
        ' Create new one
        Set PDDoc = AcroPDDoc.Add
        
    End If
    On Error GoTo 0

    
    

Line25:
    With PDDoc
       
    '.Save (strPath & "TESTPRES.pdf")
    '.Save
    .Close
    End With
    PDApp.Exit

    ' Clean up
    Set PDDoc = Nothing
    Set PDApp = Nothing

End Sub


I have a safe data source, where large files are always stored in a .gz

Is there a way to get excel VBA to open up the .gz file?

In the old old days, there used to be a windows command line that would extract files from a windows zip file, but I can't seem to find that anywhere. There has got to be an automated way to do this....

Thanks very much in advance.

Joey

Hi all!!
I need a code in Excel vba to open a folder and select a file and assign that file to a variable of object type.

Please help!!

Thanks in Advance

Hello All,

Please, does anyone know a simple code to print a specific page of a pdf file using Excel VBA.

I have a spreadsheet that contains the pdf file name, the page i would like to print and quantities of that page.

Please help.

Many thanks
Charlie

Hi everyone,

I am using the following code to open (Save As) Dialog Box:


I am also using the following code to open (Open) Dialog Box:


However, after installing (Save As PDF Add-In) in MS Office 2007, What is the proper VBA code to open (Publish as PDF or XPS) Dialog Box?

Temporarily, I am using the following two codes and they work fine, but I think they are not the correct codes for such cases,


OR
If Application.GetSaveAsFilename(ActiveWorkbook.Path & "" & ActiveSheet.Name, _
"PDF (*.PDF), *.PDF") = False Then Exit Sub

I appreciate your usual help,

Best Regards,

I'm trying to use Excel VBA to open all text files in a fold (one at a time) as a workbook, run a macro, then close the workbook without saving it. I have found at least one way that does not work:


	VB:
	
 OpenAllText() 
     
    Dim fs As FileSearch 
    Dim i As Integer 
    Dim wbk As Workbook 
     
    Set fs = Application.FileSearch 
     
    With fs 
        .LookIn = "C:UsersJDDocumentsHH25nl" 
        .FileName = "*.txt" 
        .FileType = msoFileTypeAllFiles 
         
        For i = 1 To .Execute() 
            Set wbk = Workbooks.OpenText(FileName:=.FoundFiles(i), DataType:=xlDelimited, Comma:=True) 
            Call HandRecords 
            Set wbk = wbk.Close(SaveChanges:=False) 
        Next i 
    End With 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
If I change the For/Next loop to:

	VB:
	
 .Execute() 
    MsgBox .FoundFiles(i) 
Next i 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
it will message box each file in the folder. That tells me I'm not doing something right with the .OpenText part of the code.

Any ideas?

Edit: I'm using Excel 2003

Hi there.

I am using excel VBA to launch another software using a journal file (which
is a text file). However, before I launch I sometimes want to modify some of
the information in the text file. I have written some VBA code to read the
original journal file and then printing a new text file at the same time.
When certain variables are read in I substitute the value that is printed in
the new file. This all works well and below is an example of my code.

Open filename For Input Access Read As #2
Open tempfile For Output As #3

While Not EOF(2)

Input #2, slask

If slask = "; Zero Shear Visc visc0" Then
Print #3, slask
Input #2, slask
Print #3, visc0_1
'Print #3, ";;;"
Input #2, slask
End If

If slask = "; Inf Shear Visc viscinf" Then
Print #3, slask
Input #2, slask
Print #3, viscinf1
Input #2, slask
End If

Print #3, slask
Wend

'Print #3, ";;;"
Close #2
Close #3

Name filename As filenamenew
Name tempfile As filename

I am very new at this so I am not really sure how the input and output
works, but I have found that using this method the new file that is printed
leaves out some vital characters such as ". I am reading in the word
"laminar" from the input file but the printed output reads laminar without
the ". Unfortunately I really need my " to be printed to the for the journal
file to work as it should. Any suggestions?

Thank you!

I have a very large file (24mb) that was originally created in Excel
XP, and recently modified in Excel 2003. When users who have Excel
2000 try to open this file it usually takes about 5 minutes to open
whereas it usually takes about 20 seconds to open in Excel XP and 2003.
Then if they try to save the file, an error message pops up that says
the file was not saved.

Thanks,

Brent

Hello,

Trying to in excel macro to open up Google Chrome and open a webpage, then refresh the webpage and then close the webpage.

Dont know whether this is possible or not.
I want to open save as dialog box by Excel macro.

Many thanks in advance.

I have a spreadsheet with several worksheets. I have created a macro that will run when a button is pressed that will remove two columns of data from each page, then move the focus back to the first page, and what I don't have working right now is some code to add to the macro that fun "File - Save as and then save the file in the same directory that the original file is in and just add " - Sanitized" to the end of the file name. I.e. If it was "file.xls" before, I want it to be "file - Sanitized.xls". Here is the code I have so far:

Sub Sanitizer()
'
' Sanitizer Macro
'
'
Sheets("xxx's lates").Select
Columns("B:C").Delete Shift:=xlToLeft
Sheets("xxx's lates").Select
Columns("B:C").Delete Shift:=xlToLeft
Sheets("xxx's lates").Select
Columns("B:C").Delete Shift:=xlToLeft
Sheets("xxx's lates").Select
Range("A1").Select

ActiveWorkbook.SaveAs Filename:= _
"M:Administrative Itemsxxxx-Sanitized.xlsm", _
FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
End Sub

The first part works well, but I tried to record a macro to do the last part and it not going to work as soon as the current file location moves, or the name changes. So I just need it to append the current name and use the current directory.
Thanks in advance for your help. I did try to search for this but could not find it anywhere.

Hey guys - I just discovered there are ways to use Excel macros to open and edit Word documents, but I can't seem to find much info or examples on how to do a few things. So far I have only been able to input text from my Excel sheet into a specific pre-defined location (via Word "bookmarks"). Anyone have any good websites or info that have some info on the following things?

Excel Sheet for examples:
A B
Field Name - Data
1 Seller John
2 Buyer Jane
3 Price $100
4 Amount 50
5 Terms Terms terms terms blah blah

1) If i set up a Word document that has some text in there like [SELLER], can I have excel go through the Word doc and do a find/replace for [SELLER] and replace it with John above?

2) Say I wanted to insert text somewhere in the Word doc at a bookmarked location, but I wanted to add formatting. For example, I want to underline or bold certain words that I insert. (Like in "terms" above). How can I do that?

3) If I have a checkbox in Word.. how do I toggle checking that on/off from Excel?

thanks.

Hi everyone,

I am using the following code to open (Save As) Dialog Box:


I am also using the following code to open (Open) Dialog Box:


However, after installing (Save As PDF Add-In) in MS Office 2007, What is the proper code to open (Publish as PDF or XPS) Dialog Box?

Temporarily, I am using the following two codes and they work fine, but I think they are not the correct codes for such cases,


OR
If Application.GetSaveAsFilename(ActiveWorkbook.Path & "" & ActiveSheet.Name, _
"PDF (*.PDF), *.PDF") = False Then Exit Sub

I appreciate your usual help,

Best Regards,

Hi

i am using a program called WinWedge which is used to transfer readings/measurements/data from a digital readout to Excel

just wondering if Excel VBA can open this problem when a Excel file is opened?

many thanks in advance

Hi,

I have access to two e-mailboxes at work, so in addition to sending emails from my normal address (i.e. my name) I can send them out of the department mailbox.

I have created a form which includes a "From" field, so I can specify that I want a message to come From the department mailbox.

Can anyone tell me how to get Excel VBA to open that form and send a message from this other email address? I found some code online which lets me send messages from my default email address, but how can I get it to send from the other one? .From appears to be an invalid property. Any help? Thanks!

I am using this code to select a folder and diplay the pdf files in that folder..
Sub SelectPdfFile()
    Dim fn As Variant
    Dim i As String
    i = InputBox("Enter Job Number", , "Job Number")

    P = "C:" & i & ""
    ChDrive P
    ChDir P


    fn = Application.GetOpenFilename("PDF Files,*.pdf", _
                                     1, "Select Truss Picture", , False)
    If TypeName(fn) = "Boolean" Then Exit Sub    ' no selection
    Debug.Print "Selected file: " & fn
    Workbooks.Open fn
End Sub
Of course when I select a pdf, excel opens it and it becomes gibberish.
How can the select file be opened with acrobat reader?
xl2000

How to use Excel formula to count the item and generate the 'LineNo'
column and the result look like the following:

LineNo Item Location
0 A101 L01
1 A101 L02
2 A101 L03
0 B100 L01
0 C111 L02
1 C111 L03
2 C111 L05
3 C111 L08

--
annsmjarm
------------------------------------------------------------------------
annsmjarm's Profile: http://www.excelforum.com/member.php...o&userid=13691
View this thread: http://www.excelforum.com/showthread...hreadid=467435

I was working on an excel spreadsheet and accidently "save as" and over wrote
the file, is there anyway i can recover?

I was working on an excel spreadsheet and accidently "save as" and over wrote
the file, is there anyway i can recover?

How to use Excel formula to count the item and generate the 'LineNo' column and the result look like the following:

LineNo Item Location
0 A101 L01
1 A101 L02
2 A101 L03
0 B100 L01
0 C111 L02
1 C111 L03
2 C111 L05
3 C111 L08

Hi,
I have a macro the open a pdf file and print it.

'In a Module...
Option Explicit

Private Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" ( _
ByVal hwnd As Long, _
ByVal lpOperation As String, _
ByVal lpFile As String, _
ByVal lpParameters As String, _
ByVal lpDirectory As String, _
ByVal nShowCmd As Long) As Long

Private Const SW_HIDE As Long = 0
Private Const SW_SHOWNORMAL As Long = 1
Private Const SW_SHOWMAXIMIZED As Long = 3
Private Const SW_SHOWMINIMIZED As Long = 2

Sub Button1_Click()

ShellExecute Application.hwnd, "open", "D:DocumentsLayoutaa.PDF", vbNullString, "D:", SW_SHOWNORMAL
ShellExecute Application.hwnd, "print", "D:DocumentsLayoutaa.PDF", vbNullString, "D:", SW_SHOWNORMAL

End Sub

Could anyone help me in order to:
- open multiple PDF files (all in a same folder),
- print them and
- Close them (pdf)

With a possibility of setting up the page setup (A3 / A4 - Portrait / Landscape)

Regards
RV

So far, what I have is:

As always, first and foremost, you guys rock! I have searched this forum and found many great resources and bits of code. I went from thinking conditional formatting was the greatest thing on Earth to running some pretty sophisticated macros to automate my work.

NEED
I am trying to automate the following: Navigate to multiple addresses, "https://", which will generate a respective page for each address with a link to an Excel workbook, "spreadsheet.xls". The link is the only one on the page for some pages and others have mutliple other links. I would like to find the link -- which in the code below works great -- and then instead of clicking it, I would like to use the method/property of the browser object to "Save As". Is this possible?


	VB:
	
 
Sub DownloadOpenSources() 
    Dim IEX As Object 
    Dim lnk As Object 
    Dim TimeOut As String 
    Dim frm As Object 
    Set IEX = CreateObject("InternetExplorer.Application") 
    IEX.Visible = False 'Do not open an IE Window
    IEX.navigate "[URL="http://%3c%3clink/"]http://" 
    Set o = IEX.Document.All.tags("A") 
    M = o.Length: mySubmit = -1 
    For r = 0 To M - 1: zz = "" 
        zz = zz & "Link Index : " & r & " of " & o.Length - 1 
        zz = zz & String(3, vbCrLf) 
        zz = zz & "A . tabindex : " & o.Item(r).TabIndex 
        zz = zz & String(3, vbCrLf) 
        zz = zz & "A . tagname : " & o.Item(r).tagname 
        zz = zz & String(3, vbCrLf) 
        zz = zz & "A . href : " & o.Item(r).href 
        zz = zz & String(3, vbCrLf) 
        zz = zz & "A . type : " & o.Item(r).Type 
        zz = zz & String(3, vbCrLf) 
        zz = zz & "A . name : " & o.Item(r).Name 
        zz = zz & String(3, vbCrLf) 
        zz = zz & "A . innerhtml : " & o.Item(r).innerhtml 
        zz = zz & String(3, vbCrLf) 
        zz = zz & "A . outerhtml : " & o.Item(r).outerhtml 
        zz = zz & String(3, vbCrLf) 
        zz = zz & "A . rel : " & o.Item(r).rel 
        zz = zz & String(3, vbCrLf) 
        zz = zz & "A . rev : " & o.Item(r).rev 
        zz = zz & String(3, vbCrLf) 
        zz = zz & "A . id : " & o.Item(r).ID 
        zz = zz & String(3, vbCrLf) 
         
        If InStr(1, o.Item(r).innerhtml, "spreadsheet.xls", vbTextCompare) Then 
            o.Item(r).Click: Exit For ' ******** HERE's THE ISSUE: instead of click, I wish to Save As into a sever path,
i.e. //somewhere/over/the/rainbow/ ***************
        End If 
    Next 
    TimeOut = Now + TimeValue("00:00:5") 'wait a maximum of 2 seconds
    Do While Now < TimeOut 
        DoEvents 
    Loop 
     'etc.etc.etc.
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Any ideas how to make this work better and behind the scenes? Also, some spreadsheets require passwords if opening first and then saving.

Appreciate the help.

Very respectfully,
Mac

At work we have about 10 different excel programs that we use regularly, and we're trying to create a control panel program to make things easier for the less technically inclined.

The control panel program (currently named "Control Systems.xls" needs to be a shared workbook that has the ability to open, manipulate, and save other excel files. For example, we have a bidding programming (currently named "bid form.xls") that I need to have a macro from within "Control Systems.xls" access.

The user might want to open an existing bid - in which case the macro would simply need to reference a list of already created files (is it possible to create a list in excel by having a macro scan a folder and then make a list of all of the file names in that folder?) and then select from a pull down menu to open the appropriate bid.

If the user wants to create a new bid, I would need to have the macro prompt for a customer name, check to make sure that the customer name given isn't already in use, open "bid form.xls", then do a 'save as' and save the file as the customer name. They would then need to just work on and save the bid as normal.

Any ideas on how to get started (I'm pretty much a novice) would be greatly greatly appreciated.

My new Excel workbook is always slow to open, save or exit, sometimes taking as long as 3 minutes. The file is only 733 Kb, but it does contain about 9 macros. Saving the macros to a personal macro workbook is not an option, as the workbook will be shared and used on many computers.

Although the macros are recorded, I did go back and edit them to clean them up, removing uncecessary scrolling and stuff. I'm sure they're not as efficient as macros written in VBA, but I don't know VBA. The workbook is also chock full of formulas, but that is unavoidable.

Any ideas what is causing the slow load and save times, or suggestions to prevent it, would be greatly appreciated.

Thanks in advance.

Jerry

I am using Windows Scheduler to open a batch file, which then opens 3 - 4 excel workbooks. In those workbooks, I have my OnOpen() Event to refresh about 4 access queries (per workbook) and then save and then close the workbook. The OnOpen() Event works perfect, the issue that i am running into is the fact that when myself or another user attempts to open the workbook, it continues to run the OnOpen() event (guess I should have thought of that when I was writing the code!) What I am wanting to do, is preferably someone show me code that would only have these OnOpen() events run the 1st time the workbook is opened, OR somehow code in that if VBDate = Tuesday AND System.Time (or whatever the VBA code for time) < 9 a.m. Then run my OnOpen() events.

Backstory, the reason for doing this is I have reports that I manually open and refresh. Well, to save some time I thought I would have windows scheduler open the reports, refresh, then save them. So that all I have to do is then open the reports later on in the day to verify no queries collapsed or nothing crazy went on, then save the reports and email. Would save me a great deal of time and free up my day so that I can work on other projects.

Thanks in advance for anyones assistance!!