Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

VBA to Read PDF??

I'm probably going out on a limb regarding VBA but, is there a way I can code Excel to link up to a large PDF? For one particular update that is done here, the user has around 50 different rows by 10 columns to update. His process is to:
Copy identifier from Excel worksheet
Paste it into the "Find" bar in the PDF
Look for the identifier on the page that comes up
Copy data
Paste into worksheet.
He wants me to work on automating this procedure. Is it possible? Is there another programming language should be thinking of using? I was talking to a programmer in the company I work for and he suggested VB.NET. Is this something that can be done with VBA though? If not then I guess I'll try the .NET way but if any of you that answer this know of a different way I would appreciate your inputs.


Post your answer or comment

comments powered by Disqus
What VBA would I use to read the path and name of every xls file on a drive into an array.

Hi All,

I need to read a delimited text file in the following format.
03/30/2007,11:14:16,56.1,1000,abcdefgh,abc

I try this but this but having error at line input

Sub open_read()

Dim i, Filenum As Integer
Dim var1 as Date
Dim var2 as String
Dim var3 as Double
Dim var4 as Long
Dim var5 as String
Dim var6 as String

Dim current_path As String

current_path = ThisWorkbook.Path
Filenum = FreeFile
current_path = current_path & "testcase.csv"

Open current_path For Input As #Filenum

For i = 1 To 1000
Line Input #Filenum, var1, var2, var3, var4, var5, var6
tick_array(0, 1, 2, 3, 4) = Split(tick, ",")
Sheets("Sheet1").Range("A1").Offset(i, 0) = var1
Sheets("Sheet1").Range("A1").Offset(i, 1) = var2
Sheets("Sheet1").Range("A1").Offset(i, 2) = var3
Sheets("Sheet1").Range("A1").Offset(i, 3) = var4
Sheets("Sheet1").Range("A1").Offset(i, 4) = var5
Next i

Close Filenum

End Sub

Thanks in advance for your.

Hi

I was wondering if there's any way to read pdf files from excel. I have numbers inside tables in some files with pdf format, and I would like to copy them to excel? Is this possible? web query? macro? Tks

Hi guys,

I am trying to program a loop in Excel VBA to read the first two characters of a cell in column A and, depending on the characters returned, to print something else in column B, then move to the next row up. What I have so far below is pieced together from little insights from over the web, but currently it does nothing. Not even error messages. It has more fields (if "XX" > print blah blah) but they are all the same, so only the first two iterations are shown here for clarity.

Sub MURCROFORMAT()
Dim intRow
    Dim intLastRow
    intLastRow = Range("L500").End(xlUp).Row

    For intRow = intLastRow To 1 Step -1

    Rows(intRow).Select
    If Left(Cells(intRow, 9).Value, 2) = "PE" Then
    Cells(intRow, 12).Select
    ActiveCell.FormulaR1C1 = "MATT"
        ElseIf Left(Cells(intRow, 9).Value, 2) = "RB" Then
        Cells(intRow, 12).Select
        ActiveCell.FormulaR1C1 = "FAZ"

    Else: ActiveCell.FormulaR1C1 = "OWNER"

    End If

    Next intRow

End Sub
If you can see what is wrong with this code, or indeed, if you have a better idea for how I can tackle this problem, I would really appreciate hearing from you.

Hi All,

I searched the forums for this but couldn't find anything appropriate.

I have a project where the final Excel spreadsheet will be stored online for users to download. Occasionally the file will be updated with a new version but I want to make sure that users who have downloaded an older version are alerted if there is a more up-to-date file available.

I want to write a piece of VBA that will check the name of the current file and compare it to the name of the file available for download. Ultimately I want to be prompt the user to download the newer version if/when appropriate.

Does anyone know of an elegant way of achieving this? Essentially, I just need the VBA to read the filename in the specified online directory. If that's not possible, I thought I might have to upload a hidden 'version control' txt file to the directory with a standard name, which VBA can download and open and read the most up-to-date filename from.

Any thoughts?

Cheers,
Ad

How to distinguish different data sets with unknown ranges?

