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

Free Microsoft Excel 2013 Quick Reference

Getting multiple pictures from a folder to excel spreadsheet

Hello,
I have been scouring the excel websites looking for a way to retrieve pictures (jpgs) from a folder to an excel spreadsheet. Here is the catch I will be using an sql query to get my list of photos. I have seen some cool ones that will get be a In column A will be a list of items that needs a photo and column B will be the photos. The concern is that each time I run the query, photos will have to be removed and replaced. So I will want an add picture macro and delete picture macro. The macro below looks great but I can't get it to work.

Code:
Sub PitureFill()
End Sub

Function ShowPicD(PicFile As String) As Boolean
     'Same as ShowPic except  deletes previous picture when picfile  changes
     'Created by Damon Ostrandor
    Dim AC As Range
    Static P As Shape
    On Error GoTo Done
    Set AC = Application.Caller
    If PicExists(P) Then
        P.Delete
    Else
         'look for a picture already over cell
        For Each P In ActiveSheet.Shapes
            If P.Type = msoLinkedPicture Then
                If P.Left >= AC.Left And P.Left < AC.Left + AC.Width Then
                    If P.Top >= AC.Top And P.Top < AC.Top + AC.Height Then
                        P.Delete
                        Exit For
                    End If
                End If
            End If
        Next P
    End If
    Set P = ActiveSheet.Shapes.AddPicture(PicFile, True, True, AC.Left, AC.Top, 200, 200)
    ShowPicD = True
    Exit Function
Done:
    ShowPicD = False
End Function
Sub PitureRemove()
End Sub
Function PicExists(P As Shape) As Boolean
     'Return true if P references an existing shape
    Dim ShapeName As String
    On Error GoTo NoPic
    If P Is Nothing Then GoTo NoPic
    ShapeName = P.Name
    PicExists = True
NoPic:
    PicExists = False
End Function

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub
The next bit of advice for the above was
"Now try in a cell
=ShowPicD("Your picture path here"& a1 & ".jpg")
where a1 houses the picture/file name"

Well I did put the picture name in A1 and in B1 I put a reference to the picture path but how do I get "ShowPicD" to work? Is it okay to have it "ShowPicD..."or do you really need the "=".

Thanks for anybody that can help!!!


Post your answer or comment

comments powered by Disqus
Hi,

I thank you all very much for helping so many days. I need your help again on this.
I have 25 text files say A,B,C,D,E etc in a folder and I have one master excel file with 5 sheets called Master file,Data,Groups,Total, and Price.
Wha I do now manually is I insert one sheet called A in my master file and open text file A when I open that file it open as text so I convert that to text to columns

I want to automate this process.. that is get all the 25 files from a folder to the masterfile in each sheet with converted text into to colums.

However I got a similar code in Ronderbruins site
but that will get file in a different workbook. i want the data to get in the same workbook
http://www.rondebruin.nl/txtcsv.htm

Regards
Arvind

The Cross post is here: http://www.mrexcel.com/forum/showthread.php?t=393437

Hi all,

It's great to be back here!! I have combined these two vba codes

1. brings all excel files(sheets) from a folder into one file with multiple sheets
2. combines these multiple sheets into one sheet

I have no problem using these two codes as below...however, I would like to know how to keep the formats when I do the step #2(combine all sheets into one sheet).

I think it's the code below that need to be changed. I tried several codes but no luck.

'Put data into the Master worksheet
trg.Cells(65536, 1).End(xlUp).Offset(1).Resize(Rng.Rows.Count, Rng.Columns.Count) = Rng.Value

I would much appreciate if someone can clean this code too. I tried to clean it but get an error saying that I have a "master" sheet already.

Thank you all!!!!

-----------------------------------------------------------------------------------

Sub CombineFiles()

Dim Path As String
Dim FileName As String
Dim Wkb As Workbook
Dim Ws As Worksheet

Application.EnableEvents = False
Application.ScreenUpdating = False

