Posts by HEMATINSITE

[xpost][/xpost]

hi dears

i need this vba code , please help us

i have 2 excel sheet

sheet x and sheet y

sheet x have column b as product name and have 200 row

i want in sheet x

1-select b2 and find most similar cell in printarea in sheet y

2-and then get price from next cell in sheet y (like in sheet y , a45 is most similar and must read price from b45)

if

a) find 2 cells with same similar , read price from higher price cell

3-get price and add with xpercentage in o2 or sum with z2 in sheet y and return price to sheet x cell c2

4- repeat this ,for all column B in sheet x

thanks for helping us

hi dear

i have this code for convert xlsm to html . i need repeat this code for entireworkbook and

1-export filename , same as sheet name and in file path

2-select printarea range for convert each sheet

3-every sheet,seperate convert to html

Sub Export()
 Dim rng As Range

 file1 = ThisWorkbook.Path & "\" & "test.html"

 Set rng = Sheets("Tabelle1").Range("A1:C10")

 ActiveWorkbook.PublishObjects.Add( _
 SourceType:=xlSourceRange, _
 Filename:=file1, _
 Sheet:=rng.Worksheet.Name, _
 Source:=rng.Address, _
 HtmlType:=xlHtmlStatic).Publish

End Sub

i use , office 2019 and you ?

Quote from royUK

I think this is on the right track.

I've amended the csv code and found & adapted some code to publish to html.

They will be created in the same folder as the workbook, but you need to add a subfolder and name it HTML

Code

Option Explicit


Sub SaveWorkSheetAsCSV()
Dim oWs As Worksheet
On Error GoTo err_quit
With Application
.DisplayAlerts = False    ''///will overwrite existing files without asking
.ScreenUpdating = False
For Each oWs In ThisWorkbook.Worksheets
oWs.Copy
ActiveWorkbook.SaveAs ThisWorkbook.Path & Application.PathSeparator & oWs.Name & ".csv", xlCSVUTF8
ActiveWorkbook.Close True
Next oWs
err_quit:
.DisplayAlerts = True
.ScreenUpdating = True
End With
End Sub


Sub PublishOnWeb(strSheetName As String, strFileName As String)
Dim objPub As Excel.PublishObject
Set objPub = ThisWorkbook.PublishObjects.Add(SourceType:=xlSourceSheet, Filename:=strFileName, Sheet:=strSheetName, HtmlType:=xlHtmlStatic, Title:="Calls Analysis")
objPub.Publish True
End Sub


Sub CreateHTMLFile()
Dim oWs As Worksheet
On Error GoTo err_quit
With Application
.DisplayAlerts = False    ''///will overwrite existing files without asking
.ScreenUpdating = False
For Each oWs In ThisWorkbook.Worksheets
Call PublishOnWeb(oWs.Name, ThisWorkbook.Path & .PathSeparator & "HTML" & .PathSeparator & oWs.Name & ".htm")
Next oWs
err_quit:
.DisplayAlerts = True
.ScreenUpdating = True
End With
End Sub

Display More

Display More

this is code for produce html and only need to 1-repeat for entire workbook and 2-printarea by default selected for each sheets for convert and 3-html name is same as sheet name

Sub Export()
 Dim rng As Range

 file1 = ThisWorkbook.Path & "\" & "test.html"

 Set rng = Sheets("Tabelle1").Range("A1:C10")

 ActiveWorkbook.PublishObjects.Add( _
 SourceType:=xlSourceRange, _
 Filename:=file1, _
 Sheet:=rng.Worksheet.Name, _
 Source:=rng.Address, _
 HtmlType:=xlHtmlStatic).Publish

End Sub

Quote from royUK

I think this is on the right track.

I've amended the csv code and found & adapted some code to publish to html.

They will be created in the same folder as the workbook, but you need to add a subfolder and name it HTML

Code

Option Explicit


Sub SaveWorkSheetAsCSV()
Dim oWs As Worksheet
On Error GoTo err_quit
With Application
.DisplayAlerts = False    ''///will overwrite existing files without asking
.ScreenUpdating = False
For Each oWs In ThisWorkbook.Worksheets
oWs.Copy
ActiveWorkbook.SaveAs ThisWorkbook.Path & Application.PathSeparator & oWs.Name & ".csv", xlCSVUTF8
ActiveWorkbook.Close True
Next oWs
err_quit:
.DisplayAlerts = True
.ScreenUpdating = True
End With
End Sub


Sub PublishOnWeb(strSheetName As String, strFileName As String)
Dim objPub As Excel.PublishObject
Set objPub = ThisWorkbook.PublishObjects.Add(SourceType:=xlSourceSheet, Filename:=strFileName, Sheet:=strSheetName, HtmlType:=xlHtmlStatic, Title:="Calls Analysis")
objPub.Publish True
End Sub


Sub CreateHTMLFile()
Dim oWs As Worksheet
On Error GoTo err_quit
With Application
.DisplayAlerts = False    ''///will overwrite existing files without asking
.ScreenUpdating = False
For Each oWs In ThisWorkbook.Worksheets
Call PublishOnWeb(oWs.Name, ThisWorkbook.Path & .PathSeparator & "HTML" & .PathSeparator & oWs.Name & ".htm")
Next oWs
err_quit:
.DisplayAlerts = True
.ScreenUpdating = True
End With
End Sub

