Free Microsoft Excel 2013 Quick Reference

How to create a UTF-8 text file from excel VBA

HI,

How do I create a UTF-8 encoded file in excel VBA?

I have tried using filesystem obeject with TriState property to true. This creates the file in Unicode format but not in UTF-8 format.

Thanks


HI,

How do I create a UTF-8 encoded file in excel VBA?

I have tried using filesystem obeject with TriState property to true.
This creates the file in Unicode format but not in UTF-8 format.

Thanks

--
mah_kum_70
------------------------------------------------------------------------
mah_kum_70's Profile: http://www.excelforum.com/member.php...fo&userid=2572
View this thread: http://www.excelforum.com/showthread...hreadid=537492

I've been searching for a while and it doesnt appear to be all that easy to load a UTF-8 text file into Excel using VBA. Say for example, a text file containing Chinese Text, saved as UTF-8.

This is the simplest / quickest method that I could come up with - using an ADODB stream to do the work. There were other alternatives available, but all failed to work on some level, or were awfully complex to work with. Hopefully this is a bit easier and will be useful to someone somewhere someday...


	VB:
	
 Read_UTF_8_Text_File() 
     'ensure reference is set to Microsoft ActiveX DataObjects library (the latest version of it).
     'under "tools/references"... references travel with the excel file, so once added, no need to worry.
     'if not you will get a type mismatch / library error on line below.
     
    Dim adoStream As ADODB.Stream 
    Dim var_String As Variant 
     
    Set adoStream = New ADODB.Stream 
     
    adoStream.Charset = "UTF-8" 
    adoStream.Open 
    adoStream.LoadFromFile "C:My-UTF8-TextFile.txt" 'change this to point to your text file
     
    var_String = Split(adoStream.ReadText, vbCrLf) 'split entire file into array - lines delimited by CRLF
    Range("A1").Resize(UBound(var_String) - LBound(var_String)).Value = Application.Transpose(var_String) 'output array to
activesheet.
     
End Sub 

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

Hi,

I have a basic excel file with a few columns with data.

Now I need to import this data to my financial system (iScala).

It seems iScala can only import textfile where each value starts at a
predefined position; hence it does not support "tab" or "space" delimited
values.

Does anyone know how to create a text file where all records are
"surrounded" by space and each record starts (at the same) predefined
position?

Example:

3 columns (A, B, C):

Name1 071001 452
Name123 071201 1200
Nam1 070905 51

Notice that "date" (column B) always start at position 9 and column C at
position 18... How to make this from a excel-file?

Kindly,
Mikael
Sweden

Hi everyone,

I am a very beginner in Access. I only know how to enter simple data. Can someone please show me how to create a formula in Access just like Excel?

Thanks,
Yuki

Is there a way to create a comma delimitted .txt file from Excel?

Or a semicolon delimitted file?

Thank you in advance for any help you can give me.

0down votefavorite
I don't know much about VB and need to do the following tasks.
My question in short is "How to create a text file from a column and FTP that text file to a server?"
Situation:
I have workbook with 2 sheets (Sheet1 and sheet2). Sheet1 has the data entered by the user and sheet2 has that data mapped in a required format.
What I am looking for:
I need to put 2 buttons on sheet1 Button1 should do the following:
Saves the workbook with.xls extension creates a text file from the values (untill the empty row) in sheet2 column C Displays a message once the above tasks are done or any error is returned
Button2 should do the following:
uploads the text file (created with button1 click) through FTP to a server Displays a message once the file is uploaded or any error is returned
Can anyone please help to achieve the above tasks?
Thanks in Advance
Addi

I would like to first state that I am very new to VBA, but am trying to learn it slowly but surely. I have attached two documents, ‘LU Vis’ and ‘Vis Standards’. The ‘LU Vis’ is a file that gets updated daily from an access database that I have no control over. The file I have attached is all made up but shows exactly what would be shown in the real file. What are missing from it are all the filters that the original one has on it that allows me to change the year, quarter, month, week, and day that I am looking at, but everything from row 8 and above is exactly what is shown. However, dependant on what I choose as my filters the names and numbers shown in row 9 and below change.