Path = InputBox("Enter folder to search", "File location", "x:CoreDaily Forecast Practice")
If Not Trim(Path) = "" Then
End If
FileName = Dir(Path & "*.xls", vbNormal)
Do Until FileName = ""
Set Wkb = Workbooks.Open(FileName:=Path & "" & FileName)
For Each Ws In Wkb.Worksheets
On Error Resume Next
Ws.Name = Wkb.Name & " " & Ws.Name
Ws.Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)

Next Ws
Wkb.Close False
FileName = Dir()
Loop
Application.EnableEvents = True
Application.ScreenUpdating = True

''all from the sheets

Dim wrk As Workbook 'Workbook object - Always good to work with object variables
Dim sht As Worksheet 'Object for handling worksheets in loop
Dim trg As Worksheet 'Master Worksheet
Dim Rng As Range 'Range object
Dim colCount As Integer 'Column count in tables in the worksheets

Set wrk = ActiveWorkbook 'Working in active workbook

For Each sht In wrk.Worksheets
If sht.Name = "Master" Then
MsgBox "There is a worksheet called as 'Master'." & vbCrLf & _
"Please remove or rename this worksheet since 'Master' would be" & _
"the name of the result worksheet of this process.", vbOKOnly + vbExclamation, "Error"
Exit Sub
End If
Next sht

'We don't want screen updating
Application.ScreenUpdating = False

'Add new worksheet as the last worksheet
Set trg = wrk.Worksheets.Add(After:=wrk.Worksheets(wrk.Worksheets.Count))
'Rename the new worksheet
trg.Name = "Master"
'Get column headers from the first worksheet
'Column count first
Set sht = wrk.Worksheets(1)
colCount = sht.Cells(1, 255).End(xlToLeft).Column
'Now retrieve headers, no copy&paste needed
With trg.Cells(1, 1).Resize(1, colCount)
.Value = sht.Cells(1, 1).Resize(1, colCount).Value
'Set font as bold
.Font.Bold = True
End With

'We can start loop
For Each sht In wrk.Worksheets
'If worksheet in loop is the last one, stop execution (it is Master worksheet)
If sht.Index = wrk.Worksheets.Count Then
Exit For
End If
'Data range in worksheet that need to be copied - starts from second row as first rows are the header rows in all worksheets
Set Rng = sht.Range(sht.Cells(1, 40), sht.Cells(65536, 1).End(xlUp).Resize(, colCount))
'Put data into the Master worksheet
trg.Cells(65536, 1).End(xlUp).Offset(1).Resize(Rng.Rows.Count, Rng.Columns.Count) = Rng.Value
Next sht
'Fit the columns in Master worksheet
trg.Columns.AutoFit

'Screen updating should be activated
Application.ScreenUpdating = True

'End Sub

End Sub

retrieving data from a folder full of spreadsheets...

i create a customer's order in a spreadsheet and save the individual order.

i have a few hundred of these in an "orders" folder.

i would like to sort through these spreadsheets, retrieve a specfied customer and average their orders by item, etc.

???

thank you...!

Hi Everyone!

Im a newbie here in this forum but i've visited this site so many times and found useful codes to help me in my work.

Right now im creating a vba in excel where the macro should get a particular file from a folder and attach it on an email. I have finished working on some of the e-mail's components such as determining recipients based on the e-mail ad that corresponds on the file's file type which was linked on the excel sheet. my problem now is an error occurs when i run the program. error was

[error] Run-time error '7063': Application-defined or object-defined error.[/error]

Im having a hard time attaching the file to the mail composed.

Below is my code


	VB:
	
 
Dim noSession As Object, noDatabase As Object, noDocument As Object 
Dim obAttachment As Object, EmbedObject As Object 
Dim stSubject As Variant, stAttachment As String 
Dim vaRecipient As Variant, vaMsg As Variant 
Dim x As Integer 
Dim vaDir As Variant 
Dim RowNo As Integer 
Dim FSO As FileSystemObject 
Dim TestFolder As Folder 
Dim File1 As File 
 