Display More

Display More

for csv is okey

for html , have error

in line : Call PublishOnWeb

compile error

sub or function not defined

Quote from royUK

Well if you've found the code then try it.

Also, why do you need html?

i have online store and from excel change the price and then convert to html and pdf and then show on my site . i want to show with html because it is very lower size and more mobile friendly and responsive . i have a plugin for show table on my site that only work with csv and html format

Quote from royUK

1. add the filepath into the code.

2. You don't answer my questions, as far as I know this is the only way to save a web page.

about html ,

1-in first step , i donot need show more specification , only need simple table show with cell character content

2-only need html ,please refer to https://docs.microsoft.com/en-…el.publishobject.htmltype

thx

there is 2 issue

1-for csv vba :

a) i need make file in same as directory of file.xlsm

2-for html

a)i need exact .html export but this code prepare mhtml and must change to html . thx

b)i need for each sheet make seperate file name and same as sheet name but this vba make book1.html and etc ......

c)i need make file in same as directory of file.xlsm

royUK

i fix your description.in this file,print area is set for all sheets

hi royUK

excel for example,i shared on google drive . thx for your try . about if will be done is anf for txt i can use kutoolsplus and no problem . please try about html and csv utf8 . thx

hi dears

i have excel with 10 sheets and i need vba for convert to 1-html format and 2-csv comma utf8 and 3.txt format

1-do it for entire workbook automatically and with no question

2-for each sheet make seperate file name and same as sheet name in my excel file path

3- printarea by default selected for each sheets , for convert

thx dear

Quote from Logit

https://superuser.com/question…ile-without-white-margins

thx dear logit

full description but my windows 7 ,didnot have microsoft print to pdf . i must change my os and try again . thx a lot

Quote from dangelor

Glad to help!

this vba code , can support .webp image ? like this website convert excel to webp image . need to open new post or can answer here , dear ?

dangelor

very fast and great code

hi dangelor

it is perfect and very fast and solved

can you repeat this code , for .png .gif .bmp format ?

hi dears

i have this 2 macro for excel to jpg convert and work perfectly , but my workbook have 10 sheets and i must repeat this code for every sheets . i need change these codes

macro a :

1-for do it for entire workbook automativally and with no question

2-every export jpg file name is same as sheet name in my excel file path

3- this macro need select range before run it . i need automatically, printarea by default selected for each sheets , for to convert in jpg .

Sub ExportRangeToJPG()
 '--exports selected range to jpg file
 '  default filename is address of selected range
 '  based on code example posted at:
 '     http://www.emoticode.net/visual-basic/vba-export-excel-range-as-image-and-save-as-file.html

 Dim vFilePath As Variant
 Dim sDefaultName As String
 
 If TypeName(Selection) <> "Range" Then
   MsgBox "Selection is not a range of cells."
   Exit Sub
 End If

 With Selection
   sDefaultName = Replace(.Address(0, 0, xlA1, 1), ":", "to") & ".jpg"
   vFilePath = Application.GetSaveAsFilename( _
    InitialFileName:=sDefaultName, _
    FileFilter:="JPEG File Interchange Format (*.jpg), *.jpg", _
    Title:="Save As")
    
 '--exit if cancelled by user,
 If vFilePath = False Then Exit Sub
   '--Make picture of selection and copy to clipboard
   .CopyPicture Appearance:=xlScreen, Format:=xlBitmap
   
   '--Create an empty chart with exact size of range copied
   With ActiveSheet.ChartObjects.Add( _
        Left:=.Left, Top:=.Top, Width:=.Width, Height:=.Height)
     .Name = "TempChart"
     .Activate
   End With
 End With

 '--Paste into chart area, export to file, delete chart.
 ActiveChart.Paste
 With ActiveSheet.ChartObjects("TempChart")
  .Chart.Export CStr(vFilePath)
  .Delete
 End With
End Sub

hi dears

i have excel file and after convert to pdf , there are many empty margin in page. but if i convert excel to jpg and with acrobat reader convert jpg to pdf , then there is no margin and empty margin .

i adjust page zoom,printarea,scale to fit , software printer , print to file , save as pdf and etc .

are have any macro to do it for entire workbook , without any empty margin ? if these macro have these option is very good

1- printarea by default selected for each sheets , for to convert in pdf

2-export pdf use minimum size pdf format .

3-TOTAL MARGIN SET 0.25 INCH

Option Explicit
Sub Print_PDF()
Dim Awb As Workbook
Dim Snr As Integer
Dim ws As Worksheet
Set Awb = ActiveWorkbook  For Each ws In Awb.Sheets    If Not ws.Name = "Sheet1" Then        'Sheets(ws.Name).Copy        Awb.Sheets(ws.Name).Copy        'Sheets(ws.Name).Select        ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _        Awb.Path & "\" & Awb.Sheets(ws.Name).Name & ".pdf", _        Quality:=xlQualityStandard, IncludeDocProperties:=True, _        IgnorePrintAreas:=False, OpenAfterPublish:=False               ActiveWindow.Close False    End If
Next ws
end sub

for example i attached xlsm file to try on it in google drive