For example: Row 1 to Row 56 belongs to Company A. And Row 57 to Row XX belongs to Company B and so on until 100 companies. For each company it has different range of data and we could not determine the number of the companies and the name as well. How could we use VBA to read and distinguish the name of the company in order to perform calculation for each company?

Hi All
I am currently trying to make an Excel VBA which will extract a table from a PDF File. I got to the point where, if I manually cut and paste the table into a .txt file I can extract it and remake the table.
However I want to be able to access pdf directly from the VBA function. When ever I try this, the words and numbers come out as random characters. I am guessing it is probably compatibility or that I need to access the pdf through another reader.
either way any help would be great, my code I have right now is pasted below for opening the file and separating each word in the file into separate cells.


	VB:
	
 file() 
     
    Dim Filename As String 
    On Error Resume Next 
    Filename = Cells(11, 3) 
    Open Filename For Input As #1 
    If Err  0 Then 
        MsgBox "Not found: " & Filename, vbCritical, "File Location Error" 
        Exit Function 
    End If 
     
    Dim tmpString As String 
    Dim aRow As String 
    Dim X As Integer 
    Dim Y As Integer 
    Dim JSI As String 
    Dim Length As String 
    Dim bRow As Integer 
     
    bRow = 1 
    aRow = 1 
     
    Do Until EOF(1) 
         'Read a line of text
        Line Input #1, tmpString 
         
        Dim newString As String 
         'count # of words in string
        Dim positionIndex As Integer 
        Dim numberOfWords As Integer 
        positionIndex = 1 
         
        For positionIndex = 1 To Len(tmpString) 
            If Mid(tmpString, positionIndex, 1) = Chr(32) Then 
                numberOfWords = numberOfWords + 1 
            End If 
        Next positionIndex 
         
        Dim nextWord As String 
        Dim wordIndex As Integer 
        For wordIndex = 1 To numberOfWords 
            nextWord = ExtractElement(tmpString, wordIndex, Chr(32)) 
            Cells(bRow, wordIndex).Value = nextWord 
        Next wordIndex 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Is there a way i can use vba to print outlook mail and mail attachment (.html, .pdf) Thanks

how to read a value from a cell in VBA?

Dear All

I have a list box with 3 options on a single protected worksheet (call it Worksheet 1)

the items in the list are in the following order call them a,b and C

When I select option a I would like to cause cells in the range

A1:E10 to become un protected and cells R1:Q10 to become protected
(both of the ranges are set as locked when the worksheet is unprotected)

when I select option b or c I would like the oposite to happen
ie R1:Q10 to become unprotected and cell A1:E10 to become protected:

does anyone:

a) Know if one can read from a listbox using VBA?
b) Prehaps have any VBA code for doing this?

Best Wishes

Thomas

Does anyone know of an efficient way to read worksheet data in a
specified range into a VBA array?

For instance, imagine I have a congituous matrix of numbers in Sheet1
in cells A1 through G10.

How might I insert Range("A1:G10").Values into a VBA array in one fell
swoop?

Currently I am looping to populate my VBA array (obviously asinine, I
know)...surely there must be a better way?

Any advice would be gratefully received.

--
Peter Bernadyne
------------------------------------------------------------------------
Peter Bernadyne's Profile: http://www.excelforum.com/member.php...fo&userid=7017
View this thread: http://www.excelforum.com/showthread...hreadid=560749

Using 2003

Below is the macro I recorded opening an Adobe.pdf file which is embedded in the same worksheet.

Sub Macro1()
ActiveSheet.Shapes("Object 32").Select
Selection.Verb Verb:=xlPrimary
End Sub

When I open the file (also when the macro was recorded) Acrobat came up - all seemed OK.

But when I attempted to run the macro, I get a VBA error "cannot open application."

What am I missing?

Thanks for any thoughts

Dennis

Good Evening,

The title pretty much sums up my question, but I will expand further.

I have an excel document with VBA userforms used to populate it. In the most basic manner, this is a stockbook also containing sale information.