Public Sub Main() 
     
    Set FSO = New FileSystemObject 
    Set TestFolder = FSO.GetFolder("C:Files2Sendtemp") 
     
    RowNo = 3 
     
    Do While Trim$(Cells(RowNo, "B"))  "" 
         
        For Each File1 In TestFolder.Files 
             
             'links folder to excel file
             
            If Right$(File1.Name, 4) = "." & Format$(Trim$((Cells(RowNo, "B"))), "000") Then 
                 
                vaRecipient = Trim$(Cells(RowNo, "D")) 
                 
                 'get body of the mail
                 
                Do 
                    vaMsg = ("Please review the attached file. Thank You.") 
                Loop While vaMsg = "" 
                 
                 'get subject of the mail
                 
                Do 
                    stSubject = ("CONFIRMATION") 
                Loop While stSubject = "" 
                 
                 'Retrieve the path and filename of the attachment.
                 
                stAttachment = File1.Name 
                 
                 'Instantiate the Lotus Notes COM's Objects.
                Set noSession = CreateObject("Notes.NotesSession") 
                Set noDatabase = noSession.GETDATABASE("", "") 
                 
                 'If Lotus Notes is not open then open the mail-part of it.
                If noDatabase.IsOpen = False Then noDatabase.OPENMAIL 
                 
                 'Create the e-mail and the attachment.
                Set noDocument = noDatabase.CreateDocument 
                Set obAttachment = noDocument.CreateRichTextItem("stAttachment") 
                Set EmbedObject = obAttachment.EmbedObject(EMBED_ATTACHMENT, "", stAttachment) 
                 
                 
                 'compose the mail
                 
                With noDocument 
                    .Form = "Memo" 
                    .SendTo = vaRecipient 
                    .Subject = stSubject 
                    .Body = vaMsg 
                    .SaveMessageOnSend = True 
                     
                End With 
                 
                 'Send the e-mail.
                With noDocument 
                    .PostedDate = Now() 
                    .Send 0, vaRecipient 
                End With 
                 
                 'Release objects from the memory.
                Set EmbedObject = Nothing 
                Set obAttachment = Nothing 
                Set noDocument = Nothing 
                Set noDatabase = Nothing 
                Set noSession = Nothing 
                 
                 'Activate Excel for the user.
                AppActivate "Microsoft Excel" 
                 
                 
            End If 
             
        Next File1 
         
        RowNo = RowNo + 1 
         
    Loop 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I would really appreciate your help, Thank you for your time!

I am trying to write a macro that will open files one by one from a
folder, do stuff, plot a graph and then save them, close them and open
up the next file. My problem is that the way I have it written , I can
open the first one, do everything, and then once it opens the second
one, it still has the name of the first file as the name of the sheet
shown as the source of the plot. I tried to use .FoundFiles(i) instead
of the actual name but that gave an error. That is how I get to loop
through the files but how can I get it to work for the sheet name. Help
will be appreciated. Thanks,

Icar

This is the part of the code:

ActiveChart.ChartType = xlXYScatterSmooth
ActiveChart.SetSourceData
Source:=Sheets("IN-coarse-anm-00001").Range("C1:C31"),
PlotBy:=xlColumns
ActiveChart.Location Whe=xlLocationAsObject,
Name:="IN-coarse-anm-00001"
With ActiveChart

Hi,

I would like to know how to open multiple excel files from a folder based on the information based in cells down a certain column until the last cell.

I thought it might be something like this but it isn't:

Sub OpenFiles()
     
Dim eFile As String, totalrows As String
     
totalrows = ActiveSheet.UsedRange.Rows.Count
     
For row = 2 To totalrows
    
eFile = Cells(row, 3).Value
    
Workbooks.Open Filename:=eFile
     
Next row

End Sub
I'm still a noob as it shows so any help would be good.

Thanks

If you can find a way to load that picture into an Image control on a Userform, you could do this:

SavePicture UserForm1.Image1.Picture, "c:mypicture.bmp"

But I don't know if it's possible to get the picture from the worksheet to the image control programmatically.

If not, you could create a chart on the worksheet, delete its contents, paste the picture onto the chart, and use the Export method of the Chart object:

ActiveSheet.ChartObjects(1).Chart.Export "c:mypicture.gif"

(And then delete that chart.)

BTW, Excel doesn't have any documentation on SavePicture, but VB6 does:
(I'm posting this as HTML, not sure if it's going to look right as it does now)

================================================== ===

