VBA XML and encoding

Visual Basic for Applications (VBA)

‹ Microsoft ISV
- Archive
  - Windows Live
  - SharePoint
  - SQL Server
  - VFP
  - ISV
  - Visual Studio
  - VC++
  - VB
  - IE
  - Dotnet
  - Game
  - Architecture
  - Windows Forms
  - Visual C#
  - Smart Device
  - Office
  - Windows Vista
  - VS 2008
  - VS Express

- Previous
  - 1
    - How to show dates in forms by the order of time? Thank you. I change the order in the table, but in the forms it is the wrong order. If I click descending order in the form, it works, but when I close my database and open it again, the right order will be gone. Is there any way to maintain the right order for good Qian Tag: VBA XML and encoding
  - 2
    - Showing the printing window in Excel to print a spreadsheet. Perfect. Thanks Cathrine! Tag: VBA XML and encoding
  - 3
    - VBA 6.3 String Hi, My question is how do i access individual characters of a string in VBA 6.3 is there something like myString[0] thx Tag: VBA XML and encoding
  - 4
    - IsError function from VBA6.3 to v6.0 Thanks Andy, vRange covers the whole table of the sheet being checked. i.e. A1:AE2000 (depending on the last row used). OldFN = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls), *.xls", Title:="Please select last week's file") Workbooks.Open (OldFN) OldLastRow = Range("A65536").End(xlUp).Row ' last used row of old sheet vRange = Range("A2", Cells(OldLastRow, 31)) This all works fine on the laptop! Briefly, the aim is as follows: OldFN is last week's list of outstanding cases that were allocated to specific people. Do a Vlookup to maintain those cases that also appear on this week's list (i.e. didn't get paid) by returning column 31 from vRange (the location of the handler ID) or put "NEW" in the cell to indicate the case needs allocating. Is that a bit clearer Sorry, really should have included that snippet earlier. Steve. Tag: VBA XML and encoding
  - 5
    - Calling a function from a DLL Maybe you need to register it Launch "regsvr32 C:\Windows\System32\OpcAmericanaDLL.dll". Or try to add reference (Tools->References->Browse) Tag: VBA XML and encoding
  - 6
    - VBA macro to replace image sheet 1 INDUSTRIE INDUSTRIE SOUS INDUSTRIE mediane IK 2008 Basic Resources Basic Resources Forestry & Paper 1,26 Basic Resources Industrial Metals 2,02 Basic Resources Mining 1,00 Chemicals Chemicals Chemicals 1,37 Automobiles & Parts Automobiles & Parts Automobiles & Parts 1,89 Food & Beverage Food & Beverage Beverages 1,26 Food & Beverage Food Producers 1,89 Personal & Household Goods Personal & Household Goods Household Goods 1,57 Personal & Household Goods Leisure Goods 2,21 Personal & Household Goods Personal Goods 2,21 Personal & Household Goods Tobacco 1,20 Media Media Media 0,71 Retail Retail Food & Drug Retailers 4,89 Retail General Retailers 2,99 image sheet 2 ICB Level 1 Sector ICB Level 2 Sector Consumer Services Retail Consumer Services Retail Consumer Services Media Telecommunications Telecommunications Financials Insurance Financials Insurance Consumer Services Media Consumer Services Media Industrials Industrial Goods & Services Consumer Services Travel & Leisure Technology Technology Financials Financial Services Industrials Industrial Goods & Services Consumer Goods Personal & Household Goods this is just a small portion of the sheet i mailed to help you visualise my problem. This is what i am looking for a macro to do. I would like to automatically delete the first and third columns in first image. Then i want to find identical items in column B in both images and replace column B in image 1 with coreesponding column A items in image 2. I wish i am clear, my english may be ambigous. Thank you for your help in advance Tag: VBA XML and encoding
  - 7
    - Locking clickable labels Can you lock a label like you can a toggleButton I have some code as follows; If toggleButton1.value = True Then togglebutton2.locked = True Else togglebutton2.locked = False End If But I also want to temporarily lock some clickable labels, but there is no ".locked" function for labels. How do I stop a label's click action from being allowable under certain circumstances Thanks. Tag: VBA XML and encoding
  - 8
    - Error 1004 with Excel 2007 (on VISTA) on Publish method Hi, I'm trying to publish a Range in an html file from excel 2007 (OS is VISTA) and I receive an error 1004 on the Publish method. Here is the code I'm using : Code Block ' temp file name Dim TempFile As String TempFile = Environ$("temp") & "\" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm" ' delete any PublishObjects left Set objPObjs = ActiveWorkbook.PublishObjects For Each objPO In objPObjs If objPO.HtmlType = xlHtmlStatic Then objPO.Delete End If Next objPO ' Set the publishObject attributes ActiveWorkbook.PublishObjects.Add _ SourceType:=xlSourceRange, _ Filename:=TempFile, _ Sheet:=ActiveSheet.Name, _ Source:="A1:J12", _ HtmlType:=xlHtmlStatic ' Publish the object For Each objPO In objPObjs If objPO.HtmlType = xlHtmlStatic Then objPO.Publish End If Next objPO I tried many things (including samples found on theweb) without success. Any help appreciated. Regards, Laurent. Tag: VBA XML and encoding
  - 9
    - how to get the VALUE of an OUTLINENUMBER (word / macro in VB) Hi, I am currently working on a VB macro which has to get references in Word documents in order to make a traceability matrix. My problem comes from a document, in which the references are Outlinenumbers in a column containing this outlinenumber list. I have to get the value of the cells of this column, but these outlinenumbers (auto-incremented numbers) are neither strings nor numbers... I don t know the type and then I don t know how to get the value. Any idea that would be great because i m currently stopped in my internship because of that. Regards, Cal. Tag: VBA XML and encoding
  - 10
    - HELP FIX THE CODE.......... I have somehow managed to put some code together to complete a task that i have found some difficulty with. Match and delete a row from table. Need some help fixing the code.......... ArrNames = Array("creation1", "creation2", "creation3", "Creation4", "creation5", "creation6", "creation7", "creation8", "creation9", "creation10", "creation11") If Not IsError(Application.Match(.Value, ArrNames, 0)) Then .EntireRow.Delete Dim ArrNames As Variant With .Cells(Lrow, "B") Firstrow = .UsedRange.Cells(1).Row Lastrow = .UsedRange.Rows(399).Row Sub Loop_Example() Dim Firstrow As Long Dim Lastrow As Long Dim Lrow As Long Dim CalcMode As Long Dim ViewMode As Long With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With Tag: VBA XML and encoding
  - 11
    - Problems with VB solver code I am writing visual basic code behind an excel spreasheet and am having difficulty making it work. It involves using the solver function. The code is listed below: Sub signif_control_pop() Dim cntlpc Dim pop 'Clear any previous solver settings. SOLVERReset 'Prompt user for population size. pop = Application.InputBox(Prompt:="Population Total:", Type:=1) Range("C3") = pop 'Set targeted cell, H16, to a value by changing the range of acceptable percentages. SolverOK SetCell:=Range("H16"), MaxMinVal:=3, _ ValueOf:=0.95, _ byChange:=Range("B5") ' Solve the model and keep the final results. SolverSolve Userfinish:=False 'Save results of control percent before you discard the results. cntl_pc = Range("B5") SolverFinish KeepFinal:=1 'NOTE: Does not work if I include ReportArray:=1 ' Show the result in a message box. MsgBox "The Control Population Percent for " & pop & " is " & cntl_pc & " " End Sub The problem I'm having is regarding the "ByChange" cell (B5) used in the solver function. The value in the cell never changes. When the message box is displayed, it always displays the same number, which is the number that was sitting in cell B5 before I ran the program. I have included "SolverFinish KeepFinal:= 1" in the code. If I do not run the visual basic code, but rather manually run Solver in excel, it will change cell as I want it to do. Any help is appreciated. Stacy Tag: VBA XML and encoding
  - 12
    - New to Visual Basic Hi Dear, The simplest way to perform this task will be to save the each button value in a single string and whenever you want its integer or float representation cast it into the integer. eg. Code Snippet Dim str As String Dim Number As Integer str = "" str = str + "1" ' On Button 1 Press str = str + "2" ' On Button 2 Press 'and when u want integer representation of the string cast the string into integer Number = CInt (str) Happy Programming! Tag: VBA XML and encoding
  - 13
    - Information required : WOW64 I beg to differ with the statement above: "It does this by intercepting calls to APIs like GetSystemDirectory and returning the appropriate directory" A 32 Bit VB program with: Declare Function GetSystemDirectory Lib "kernel32" Alias "GetSystemDirectoryA" (ByVal lpBuffer As String, ByVal nSize As Long) As Long Will return "c:\windows\system32" when run on windows 2003 64BIT. This is causing major hassles with every single 32-bit program that makes this system call and breaks most installers that put things in the system folder. 32-bit DLL's simply do not appear to be able to be used if they are installed in system32. If the GetSystemDirectory call in Kernel32 does not return the right folder how can anyone expect applications to work correctly Tag: VBA XML and encoding
  - 14
    - MSDN Installation I've MSDN installed on my system, since i've installed it through MSDN setup (not by VS setup) Visual Studio does not recognize its presence(Ctrl+F1 doesn't work.), Is there a way I could tell visual studio about the presence of MSDN. By the way this behaviour is common in both VS 2003 as well as VS2005 Tag: VBA XML and encoding
  - 15
    - error message when closing ie7 I was having this same error message every time I exited Explorer. One of the formums mentioned the optional Expr add-ons might be a problem. So I went back in and deleted/removed all of the Explorer add-ons I had downloaded through MS Update. That did the trick. My question: why is it listed on MS Update as an option if it causes additional issues Thanks! AContinuallyFrustratedMsCustomerWhoThinksIfHisCompanyProducedProductThatWorkedAsInconsistantlyAsMs'sHisCompanyWouldBeLongGone Tag: VBA XML and encoding
  - 16
    - add group to printer ACL Hello, I have a print server with 200 shared printers. I need to add a group to each printer ACL with an appropriate rights (same for all printers). Is anyway to script it Thank you Ali Tag: VBA XML and encoding
- Next
  - 1
    - Increase prompt in message box Thanks. However the problem with using a text box is that a text box is interactive, allowing the user to click in it and alter the text. I don't want this. I just want an unchangeable message (a rather long help message) to be displayed. How would I do this Tag: VBA XML and encoding
  - 2
    - Configuration Failed Hi. I'm also getting this error while trying to fix SharePoint Portal 2007. A little background about what I did: 1) I wanted to uninstall SharePoint from a web site (or webapplication, whatever) 2) I clicked it and immediately realized i was uninstalling from the Administration Page rather than from the other site i wanted. 3) It was too late. 4) I tried running the SharePoint products and Technologies Configuration Wizard to fix it. Several attempts did not seem to make any difference 5) I un-installed the admin page from my 2 web front ends, and also from my Job/Index server 6) I then deleted the IIS SharePoint v3 admin page and the corresponding folder (after backing it up) 7) I tried running SharePoint Products and Technologies Configuration Wizard again. 8) It runs through most of it and then failes with that error message. I'm running SharePoint Portal 2007 on Microsoft Windows 2003. Tag: VBA XML and encoding
  - 3
    - Communicator Mobile 2005 and TLS (Using port 4443 possible?) More options Confused on something with CM and CWA: We have web access enabled/installed, it uses MTLS and certificates on port 4443 to access the site... I'm assuming when using Communicator Mobile on Windows Mobile 2005, I specify TLS to connect remotely to the server (no vpn).. Is there a way to specify port 4443 or is the TLS port 443 even with Web Access installed on port 4443 (IE: these are different or the same) Thanks Tag: VBA XML and encoding
  - 4
    - VBA in Excel - organizing charts as objects in a sheet I am using a macro to create multiple charts from data on a separate sheet in the Excel file. However, the program places each new chart on top of the previous one in the sheet I select. I would like to place these charts apart on the sheet so that they are viewable. 1) Is there an command to order the charts down and across the sheet 2) Alternatively, can I select and move each sheet without using the actual object name in the while loop, "Chart1," for example. 3) Specifiying the object name would only work for the first chart. Is there a way to number the charts with a variable Here is the while portion of my code as it is now ( red text indicates the portion I am having difficulty with ) : While j < 243 Charts.Add With Sheets("TRESULTSdmp_3") ActiveChart.SetSourceData Source:=.Range(.Cells(i + 1, j + 1), .Cells(i + m, j + 1)), PlotBy:=xlColumns End With ActiveChart.ChartType = xlXYScatter ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1" With ActiveChart .HasTitle = True .ChartTitle.Characters.Text = "Test point to the right" .Axes(xlCategory, xlPrimary).HasTitle = False .Axes(xlValue, xlPrimary).HasTitle = False End With ActiveWindow.Visible = False <----- question 2 above) want to move object to a specific location ActiveWindow.WindowState = xlNormal (will have to make Range a variable) ActiveWindow.WindowState = xlMaximized Range("A1").Select ActiveSheet.ChartObjects("Chart 8").Activate ActiveChart.Paste ActiveSheet.Shapes("Chart 3").IncrementLeft -123.75 <--- question 3 above) want to make object # variable ActiveSheet.Shapes("Chart 3").IncrementTop -8.25 j = j + 1 Wend Tag: VBA XML and encoding
  - 5
    - AutoExec will not execute properly Hi the series of macros must run in Word, Excel and Powerpoint. Therefore individual start modules exist for the three applications calling then the common Sub initialise_program here is the code in Module "Main" Sub AutoExec() ' ' macro is run when AddIn is read in ' the only job is to set the entry in the menubar ' Call initialise_program() End Sub ' AutoExec Module mod_initialise_form_menu Sub remove_commandbar_entry() Dim xx, my_bar As Object Set my_bar = CommandBars.ActiveMenuBar ' remove existing entry (may be more than one!) For Each xx In my_bar.Controls If xx.Caption = menu_forms Then my_bar.Controls(xx.Caption).Delete End If ' xx.Caption = menu_forms Next xx ' Each xx In my_bar.Controls End Sub Sub initialise_program() ' ' ' Adds the menue to the menubar ' Dim xx, cmd, my_bar, new_menu As Object Dim cmd_sub As CommandBarControl Dim aa As String Dim yy, entry_nr As Long Dim test As String, is_here As String ' ' get language dependent texts ' If init_them_variables(True) = False Then Exit Sub ' ' remove possibly existing CommandBar entry ' Call remove_commandbar_entry ' ' find " " so that new entry position can be defined yy = 0 Set my_bar = CommandBars.ActiveMenuBar For Each xx In my_bar.Controls yy = yy + 1 If xx.Caption = "& " Then Exit For End If ' xx.Caption = "& " Next xx ' Each xx In my_bar.Controls ' check if word If InStr(1, Application.Name, "word", vbTextCompare) > 0 Then Set new_menu = my_bar.Controls.Add(Type:=msoControlPopup, before:=yy) Else ' InStr(1, Application.Name, "word", vbTextCompare) > 0 Set new_menu = my_bar.Controls.Add(Type:=msoControlPopup, before:=yy, Temporary:=True) End If ' InStr(1, Application.Name, "word", vbTextCompare) > 0 new_menu.Caption = menu_forms ' Set cmd = new_menu.Controls.Add(Type:=msoControlButton, Id:=1) cmd.Caption = menu_forms_and_documents '"&Formulare und Dokumente" cmd.TooltipText = "Formulare und Dokumente" cmd.Style = msoButtonCaption cmd.OnAction = "get_form" test = Dir(local_start_folder & name_project_list_txt) If test <> "" Then Set cmd = new_menu.Controls.Add(Type:=msoControlButton, Id:=1) cmd.Caption = menu_projects ' "&Projektdokumente" cmd.TooltipText = "Formulare und Dokumente" cmd.Style = msoButtonCaption cmd.OnAction = "Select_Project_Document" End If ' test <> "" Set cmd = new_menu.Controls.Add(Type:=msoControlPopup, Id:=1) cmd.Caption = menu_file_management ' "&Dateimanagement" cmd.TooltipText = "" ' Set cmd_sub = cmd.Controls.Add(Type:=msoControlButton, Id:=1) cmd_sub.Caption = menu_protection_set '"Schreibschutz &setzen" cmd_sub.TooltipText = "" cmd_sub.Style = msoButtonCaption cmd_sub.OnAction = "file_release" ' Set cmd_sub = cmd.Controls.Add(Type:=msoControlButton, Id:=1) cmd_sub.Caption = menu_protection_clear '"Schreibschutz &entfernen" cmd_sub.TooltipText = "" cmd_sub.Style = msoButtonCaption cmd_sub.OnAction = "remove_write_protect" Set cmd_sub = cmd.Controls.Add(Type:=msoControlButton, Id:=1) cmd_sub.Caption = menu_archive '"Datei &archivieren" cmd_sub.TooltipText = "" cmd_sub.Style = msoButtonCaption cmd_sub.OnAction = "archive_document" ' ' check if mod_customer_data is ok, if not, don't show menu_client_data ' If mod_customer_data_bas = True Then Set cmd = new_menu.Controls.Add(Type:=msoControlPopup, Id:=1) cmd.Caption = menu_client_data ' "&Kundendaten" cmd.TooltipText = "" ' Set cmd_sub = cmd.Controls.Add(Type:=msoControlButton, Id:=1) cmd_sub.Caption = menu_client_data_enter '"&Neue Kundendaten eingeben" cmd_sub.TooltipText = "" cmd_sub.Style = msoButtonCaption cmd_sub.OnAction = "enter_new_customer_data" ' Set cmd_sub = cmd.Controls.Add(Type:=msoControlButton, Id:=1) cmd_sub.Caption = menu_client_data_change ' "&Aktuelle Kundendaten andern" cmd_sub.TooltipText = "" cmd_sub.Style = msoButtonCaption cmd_sub.OnAction = "change_customer_data" ' Set cmd_sub = cmd.Controls.Add(Type:=msoControlButton, Id:=1) cmd_sub.Caption = menu_client_data_insert '"&In Formular einfugen" cmd_sub.TooltipText = "" cmd_sub.Style = msoButtonCaption cmd_sub.OnAction = "insert_customer_data" End If ' Application.Name <> "Microsoft PowerPoint" If update_allowed <> "yes" Then Set cmd = new_menu.Controls.Add(Type:=msoControlButton, Id:=1) cmd.Caption = menu_update_forms ' "&Update Formulare" cmd.TooltipText = "Update Formulare" cmd.Style = msoButtonCaption cmd.OnAction = "menue_get_new_files" End If ' update_allowed <> "yes" ' ' test if Guru is here ' If InStr(1, all_the_gurus, get_user_name, vbTextCompare) > 0 Then Set cmd = new_menu.Controls.Add(Type:=msoControlPopup, Id:=1) cmd.Caption = menu_procedures ' "&Prozeduren" cmd.TooltipText = "" ' Set cmd_sub = cmd.Controls.Add(Type:=msoControlButton, Id:=1) cmd_sub.Caption = menu_procedures_save ' "&Save Procedure" cmd_sub.TooltipText = "" cmd_sub.Style = msoButtonCaption cmd_sub.OnAction = "archive_procedure" End If ' InStr(1, all_the_gurus, get_user_name, vbTextCompare) > 0 End Sub ======= end of code ======== In the cases were the macro does not execute properly only the entries menu_protection_set, menu_protection_clear and menu_archive appear in the menubar Hope this helps! regards Jurgen Tag: VBA XML and encoding
  - 6
    - Determine if and where the .NET Framework is installed using VBA For example check if exists %windir%/Microsoft.NET/Framework/ directory. Regards. Tag: VBA XML and encoding
  - 7
    - Pivot Table Macro Thank you Navajo - I will try what you suggested. Gary Tag: VBA XML and encoding
  - 8
    - how can I count # of columns between two specific columns? The key is to get the column in integer value. Range("A3").column will give you 1 Range("D321").column will give you 4 the row number doesn't matter. you can figure out the rest since I don't know how you want to count it specifically. Tag: VBA XML and encoding
  - 9
    - Why the do while loop does not work? Instead of Range(cells(raffiliate, 1), cells(raffiliate, lastcolumnGSVGrowthRate)).Select Selection.Copy worksheets("AFFILIATE").Activate ActiveSheet.cells(rowaffiliate, 1).Select Selection.PasteSpecial Paste:=xlPasteAll Use Range(Cells(raffiliate, 1), Cells(raffiliate, lastcolumnGSVGrowthRate)).Copy Destination:=Worksheets("AFFILIATE").Sheets(1).Cells(rowaffiliate, 1) This will have the control in the activesheet and still copies the contents to Affiliate sheet Cheers Shasur Tag: VBA XML and encoding
  - 10
    - VBA and automated word generation Move the thread from Visual Basic General since the question is mainly on VBA. There are more exports in Visual Basic for Application forum and I believe you can get satisfying answers in this forum. Thanks again for your question. Tag: VBA XML and encoding
  - 11
    - Using Excel files in MS Works Had Office and XP on my previous computer and created several Excel files I want/need to keep New computer has Vista and MS WORKS installed - will I have a problem with transferring the Excel files to new computer If so, how do I remedy Tag: VBA XML and encoding
  - 12
    - iexplore.exe - Application Error I also get error message with my explorer. It says "0x00139ae5" referenced memory at "0x00139ae5". The memory could not be "written". It happened when I installed and used IE 7 beta. Already getting frustrated with this. Tag: VBA XML and encoding
  - 13
    - error with wscript.shell error 80070483 hi, i am trying to run a vb script from a VBA module in excel. fn = "test.vbs Set wshShell = CreateObject("Wscript.Shell") wshShell.Run (fn) in the line wshShell.Run (fn) i obtain an error error 80070483 method Run of object IWshShell3 failed it is the first time i do this, so i am not shure if i need to have done something before, some references in windows extension .vbs is not related to any application (is it ok ) tks in advance Tag: VBA XML and encoding
  - 14
    - Obtain Unique Work Units via SQL Adamus, How and where does that fit into my current code Tag: VBA XML and encoding
  - 15
    - Adding Menu to Microsoft project 2007 using VBA How can I add a custom menu using VBA code. There are some changes happened in from Microsoft Project 2003 to Microsoft Project 2007. How can I add custom menu to microsoft project 2007 and make it work to invoke appropriate code. Are there any whitepaper which talks about the changes in the coding from Microsoft project 2003 to 2007. I am new to this area of coding with little knowledge of VBA. ANy help will be highly appreciated. A sample code will work wonders for me. Tag: VBA XML and encoding
  - 16
    - compile error: invalid quaifier Hi! Thanks a lot! It works like this!!!! Thanks!! Tag: VBA XML and encoding

