Xiao Ming and VBA's first date

Chit chat when a little old-fashioned piece

The first time I had a feeling for EXcel, it should have been the early years of practice at Foxconn. At that time is in the FQC post work, once in the new year value night shift relatively busy time, with Excel did a salary calculation, which is the use of Excel some simple formula to calculate. At that time was still very young, this small work also felt small joy, because made, many colleagues also used. (At that time the computer can only see the intranet "news", mobile phones can only be carried to some cadres to work area, data copy or floppy disk, yes, is the storage capacity of 1 to 2 m floppy disk)

This should be the first time I've been indirectly in love with VBA (Visual Basic for applications).

Application Scenarios for problems

Recently we made an export data sheet, where there are pictures in the report. Considering the download of the image directly on the server and plug the picture into the report, and then generate a file return, so that the memory resources of the server is very much consumed, we consider the client implementation of the picture downloaded and placed in the report.

So I started looking at Excel VBA (Visual Basic for applications). This should be my first date with VBA after a long time.

VBA implements the picture download operation for Excel.

Looking at the data for two days, I simply implemented a column in the corresponding row to download a picture based on a column value (image link) in an Excel table.

The realization idea is: 1, traverse the Excel table the picture link column, gets the picture link;

2, download the picture, save the picture file to the local disk temporarily;

3. Insert a picture into the Excel layer, adjust the position and size of the picture

Achieve results

Original report

Download pictures on the client

VBA Code

Simple thinking, code implementation is also simple (raw primary low), on the code

1 'download the picture from net2 'by Wmy at 2018/05/143 Option Explicit4  PublicIsloadimage as Boolean5 6 'must control: Button "CommandButton1", the name of the button control is: CommandButton17 'instructions for use: According to the "Picture Address column" To download the network picture, placed in the corresponding row of "download picture will be placed column"8 'Modify the "Picture Address column" and "Download picture will be placed column" corresponding to the report requirement9 'the corresponding parameters are: Imgurlcolumidx,imgcolumidxTen Private SubCommandButton1_Click () One     DimTxturl as String A     DimLoadtag as String -     DimAsheet asWorksheet -     DimR as Integer the     DimI as Integer -     DimImgurlcolumidx as Integer -     DimImgcolumidx as Integer -R =Sheet1.UsedRange.Rows.Count +i =2 -Imgurlcolumidx =3 'URL picture Address column +Imgcolumidx =4    'download picture will be placed column A     SetAsheet =Me atIsloadimage =isexistpics () -     If(Isloadimage =False) Then -         PagerClearpics -          Do  whileI <=R -Txturl =Asheet.cells (i, IMGURLCOLUMIDX). Value -             If VarType(Asheet.cells (i, IMGURLCOLUMIDX)) > Vbempty Then in                 If VarType(Asheet.cells (i, imgurlcolumidx)) = vbstring Then -                     If VarType(Asheet.cells (i, imgcolumidx)) = Vbempty ThenDownnetfile Txturl,"C:\xiaoming-vab-temporary.jpg", I, Imgcolumidx to                 End If +             End If -i = i +1 the         Loop *Isloadimage =True $     ElsePanax Notoginseng         DimBoxresponse asVariant -Boxresponse =MsgBox("The picture has been downloaded. "&Chr( -) &"do you want to download all the pictures again? ", vbYesNo,"BG report Information Tips") the         IfBoxresponse = vbyes Then +             PagerClearpics A              Do  whileI <=R theTxturl =Asheet.cells (i, IMGURLCOLUMIDX). Value +                 If VarType(Asheet.cells (i, IMGURLCOLUMIDX)) > Vbempty Then -                     If VarType(Asheet.cells (i, imgurlcolumidx)) = vbstring Then $                         If VarType(Asheet.cells (i, imgcolumidx)) = Vbempty ThenDownnetfile Txturl,"C:\xiaoming-vab-temporary.jpg", I, Imgcolumidx $                     End If -                 End If -i = i +1 the             Loop -Isloadimage =TrueWuyi         End If the     End If - End Sub Wu 'Download the Web,and insert to the active sheet - Private SubDownnetfile (ByValNurl as String,ByValNFile as String, Rowidx as Integer, Colidx as Integer) About DimXmlHttp, B () as Byte $ SetXmlHttp =CreateObject("Microsoft.XMLHTTP") -Xmlhttp.open"GET", Nurl,False - Xmlhttp.send - IfXmlhttp.readystate =4  andXmlhttp.status = $  Then AB () =Xmlhttp.responsebody +Open NFile forBinary as#1 thePut #1,, B () -Close #1 $ End If the SetXmlHttp = Nothing the  the 'Dim img as Image the 'Set img = New Image - 'Set img. Picture = LoadPicture (nFile) in 'me.cells (ROWIDX, Colidx + 1) = img the DimRng asVariant the DimFilePath as String About DimAsheet asWorksheet the SetAsheet =Me the  withAsheet theFilePath =NFile +     If Dir(FilePath) <>""  Then -     . Pictures.insert (FilePath). Select the     SetRNG = . Cells (Rowidx, Colidx)Bayi      withSelection the. Top = rng. Top +1 the. left = rng. Left +1 -. Width = rng. Width-1 -. Height = rng. Height-1 the     End  with the End If the End  with the Kill(FilePath) - End Sub the 'Delete all pictures in active sheet, but does not include the Buttom the Subclearpics () the     DimShp asShape94      for  eachShpinch Me. Shapes the     IfShp.type = -  ThenShp.delete the     Next the End Sub98 'Is there any picture inserted About Functionisexistpics () -     DimIsexist as Boolean101Isexist =False102     DimShp asShape103      for  eachShpinch Me. Shapes104         IfShp.type = -  Then theIsexist =True106             Exit  for107         End If108     Next109Isexistpics =isexist the End Function

View Code

Hope, is the unseen air, but shines in the light of the heart

First date, written low!. Take it as a study note. Hope to help to help, but also hope that the VIP sofa in the comment area has the great God's opinion, together to exchange learning.

This article path: http://www.cnblogs.com/youler/p/9046358.html

Xiao Ming and VBA's first date