SavePicture Statement
Saves a graphic from the Picture or Image property of an object or control (if one is associated with it) to a file.

Syntax

SavePicture picture, stringexpression

The SavePicture statement syntax has these parts:

Part Description
picture Picture or Image control from which the graphics file is to be created.
stringexpression Filename of the graphics file to save.

Remarks

If a graphic was loaded from a file to the Picture property of an object, either at design time or at run time, and it's a bitmap, icon, metafile, or enhanced metafile, it's saved using the same format as the original file. If it is a GIF or JPEG file, it is saved as a bitmap file.

Graphics in an Image property are always saved as bitmap (.bmp) files regardless of their original format.

================================================== ===

Hope this helps,

Greg Lovern
http://PrecisionCalc.com
Get Your Numbers Right

"Steven Li" > wrote in message ...
> I am trying to save an image inserted on a sheet to a
> file in vba, is there anyway to do that?

Good Morning All,

I am not sure if I am posting this in the right place, but please bear
with me.

I am trying to run a db query to quickly populate a database with
filenames from a folder on my hard drive. Then in turn I would like to
be able to delete files from the folder, based on my deletes in the
database.

I hope that this makes sense. I am currently using the following code
in the Visual Basic editor to populate my table with the necessary
data:

Sub GetGameFiles()
Dim strFile As String
Dim strFolder As String
Dim strSQL As String

strFolder = "C:pathtothefileiwantaddedtothisdb"
strFile = Dir(strFolder & "*.*")
Do While Len(strFile) > 0
strSQL = "INSERT INTO gamenames (gamefilename) " & _
"VALUES (" & Chr$(34) & strFile & Chr$(34) & ")"
CurrentDb.Execute strSQL, dbFailOnError
strFile = Dir()
Loop

End Sub

I am currently running Access 2003. I also thought about trying this
with Excel 2003

If anyone can offer assistance it would be greatly appreciated.

Thank you.

Devon L.

Hi.

I am trying to extract dat from multiple workbooks within a folder to a master workbook.

for example.

within folder My Documentstest

there are 3 sub folders
folder 1
folder 2
folder 3

each folder has multiple workbooks
etc etc..

so

Folder 1 has workbooks A,B and C

How do I use a message box or similar to just select the folder where all my data is then autoamtically extract all the information I need.

I have tied myself up in knots and the code I have is useless at best

Any ideas how to start with this?

Thanks in advance

Hello All,

This is my first post and my knowledge of VBA is limited. I want to import a worksheet from multiple workbooks in one folder to another workbook in a seperate folder. I want each worksheet imported to appaer in a seperate tab in the new workbook called MASTER PLOD. For example I only want the worksheet DAILY REPORTS to be imported from Workbooks ALPHA 01-07-10 DS and ALPHA 01-07-10 NS and to appear in a separate tab in workbook MASTER PLOD. I could copy/paste but I have 100's of worksheets to import.
Can anyone help with a code?
Thanks
ALPHA 01-07-10 DS.xls

ALPHA 01-07-10 NS.xls

Master Plod.xlsm

Is it possible to get exact values from a graph that isn't one of your data
points? When I graph nonlinear variables is there some type of formula I can
type in to get me an exact x value for a specific y value. For example i have
x values of 0, 1, 2.5, 5, 10.... and y values of 100, 79, 55, 32, 12. If I
want to know what x equals when y=50 how do i go about doing that? I know
that I can't use the standard y=mx+b because my data is not linear... any
suggestions?? I've been trying to figure it out for a couple of weeks and its
starting to drive me crazy. Ive looked into a couple of other graphing
programs (Igor Pro, GraphPad Prism and Delta Graph 3 and they have been no
help either!)

Thank you!

Hi,

I was wondering if anybody could help. I am trying to extract a serie
of files from a folder, but I do not need them all.

I have a list in one column with the files that I need to extract an
copy to another folder. The folder which I copy the files from is on
local network, and the folder that I want to copy them to is sometime
on the network and sometimes on my hard drive.

Any help would be greatly appreciated.

Regards,

Mark P

--
Message posted from http://www.ExcelForum.com

Hello,