What I have shown in the file ‘Vis Standards’ is exactly what I am trying to create a macro for. In the ‘Vis Standard’ file, what I essentially need the macro to do is read the file ‘LU Vis’, specifically everything in column A, B, and C and from row 9 to whenever there is no longer any values listed. At times, there is the word “unknown” listed under a name instead of a number, which can be ignored. The numbers listed in the table FR and Standard are constant, except that in my actual file, there are 120 FR’s and standards. I need the macro to sort through ‘LU Vis’ then enter the Pcs and Hrs into the table in ‘Vis Standards’. Pcs corresponds to column B and Hours corresponds to column C, both respectively in ‘LU Vis’. I need each person to be put into their own tab within ‘Vis Standards’ in alphabetical order. If anyone could help with creating a macro that can do this that would be greatly appreciated. If possible, I would also love an explanation of what specific parts of the code does, being that I am trying to learn VBA at the same time. Thanks.

I have built a web query in my worksheet and the data are imported
every 5 minutes. I am trying to copy and paste the lastest data to the
far right column so that a time series can be made. However, I am not
sure how to create a macro that can be carried out repeatedly every 5
minutes and most importantly, the marco needs to be carried out after
the data is updated.

12:00 12:05 12:10 12:15 12:20 12:25 12:30

Type1 12.4% 12.6% 12.6% 12.4% 13.2% 13.9% 13.9%
Type2 6.8% 9.5% 18.2% 16.7% 15.8% 14.9% 13.9%
Type3 9.6% 6.8% 5.9% 5.1% 5.1% 4.8% 5.1%
Type4 3.9% 6.3% 5.9% 6.3% 6.8% 7.5% 8.2%
Type5 37.3% 25.6% 21.0% 19.1% 17.8% 16.1% 15.5%
Type6 4.3% 4.6% 4.6% 5.1% 5.1% 5.5% 5.5%

Can anyone help me on this? Thanks.

Excel 2003. FIRST, I want to just be able to print (to my default printer)
the contents of a text control on a user form. I was given the following
code, but the printout is funky (margins are super narrow).

Dim PrinterName As String, I As Integer
I = InStr(Application.ActivePrinter, " on ")
PrinterName = Mid(Application.ActivePrinter, 1, I - 1)
Open PrinterName For Output As #1
Print #1, ProcessStatus.ProcStat.Text
Close #1

IS THERE A BETTER WAY TO ACCOMPLISH THIS?

SECOND, How does one print out an external file from Excel VBA code.
Suppose for instance that I want to print out file "Test.txt." How would I
program that? Thanks for the help and God bless.

--
Dr. Doug Pruiett
Good News Jail & Prison Ministry
www.goodnewsjail.org

Hi

I have a 4000 row spreadsheet with approximately 33 columns and I have about
about 300 intermittent blank lines.

How can create a macro in the file to automate the process of deleting these
empty rows which I do manually?

I don't want to use the autofilter function to deal with this problem. I
would if possible to do this with a button or shortcut.

Anyone can help me this.

Thanks very much in advance
Janet

Hello.

I'm having problems trying to get some uniformity in our documents. The problem is that all of our company Winword documents (thousands of documents) already have a table in the footer in which the following information is listed in sequential order:

Machine no.:
Filename:
Version:
Date:
Page x of x

The table is made up of 2 rows and 5 columns.
The bottom row is updated by the worker that processes the file.

Furthermore, we do not edit the winword footer but instead edit the document properties because there are fields in the winword footer that are connected to the document properties. In other words machine no., version and date are edited in the document properties and this changes the footer.

Can this be done in Excel with a footer?

I have found out how to insert the properties into the footer (in Excel), but not how to create a table in the footer.

May I know how to create a excel file from the VB??
For example, the syntax below is to open a file, what is the syntax to create a new excel file, and I will put my output to the new file.

Eg
===
With Application
fName = .GetOpenFilename(Title:=iTitle, _
filefilter:=FilterList, FilterIndex:=2)
End With

Thank You.
regards,
DCCH

I have looked in books and online and can't seem to locate how to create a macro that opens a browse function. From Excel I would like to click a button to start a macro and have that macro prompt me where to browse for the file. There is a lot of other code that will go after this step (which I already have), so once I click to "open" a file I want the macro to continue.

I appreciate your help in advance!

How to create a User Form in Excel 2007 with several objects such as a Blank Text-Box, OPtion Button, Check Boxesand Command Buttons?

Dear Forum,