I am looking to be able to create PDF invoices (or at least .docx invoices which can then be convered to PDF) using the information in the excel document. Each sale for these invoices has a unique key (sale ID for example), and if at all possible I would like to use a VBA userform to insert this key, to then create the relevant invoice.

I am not necessarily looking for a full working example of this, but just what the best way for me to achieve this would be.

Thanks
Ralph

Using 2003

Below is the macro I recorded opening an Adobe.pdf file which is embedded in the same worksheet.

Sub Macro1()
ActiveSheet.Shapes("Object 32").Select
Selection.Verb Verb:=xlPrimary
End Sub

When I open the file (also when the macro was recorded) Acrobat came up - all seemed OK.

But when I attempted to run the macro, I get a VBA error "cannot open application."

What am I missing?

Thanks for any thoughts

Dennis

Does anyone know of an efficient way to read worksheet data in a specified range into a VBA array?

For instance, imagine I have a congituous matrix of numbers in Sheet1 in cells A1 through G10.

How might I insert Range("A1:G10").Values into a VBA array in one fell swoop?

Currently I am looping to populate my VBA array (obviously asinine, I know)...surely there must be a better way?

Any advice would be gratefully received.

I am leveraging WMI via VBA in Excel 2010 to read the registry and write it to a cell. The code works on similar items but I am having issues reading a WSUS value. The code works in a stand alone vb script but not in excel. Code:
Sub Test()

Const HKEY_LOCAL_MACHINE = &H80000002

strComputer = "."
Set objPatch = GetObject("winmgmts:{impersonationLevel=impersonate}!" & strComputer &
"rootdefault:StdRegProv")

strPatchKeyPath = "SOFTWAREMicrosoftWindowsCurrentVersionWindowsUpdateAuto UpdateResultsInstall"
strPatchEntry = "LastSuccessTime"

objPatch.GetStringValue HKEY_LOCAL_MACHINE, strPatchKeyPath, strPatchEntry, strPatchDate

Cells(1, 1) = strPatchKeyPath & "" & strPatchEntry & " = " & strPatchDate

End Sub
The value is returned as Null. I can manually read the value in the registry and it works through the VB script using wscript. Any help would be appreciated.

I have some VBA code that tries to read in data from the active excel book using ADO (I am currently using the ADO 2.7 library). Here is the code:


	VB:
	
) 
    Dim con As String 
    Dim src As String 
     
     'Open volume data as read-only recordset
    con = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ 
    "Data Source=" & fullName & ";" & _ 
    "Extended Properties=Excel 8.0;" 
     
    src = "SELECT * FROM [volumes$]" 
     
    xl.CursorLocation = adUseClient 
    Call xl.Open(src, con, adOpenStatic, adLockReadOnly) 
     
    xl.MoveFirst 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
The string "fullName" is determined when somebody clicks the "run" button by calling ActiveWorkbook.Path and ActiveWorkbook.Name. The sheet being read is "volumes", which is dynamically created during the execution of the code.

Here is the problem I have encountered:

My end users often have multiple instances of Excel running. If they open this workbook in the original (primary) instance of Excel, there is no problem; however, if they open it in a secondary instance, the code bombs at the "Call xl.Open" line. The code opens a new copy of the workbook in the primary instance of Excel, and this copy doesn't have the "volumes" sheet in it, so the program crashes.

The problem is solved if they always remember to open my workbook by double-clicking on the file icon outside of Excel, rather than going through the Excel File menu; it then always opens in the primary instance.

However, I would prefer that they be able to open the workbook any way they choose, and in any Excel instance they choose.

One solution, which is my least preferred, is to abandon the use of ADO, and read the data into arrays.

The preferred solution, if it exists, is to find a way to specify the active workbook in the connection string. The code snippet I posted above is generally referenced as a way to read data from a closed workbook; is there a different, preferred way to read from the active workbook, so that Excel doesn't attempt to open a new copy?

Failing that, is there a way within VBA to determine if the Excel instance executing the code is the primary instance? I could use this code in Workbook_Open() to determine if it was the right instance of Excel, and if not, inform the user to open the file by double-clicking on it, and then close.

Hi All,