How to copy and paste text from note pad to excel spreadsheet with automatic
text to column format without doing it manually.

Example from note pad:-

XXX XXXXX XXX XXXX XX XXX
qwwq qwrew ewqr ishdaf ewrl asdlkfj

Copy the above from note pad and paste to excel you will get all content in
a single cell then you have to use function "Text to column" to split it into
column.

I believe you can paste it to a excel spreadsheet without using function
"Text to column" to get the same result.

Kind regards,
Ngin Hong

Hello,

How to copy and paste text from note pad to excel spreadsheet with automatic
text to column format without doing it manually.

Example from note pad:-

XXX XXXXX XXX XXXX XX XXX
qwwq qwrew ewqr ishdaf ewrl asdlkfj

Copy the above from note pad and paste to excel you will get all content in
a single cell then you have to use function "Text to column" to split it into
column.

I believe you can paste it to a excel spreadsheet without using function
"Text to column" to get the same result.

Kind regards,
Ngin Hong

Is it possible to get exact values from a graph that isn't one of your data
points? When I graph nonlinear variables is there some type of formula I can
type in to get me an exact x value for a specific y value. For example i have
x values of 0, 1, 2.5, 5, 10.... and y values of 100, 79, 55, 32, 12. If I
want to know what x equals when y=50 how do i go about doing that? I know
that I can't use the standard y=mx+b because my data is not linear... any
suggestions?? I've been trying to figure it out for a couple of weeks and its
starting to drive me crazy. Ive looked into a couple of other graphing
programs (Igor Pro, GraphPad Prism and Delta Graph 3 and they have been no
help either!)

Thank you!

Tom,

your question is a bit unprecise..

what do you want to achieve?

passing it from where to where?
from vba to excel or from a userform to excel or
from another application (via vba) to excel?

or do you mean ole or DDE?

keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >

"Tom Walker" > wrote:

> Hi,
>
> How do I pass a parameter to excel? a string?
>
> Thanks,
> Tom
>

Hello
It would be great if someone could help me with this. I have no real experience with macros and rely on sites like this to find the code I need. That said I cant find exactly what I'm looking for this time....

I have an excel template which I use for estimating. I would like to copy selected data from that workbook to another spreadsheet each time I run the template. I have organised the data I want into one row on my template and currently I manually select, copy and paste this into the next available row on a spreadsheet called "Estimate List". I have managed to record a macro to do this but it always overwrites the row which I use when I record the macro. I also have problems with whether the Workbook containing "Estimate list" is open or closed when I try to run the macro.

Thanks in advance for any suggestions you might have.

Cheers Mick

How could I summarize data (total of values of two separate data ranges) from multiple workbooks within a folder in to a master workbook please...

As shown in attached sample workbook, i have data(quantity: numbers only range) in two separate ranges, C5:C9 and F5:F7, and I would like to sum of values from all C5s to C5 of a master workbook(from where I would be running a Macro?) from all the workbooks within a folder. Basically this is to get quantity totals at the end of a month from 20 to 30 workbooks created within a month.

Many thanks in advance.

Sample File - 1.xlsx

Hi all,

First of all, I would like to thank all Excel Forum Moderators and Members especially Andy Pope for the great effort they exert to help all members.

Lately, I viewed the thread available on the following link and I admired the great perfect work and codes provided by Andy Pope so much,

http://www.excelforum.com/excel-prog...-userform.html

My request is similar to the points discussed in the above mentioned thread, but, it is a little bit different ..

Instead of inserting the picture to the worksheet, I want the picture to be copied from one folder on the HD to another folder called (Employees photos), which is located in workbook's path.

In other words, I need a code to copy one jpg picture from one location to another.

For that purpose I tried the following code, but it didn't work with me !!!

Private Sub
From_File_Buttion_Click()

Dim picToOpen As String
     On Error Resume Next
picToOpen = Application.GetOpenFilename("Pics (*.jpg), *.jpg")
                
If picToOpen <> False Then
    objNewPic.Copy
    ActiveWorkbook.Path.Paste
    Unload Me
End If

On Error GoTo 0

End Sub
Can you please help me reaching my goal?