Vsfa

Hello,

Here is my issue.
In an excel sheet, I fill in several cells which contain text.

This text can be either in english / french / spanish / italien / german, which means I can have characters like "e" or "e" etc...

In a vba macro I'm trying to put all these data into an XML, this XML is then processed later on by an ASP.net app I have created (using an xmlreader).

The thing is, when I try to display the value of each node of my XML with my ASP.net app, some characters are replaced by squares.
For example, characters like "-", "...", singlequote

BUT, characters like "e" and "e" are perfectly displayed.

And when I have a look into my XML file with Visual Studio I can clearly see all my characters.

Everything is fine.

However, when I try to save the file in VS, it warns me "that some unicode characters can't be saved in the current codepage. Do you want to resave the file as Unicode in order to maintain your data "

If I click "YES" it then set the Xml Encoding to : UTF-16

And from now on, everything is displayed correctly in my ASP.net app.

Here is the VBA Code which generates my XML File.

Code Snippet

Open strPath & cstStudyName & ".xml" For Output As 1

Print #1, "< xml version=" & Chr(34) & "1.0" & Chr(34) & " encoding=" & Chr(34) & "ISO-8859-1" & Chr(34) & " >"
Print #1, "<root>"
iCurLine = cstiStartLine
While objSheet.Cells(iCurLine, cstiVarCol).Value <> ""

strValue = objSheet.Cells(iCurLine, iEnglisCol)

Print #1, "<" & objSheet.Cells(iCurLine, cstiVarCol) & " label= " & Chr(34) & CheckContentsXML(strValue) & Chr(34) & "/>"

iCurLine = iCurLine + 1
Wend
Print #1, "</root>"
Close #1

As you can see, I have tried to specify "ISO-8859-1" in the XML header. If I specify "UTF-8" then xmlreader is generating an error at the first weird character it encounters such as "-"

If I try to specify "UTF-16", it then becomes a bunch of unreadable characters and my ASP.net display nothing.

Does anyone have an idea

Thanks a lot.