I am trying my hands at creating an USER form for the first time for making a Banking Application which takes all different types of Receipt and Withdrawal Types of Entries.

I have several types of entries for Outgoings (Debits) and Incomings (Credits) in account and want to use a Single form to records these transaction.

So, how do I start to make a User Form which can categorise these entries as well as offer options while entering..

I have about 25 Debits and around 13 Credits identified as per regular usage uptill now and that might increase however I will be happy to confine it at the specified limits.

I was doing this earlier using Dropdowns but realised that a User Form would be an appropriate choice..

However, I need some helping hand as I ahve never done it before and this is going to be a slightly bigger form to manage as there are some transactions which can be done in 2 types like I can pay my Electricity charges with a Cheque or directly through Internet..

Now, if this can be handled at the form level nothing like it..

Since I am not familiar with creating forms i will just use Colours to explain what I require?

Wam Regards
e4excel

I was wondering how to create a pull-down menu with certain choices for a cell.

Is this possible in Excel? If so, how?

Please Help!
Thanks!!

How to Create a Global User-Defined Function

For information about using references to call user-defined functions, please see the ... 141288 (http://support.microsoft.com/kb/141288/EN-US/) How to Use a Custom Function in ...

How To Create a Visual Basic Automation Add-in for Excel Worksheet ...

Article ID : 285337: Last Review : January 29, 2007: Revision : 5.3 ... 1. In Visual Basic, start a new AddIn project. 2. By default ... VBA functions in workbooks and regular Add-ins (.xla) also ...

How To Create a Visual Basic Automation Add-in for Excel Worksheet ...

... Object 'The Excel Application object Private Sub AddinInstance_OnAddInsUpdate(custom ... VBA functions in workbooks and regular Add-ins (.xla) also take precedence over ...

How To Create a Visual Basic Automation Add-in for Excel Worksheet Functions

Hi there,

I have a very large excel 2002 document with many worksheets. The worksheets are formatted identically but named randomly (like RMA3498 and IJ98M).

I am hoping that someone could give me some tips on how to create a formula or script.

I would like to create a new worksheet in the workbook that lists the data from each worksheet's F3 cell on a different cell in the new workbook. (I just want a list of what's in cell F3 for each sheet.)

I know how to do the =[Book1]Sheet1!$F$3, but since each page of the workbook is randomly named, I was hoping someone could explain how I could do a range of worksheets or some other workaround so I don't have to manually enter the formula for each cell.

Please let me know if you have a tip for this. I looked around extensively to find the answer before posting, but I couldn't find anything.

Thank you!

Hi,

I need help, I need to create a user form that opens from the click of a command button and does the following.

It asks the user for the following information:

initials - This data goes to cell k35
surname - data goes to cell l35
national insurance number - data goes to cell m35 (format must be Letter, letter, number,number,number,number,number,number, letter)
On caseload? - data is "yes or "No" and goes to cell n35
Parent? - data is again "Yes" or "No" and goes to cell O35
Benefit Type - data is "JSA" or "IS" or "IB" or "IS/IB" and goes to cell P35
Then lastly it asks for the clients postcode to be entered but must match this format (letter,letter,number,space,number,letter,letter) before the space i would like it to have the option of Letter, letter, number number. if possible.

Once they click "OK" i would like this data copied to the relevant cells and then the form is cleared. The they have the option of adding another row of data to the next empty row (row36) or exiting.

Can this be done and how???

many thanks you clever people

Hi,

i want to create a set of variables that form a structure like the "Range" or the "Shape" object for example.

Let me give an example to be more clear :

Lets say we have Student, Name, Surname, Age, ID, Math, Phsics, Grade, Attendance as variables.

I want to be able to use them as for example:

Set Student(1).Phsics.Attendance = "20"
or
Set Student(3).Name = "James"

Just like we can call Cells(3,2).Value = "45".

is it possible to form something like that and how can i do it ?

thanks in advance.

Hi,

I'm trying to chart a graph but I can't figure out how to create a second category. I know how to add a new series but I can't add a NEW categor so that I can chart data on a new bar. (I have select the stacked bar option)

The way I have it now, if I add new data my column stacks taller and taller but I want to add a NEW column.

Does that make sense? Please help!

How to create a countdown timer in a worksheet, like a stopwatch counting
down the seconds/minutes when you click on a START button until you click on
a STOP button