I hope that my request is clear.

Thank you,

Hello,

As the title states, I have multiple workbooks within a folder. Each workbook has a specific worksheet (Named "Data") that I'd like to copy/paste as values into a master file named "Total Financials."

The data in these workbooks are in the same exact format. I'd like to have all the data pasted as values one below the next. So for example the first worksheet would paste from A4:DY61, then the 2nd would paste from A62:DY120.

I'm not strong in macros and this is the only piece to a new process I'm attempting to implement that is holding me back. Any help will be greatly appreciated!

Hi,

I have a folder called New folder with many excel files. I need a macro which extracts all the excel file names into a master sheet in column A from 10th Row one after the other.

Thanks in Advace.
Arvind...

Hi there, the following I want to do:

1. Import large txt. file from C:data( I have the macro)
2. Filter a range out of it with a vlookup and copy them away to a seperate file ( I know as well how to do this)
3. Clear the import data
4. Open the second file in the folder C:data( I have the macro for this as well)
and do step 1 through 3 again etc.

I have the macro to import large txt. files thanks to user dk, see below

Code:
Sub ImportLargeFile() 
'Imports text file into Excel workbook using ADO. 
'If the number of records exceeds 65536 then it splits it over more than one sheet. 

Dim strFilePath As String, strFilename As String, strFullPath As String 
Dim lngCounter As Long 
Dim oConn As Object, oRS As Object, oFSObj As Object 

    'Get a text file name 
strFullPath = Application.GetOpenFilename("Text Files (*.txt),*.txt", , "Please selec text file...") 

If strFullPath = "False" Then Exit Sub  'User pressed Cancel on the open file dialog 

    'This gives us a full path name e.g. C:tempfolderfile.txt 
    'We need to split this into path and file name 
Set oFSObj = CreateObject("SCRIPTING.FILESYSTEMOBJECT") 

strFilePath = oFSObj.GetFile(strFullPath).ParentFolder.Path 
strFilename = oFSObj.GetFile(strFullPath).Name 


    'Open an ADO connection to the folder specified 
Set oConn = CreateObject("ADODB.CONNECTION") 
oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" &"Data Source=" & strFilePath & ";" & "Extended
Properties=""text;HDR=Yes;FMT=Delimited""" 

 Set oRS = CreateObject("ADODB.RECORDSET") 

    'Now actually open the text file and import into Excel 
 oRS.Open "SELECT * FROM " & strFilename, oConn, 3, 1, 1 
 While Not oRS.EOF 
 Sheets.Add 
 ActiveSheet.Range("A1").CopyFromRecordset oRS, 65536 
 Wend 

 oRS.Close 
 oConn.Close 

End Sub
And the code to open one file after the other from a folder(this should be on: VBA Editor => Tools => References => Microsoft Scripting Runtime), see below
Code:
Sub filesladen()
Dim fso As New FileSystemObject
Dim fld As Folder
Dim fle As File
Set fld = fso.GetFolder("C:Data")
For Each fle In fld.Files
If UCase(Right(fle.Name, 3)) = "XLS" Then
Workbooks.Open fle.Path
End If
Next
Set fld = Nothing
End Sub
I have trouble getting the 2 working together. In the first macro you are supposed to select the file, I tried to delete that piece of code and replace it with the second piece of code but that not work. Somebody how to get this working?

Thanks in advance

RS

I hope I can get your help please. I want to know how to obtain
consecutive data from a sheet to another sheet in the same excel book.
I registered data in sheet one for different sheet in the book. I want
this data to b transferred automatically from sheet one to the
different sheets row sequency.

Example: Sheet one Row 23 567 to Sheet six Row 6 567
Sheet one Row 65 879 to Sheet six Row 7
879

As well data can be transferred to another sheets from Sheet one as
same fashion.

I hope there is some help for this problem.

Thanks

--
rodante
------------------------------------------------------------------------
rodante's Profile: http://www.excelforum.com/member.php...o&userid=28251
View this thread: http://www.excelforum.com/showthread...hreadid=479998

Hi,
I wish to write a macro that can get select all the csv files and copy from
one folder
and paste to another folder, and after it will start my other macros.


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