I am pretty new to VBA and currently writing a macro in excel to read lines in a text file if some condition is met. I managed to write a code of read the text file line-by-line but it is not efficient enough as I am just interested in

I am looking for a solution to the following – to be able to use VBA from one workbook – to copy the VBA modules in different workbook, into a third workbook.

Example:

I have three workbooks.

Workbook 1 = Running VBA code – sub routine : Copy_VBA

Workbook 2 = Static workbook – with (30) separate VBA modules

Workbook 1 = Static workbook – with (0) VBA modules, (i.e. – a new workbook).

I want to use the running VBA sub routine - (Copy_VBA) – to make and copy all (30) modules in workbook 2 into workbook 3.

I can read each module name and each sub routine within each module, (including the line numbers where each sub routine starts within a module – also – all labels with line numbers), but so far I have not been able to read / transfer the actual VBA code in the module. I have also not been able to make a new module in a static workbook, (using the running sub routine - (Copy_VBA) - VBA code).

Options??

Many Thanks In Advance!!

Choppork
March 2, 2011
0845 CST

I have an excel 2007 application add-in (using C#.NET). In my application, I create and delete a conditional formatting rule programmatically. The issue I am facing is when I try to delete the conditional formatting rule that I programmatically created, I ran into the following errors:

1. Exception from HRESULT: 0x800A03EC
2. Attempted to read or write protected memory. this is often indication that other memory is corrupt.

I get these errors only when I have user created conditional formatting rules prior to deleting – I don’t want to delete all conditional formatting rules using FormatConditions.Delete(). Further, I can’t use FormatConditions[index].Delete() because I don’t have a index reference for the one that was created from the application.

Note: I have checked the Trust access to the VBA project object model.

Any thoughts on why and how to fix these issues upon delete?

Thanks

I've been assigned the task to automate a process at work. In the current (manual) process, a person receives PDF files via email and manually types in values into an Excel sheet.

I realize that VBA cannot read PDF files. But would it be possible to use Adobe reader (via VBA) to open a PDF file, SaveAs a text file, and then use VBA?

I'd like to know if this will work before I go ask my boss to purchase Adobe Reader.

Hi All,

I have been reading a number of the threads in the forum regarding printing to PDF file, but non of them are in terms I can understand, (Bit too complex for me), I simply want to print one of my worksheets to a PDF file by clicking a Command button on the sheet. I would like the PDF file name to be the text from one of the cells on the sheet say B10 for example. Im using Adobe Acrobat 8 Pro for my PDF program.

Anyone who can help me to try and understand this is will be regarded as an Excel Master in my eyes

Cheers inadvance

J

Hello again!
I do a LOT of searches on here, and a lot of reading. I could have sworn that last week I found a thread on using VBA to open several workbooks, print predesignated pages, and close the workbooks.
I have searched using a lot of combinations...I may be mistaken and read that somewhere else (Highly unlikely as I am like...always here).
Anyone remember a thread like that? I have written a code, but thus far it is purely decorative and I would like to re read the ideas.
Thanks

Hello i want to copy the following function via vba to a number of cells

=countif(i10:ah10,"does not comply")

i basically want row to do a a comparison from i10 to ah10, row 11 to do a comparison from i11 to ah11 and so on.

i tried this as a first step
[vbcode]
Workbooks(WorkBookFileName).Worksheets(TabID).Cells(m, fc + 8 + 1).formula = COUNTIF(I97:AH97,"does not comply")
[/vbcode]

but i got the error

"compile error: expected: list seperator or)

and i tried this one

[vbcode]
Workbooks(WorkBookFileName).Worksheets(TabID).Cells(m, fc + 8 + 1).text = "=COUNTIF(I97:AH97,"does not comply")"
[/vbcode]

and got the error compile error: expected : end of statement

i read some place to ensure that the reference is copied, one can use the following
[vbcode]
wks.Cells(i + 13, 7).FormulaR1C1 = wks.Cells(i, 3).FormulaR1C1
[/vbcode]
but cant get through the first step, where i actually use vba to make the first entry


No luck finding an answer? You could always try Google.