ANmarS - Search results

Excel/VBA expert since Sep 1997 (21 years 2 months)

We are moving ...

We are moving to another location, better, faster and more resources .... check it out ANmar.Systems

51 Q & A posts tagged with vb

Mon
Feb 6
2012

Copy pictures from sheet to another

i have two workbooks, lets say, workbook A and Library Workbook
Workbook A is where all the code has been going, Library has no code in it. My project in Workbook A needs to look up and pull a worksheet out of Library Workbook - just a direct copy. Simple enough in general, only one problem. Most of the sheets in Library Workbook have images embedded in them that dont get copied when doing a direct Workbooks(Library)Cells.Copy
i can get all of the formatting, column widths, and values/formulas, but not the images
now, there is one thing i havent tried that I think will work, but it has drawbacks...i could copy the entire sheet, not just the cells, but the sheet itself and put it in Workbook A. I think that this would work in theory, but Im reluctant to try it for one reason
I have embedded sheet code in Workbook A in the sheet that I want to be the destination for the copied library contents. The sheet code is event-based, and Id like it to stay in the code. So I was trying to paste the library contents into this sheet to retain the sheet code, where if I paste the entire sheet directly from the library, I dont get to keep the sheet code, because the library, by definition, is supposed to be (vba free)

so...Ive been chasing two different possible solutions, neither one with much luck yet.
(1) Learn how to copy cells from a worksheet with the images included
i know you can use vba to copy images, but the sheets have varying numbers of images, so theres no good way to programatically handle all sheets
shapes(1).copy, ...
might be more than one image


Answer

you still can use shapes.count and start a loop, did you tried that?
however, there might be a downside, when you copy and paste, they will all be over each other, but you can override it by using:
Sheet1.shapes(x).left = Sheet2.shapes(x).left
sheet1.shapes(x).top = sheet2.shapes(x).top
assuming copying shape from Sheet2 to Sheet1
does that make sense?

Monday 2/6/2012
Mon
Nov 21
2011

Can someone please help with this piece of visual basic excel code? 10 Points!?

Please go here for the code: http://pastebin.com/Hr4rhd3P
The reason I couldnt put it here was becasue for some reason it went past the character limit. Please could you check out my visual basic code in excel. I keep getting mainly Else without If Errors and Sin errors. Please could you go through my coding and check for the mistakes. Ty in advanced. Ten Points to best answer...


Answer

You need to give more details on what is exactly you need to do to have your code corrected

When I went through it trying to correct it, I got lost since I don't know what you want to do

It is in sub FFactorial indeed

plus Private Sub Factorial_Click()

Both have mistakes in IF statement

email me here with more details to go through it and correct your IFs

Monday 11/21/2011 5:07:16 PM
Wed
Nov 16
2011

Correct VBA Code on microsoft visual basic?

please help, it is not working. Thanks.

VBA CODE:

Sub StemAndLeaf()
dataColumn = 1

'Clean everything out of the Stem worksheet.
Worksheets("Stem").Cells.Clear

'Look at the Data worksheet.
Worksheets("Data").Activate

'Find the maximum value.
rowPointer = 2
Do Until Cells(rowPointer, 1).Value = ""
rowPointer = rowPointer + 1
Loop
Maximum = Cells(rowPointer - 1, dataColumn).Value

'Set the divisor to strip off leaves.
divisor = 1
Do Until Maximum / divisor <= 10
divisor = divisor * 10
Loop

'If the first digit of the largest value is less than 5, then
'use a smaller divisor.
'Otherwise you could end up with four or fewer rows in the plot.
If Fix(Maximum / divisor) < 5 Then divisor = divisor * 10

'Calculate the top stem's value.
topStem = Fix(Maximum / divisor)

'Set up the Stem worksheet.
Worksheets("Stem").Activate
Cells(1, 1).Value = "Count"
Cells(1, 2).Value = "Stem"
Cells(1, 3).Value = "Leaves"
For rowPointer = 2 To topStem + 2
Cells(rowPointer, 2).Value = rowPointer - 2
Cells(rowPointer, 3).Value = "|"
Next rowPointer

'Calculate the counts.
'The following code is slower than it needs to be,
'but a faster code would be harder to read and understand.
Worksheets("Data").Activate
rowPointer = 2
Do Until Cells(rowPointer, dataColumn).Value = ""
measurement = Cells(rowPointer, dataColumn).Value
Stem = Fix(measurement / divisor)
Worksheets("Stem").Cells(Stem + 2, 1).Value = Worksheets("Stem").Cells(Stem + 2, 1).Value + 1
rowPointer = rowPointer + 1
Loop

'Calculate the shrink factor.
Worksheets("Stem").Activate
maximumCount = 0
For rowPointer = 2 To topStem + 2
If Cells(rowPointer, 1).Value > maximumCount Then
maximumCount = Cells(rowPointer, 1).Value
End If
Next rowPointer

shrinkFactor = Fix(maximumCount / 50)
If shrinkFactor < 1 Then shrinkFactor = 1
Cells(1, 4).Value = "Each digit represents" + Str(shrinkFactor) + " cases."

'Return to the data, and fill the leaves in light of the values in the data.
Worksheets("Data").Activate
rowPointer = 2
Do Until Cells(rowPointer, dataColumn).Value = ""
measurement = Cells(rowPointer, dataColumn).Value
Stem = Fix(measurement / divisor)
leaf = measurement - Stem * divisor
leaf = Fix(leaf * 10 / divisor)

Worksheets("Stem").Cells(Stem + 2, 3).Value = Worksheets("Stem").Cells(Stem + 2, 3).Value + Trim(Str(leaf))
rowPointer = rowPointer + shrinkFactor
Loop

'Get to the Stem worksheet.
Worksheets("Stem").Activate
End Sub



Read more: How to Make a Stem & Leaf Plot in Excel 2007 | eHow.com http://www.ehow.com/how_6408182_make-leaf-plot-excel-2007.html#ixzz1dsvrvNfw


Answer

What is the problem? what error code generated?

as a start, you should have two sheets named "Data" and "Stem"

let me know, I will be glad to help, contact me here

Wednesday 11/16/2011 4:07:39 PM
Wed
Nov 16
2011

Beginner Programming Task Questions?

Hi all,

I have next to no programming knowledge but keen to learn.

My goal is to create a simple program ~ where it goes to a website, gets certain current information, puts it into MS excel and crunches some numbers for me (few further things i'd like after).

Assuming i'm starting with nothing what should I be looking at to make this happen? Programming language (im guessing C?+?) and what do i need to create a program using C+? anything else i need to know?

Many thanks.


Answer

Microsoft Excel itself has a programming language called (Visual Basic for Application) aka VBA or macros
I would suggest that you start with that
In addition you may need to learn some HTML top read website directly

That been said, there is a code that enables you to log in certain website and do whatever you need to do with it, like search, register, sign in, etc

Get it here as a start
http://www.access-programmers.co.uk/foru…
here
http://vba-corner.livejournal.com/4623.h…
and here
http://www.excely.com/excel-vba/ie-autom…

If you stuck somewhere you may always contact me, most of my help to others are free.

Wednesday 11/16/2011 9:21:06 AM
Sun
Nov 13
2011

How to exchange text into random number and then exchange back?

I need a code so that when I run a macro in excel it will exchange items enclosed in a parenthesis to a random number. For example the text in cell A1 is: {I have a (cat) and a (dog)}. {I have (two) cats and (three) dogs}.
What I need the macro to do is that:
1. Look at the cell and search for curly braces
2. Look for text enclosed in a parenthesis
3. Replace the text with a random number
4. Replace the number with the original text
5. Then move on to the next curly braces and repeat the process

So it would look like this:
1. {I have a (cat) and a (dog)}. {I have (two) cats and (three) dogs}.
2. {I have a (1.8) and a (3.4)}. {I have (two) cats and (three) dogs}.
3. {I have a (cat) and a (dog)}. {I have (two) cats and (three) dogs}.
4. {I have a (cat) and a (dog)}. {I have (.9) cats and (1) dogs}.
5. {I have a (cat) and a (dog)}. {I have (two) cats and (three) dogs}.

All your help will be greatly appreciated.


Answer

Try doing something like this

Range("A1").value = Replace(Range("A1").value, "(dog)", "(" & Int( Rand()*100) & ") dogs")

This is the main command that you will be needing
You can actually repeat it to cover all options like these:

Range("A1").value = Replace(Range("A1").value, "(dog)", "(" & Int( Rand()*100) & ") dogs")
Range("A1").value = Replace(Range("A1").value, "(mouse)", "(" & Int( Rand()*100) & ") mice")
Range("A1").value = Replace(Range("A1").value, "(cat)", "(" & Int( Rand()*100) & ") cats")


And then put it inside a loop to check for them, like this

X1=1
Max1 = range("A1" ).currentregion.rows.count
do until x1>max1
Range("A1").offset(X1 ).value = Replace(Range("A1").offset(X1 ).value, "(dog)", "(" & Int( Rand()*100) & ") dogs")
x1=x1+1
loop





let me know if you need more help

Sunday 11/13/2011 6:58:22 PM
Sun
Nov 13
2011

How to assign a random number as an id to a marked text in a cell in excel?

What code can I use so that I can assign a random number as a the ID for a text in a cell. For example cell A1 has these sentences:
{I have (three) dogs and (two) cats}. My pets always fight. {My (dog) is afraid of my (cat) and they are both afraid of (mice)}.
I need an excel macro code so that it will assign a random number as an ID for the items enclosed in the parenthesis. I need this macro to assign a random number ID one at a time. In other words what I want the macro to do is to:
1. Search for curly braces in the cell
2. Get all the items enclosed in the curly braces that are enclosed in parenthesis
3. Assign a random number as an ID to the items in the parenthesis without touching the other text
4. Then move on to the next text enclosed in curly braces and continue until there are no more curly braces in the cell

So what the macro will do is that it will look at the contents of the selected cell. Then look for curly braces then look for words that are enclosed in parenthesis and then assign a random number as an ID for each then move on to the next curly braces.

The curly braces and the parenthesis may be changed to any marker.

All help is greatly appreciated.


Answer

The main command you might need is:
Range("A1").value = Replace(Range("A1").value, "(dog)", "(" & Int( Rand()*100) & ") dogs")

Doing it repeatedly is the trick that you will be using to replace all options once found, note that "Replace" works only if that text is found

And then put it inside a loop to check for them, like this

X1=1
Max1 = range("A1" ).currentregion.rows.count
do until x1>max1
Range("A1").offset(X1 ).value = Replace(Range("A1").offset(X1 ).value, "(dog)", "(" & Int( Rand()*100) & ") dogs")
Range("A1").offset(X1 ).value = Replace(Range("A1").offset(X1 ).value, "(cat)", "(" & Int( Rand()*100) & ") cats")
Range("A1").offset(X1 ).value = Replace(Range("A1").offset(X1 ).value, "(mouse)", "(" & Int( Rand()*100) & ") mice")
x1=x1+1
loop


let me know if you need more help

Sunday 11/13/2011 6:47:20 PM
Sat
Nov 12
2011

How do you insert external data into excel STARTER?

This question is specifically for Microsoft Excel STARTER. It's not like on other versions of excel where you have a data tab and can follow the steps from there.
I have already checked google also so please don't suggest pages on their or searching it on google. I have also checked the microsoft website.

BASICALLY, if you personally know how you insert data into Microsoft Excel STARTER I would really appreciate the steps on how to do it :) And will rate best answer for whoever tells me. Thanks


Answer

There are actually several ways, basically using VBA or macros
If you know how to do that there are several macros out there

I would suggest one of these
http://www.anmars.com/Work/?P=598866300
to read directly from MySQL DB into Excel sheet

or use
http://social.msdn.microsoft.com/Forums/…
to read from sql or Access

Also
http://www.bygsoftware.com/Excel/SQL/Usi…

Again, those are macros (Using the back door for Excel) to insert these data

If none of them helped, let me know from where are you inserting the data and I would be glad to show you the way.

let me know if that helped

Saturday 11/12/2011 2:17:09 AM
Mon
May 3
2010

Need help putting quotes around my text in excel spreadsheet?

Hi,

I have an excel spreadsheet with different words in all the cells. I need to put quotes around each word. I have tried using a macro but I am new to it and so far have not been able to get it to not just paste the exact same word I used to record the macro. Every cell is a different word. I need the macro to ignore the word in the cell and just put quotes around each individual word. Can anyone help with this??? I appreciate it!


Answer

The char code for the quotes is 34
Meaning, if you use Chr(34) in VBA code
or
=CHAR(34) in Excel formulas, you will get the quote

Maybe something like this, in formula bar
=CHAR(34)&C1&CHAR(34)

or

Range("A1").value= Chr(34) & Range("A1").value & Chr(34)

Hope this will help

VBAXLMan

Monday 5/3/2010 1:00:36 PM
Mon
Apr 26
2010

How to add check boxes to Excel 2007 to a column if a the row has data?

I want to have check boxes in column D, but I dont want the box boxes to appear unless data is intered into column A, that way I can keep the spread sheet clean because I will be adding data to this spread sheet daily. I have no idea how to even start this process. Any help would be great! Thanks!


Answer

The only ay to add checkbox automatically is using VBA or macros

However, there is another way, but using Validation instead
You can use Validation based on Validation, or Validation based on your value in column A
Make the validation list of Yes, No and you will get something similar to check box

Check out Multiple validations here
http://www.file1.net/lesson.asp?id=6y0x6…

VBAXLMan

Monday 4/26/2010 7:24:31 PM
Fri
Apr 16
2010

How to work with open Excel workbooks where filenames are given in cells?

I have the user input the file name into a cell. There are several open Excel files so the user will call out the name of one. What is the syntax to reference the named file so I can copy/paste?

The files come from emails so want to bypass having to save them and use the
...Workbooks.Open(Range("A5").Value)
command.
I've tried lots of variations with syntax but can't get it.


Answer

You need to create a loop through all open files, like this
Now, if you have the cell that holds the file name in A12 sheet1, then do this

MyFileName = Sheet1.Range( "A12").Value
For I=1 to Workbooks.Count
If UCase(Workbooks(i).Name) = UCase( MyFileName) then
Workbooks(i).Activate
Exit For
End If
Next

I used Activate because doping Open while the workbook is already open will generate an error

Try it and let me know (using my profile) if that solved it or not

VBAXLMan

Friday 4/16/2010 8:40:37 AM
Tue
Apr 13
2010

How do you write a VBA function to work in any Excel spreadsheet you have open?

I wrote a function and saved it in my personal.xls file. When I want to call that function from another spreadsheet, I have to write Personal.xls!FunctionName(). Is there a way to write the code so that I don't have to reference my personal file each time?


Answer

That is because your function name is the same as a build-in function already used in Excel
Try a unique function name, like MYFunc0001

That is the only reason I see, otherwise, the function would be work easily as long as the function file is open

I am using that all the time

let me know

VBAXLMan

Tuesday 4/13/2010 7:47:09 AM
Sun
Apr 11
2010

How do you add an npv function into vba for cash flows?

I have interest rate as 10% and future value of 1,000. I've already done everything in VBA that allows excel to show 10 years of present values, future values and compounding factors. I just don't know how to add a function in for NPV so that a specific cell would add up all the present values that were already calculated with the formula pv = fv / (1+irate) ^ 1 using VBA and not excel's npv function or sum of values.


Answer

You have several options here
1- Use the mathematical way to calculate the NPV
2- Use some temporary cells to set the values and use NPV in another cell do calculation, then read the result
3- And the one I used a lot is to use WorkSheetFunction object to use Excel function in VBA, like this
NewValue = Worksheetfunction.NPV( MyRate, MyAmount, etc)

If none of those helped, that means I didn't get your question right, reply me here.

VBAXLMan

Sunday 4/11/2010 4:36:44 PM
Sat
Apr 10
2010

How do I put multiple criteria in a case statement in excel visual basic?

Here's my problem: if cell k4>0 and b4>100, then I want b3 rounded down to the next digit. If cell k4<0 and b4>100, then I want be rounded up to the next digit. If cell k4>0 and b4<100, then I want b3 rounded down to the nearest 1/100. If cell k4<0 and b4<100, then I want b3 rounded up to the nearest 1/100. Since this analysis is done only once in the spreadsheet, I don't think I need a loop statement. Thanks.


Answer

You want that in VBA in Excel, right?
Because if you want, you can use formulas to do that.
I will assume you want it in VBA, since nested IF is not that hard to figure out.

Case statement work on one variable or criteria
Meaning, if you insist using Case, then you will need multiple Case statements, like this


Select Case Sheet1.Range( "K4").Value
Case >0
Select Case Sheet1.Range( "B4").Value
Case >100
Sheet1.Range( "B3").Value= NewValue1
Case <100
Sheet1.Range( "B3").Value= NewValue2
End Select
...
...
End Select


And I guess you know the rest

Let me know if you need more than this

VBAXLMan

Saturday 4/10/2010 8:21:33 PM
Tue
Apr 6
2010

What is the VBA code for Convert Excel Worksheet in to PDF in the Name of the Text of specified Cell?

Please Mention the VBA code to convert Excel work sheet in to PDF in the name specified in the particular cell of the Excel Sheet.


Answer

I am assuming you do know how to play around with VBA
In this case, install this PDF Printer
http://www.primopdf.com/

Then print the Excel file through VBA (Record macro and see how it does it)
Then use this VBA line to rename the exported file
Rename [originfilename.ext] AS [newfilename.ext]
Or
Save the file itself (Excel file) with that name from cell, then do the export

PrimoPDF usually saves the file in the same name as the Excel file

Good luck

VBAXLMan http://blog.soliddocuments.com/2010/01/b…

Tuesday 4/6/2010 7:34:07 AM
Tue
Apr 6
2010

How to change the author of excel macros?

how to change the author of excel macros?

i've gone into visual basic and replaced the original author's name with my name, but it still appears with the original authors name when i use the macros in excel.

Thanks!


Answer

Excel saves these info usually in the few lines of the macro (the ones that start with ' )

So editing that will do it for most cases

However, you might also looking for the Author of the file itself (The file that has the macro), change that also in Right click in Explorer > Properties

Hope that is what you are looking for

VBAXLMan

Tuesday 4/6/2010 4:38:23 AM
Sun
Apr 4
2010

Create an automatic table based on the past three months?

hi, i have an excel sheet with the past five years data on it. Each row represents a month so each month it gets one row longer.

I already have a defined range that selects the most recent 3 months, but i need to know how to display the last 3 rows and the field headings on another sheet in a specific position by the click of a button(macro).

I can make a line graph using the last three rows of data with just a click of a button and now i want to show the data used underneath the chart.

Thanks for all answers


Answer

You don't need a macro to do that
You can make it in Formulas which means faster, auto updated, etc

Let's start with some assumptions:
- You have two sheets (Sheet1 has the full data and Sheet2 to have the last three months)
- In Sheet1, your data starts from cell A1 along to V1, with headers in first row and the data below it.
- In Sheet2, you want the last three months to be in A2 to V2, A1 to V1 has the row headers

Now, in Cells A1:V1, just copy the headers (you need to do that once)
In cell A2, paste this
=OFFSET( Sheet1!$A$1,COUNTA( Sheet1!$A:$A)-ABS(ROW()-1),COLUMN( )-1)

Then copy A2 along to V2, then copy Row2 into Row3 and Row4

These three rows will bring you the last three rows from your table in Sheet1 assuming you don't have any empty cells in that table in column A
And when you add new values at the end of that table in Sheet1, these will automatically updated

Does that make sense?

let me know

VBAXLMan

Sunday 4/4/2010 11:13:43 AM
Sun
Apr 4
2010

How do I copy particular images from one excel file to another in a automatic manner?

I have an excel file with more than ten thousand of product details and its images. Most of the times I am getting the requirement for images of 100 or 200 random items. Each time I have to search manually and copy those image to the new file. It would be great if there is any 'program' or 'shortcut' to paste those images from the original file to the newly requested file.
Thanks in advance


Answer

Excel has the most powerful macros (VBA), that we can use
I recommend saving the images out in a seperate folder, naming them with certain mask that has the product ID
Then, when you need a product image, you can easily let the macros to call its image

I use this technique all the times, however, you might need some VBA skills

VBAXLMan www.samotech.net

Sunday 4/4/2010 12:16:30 AM
Fri
Apr 2
2010

How can I put a private sub within a private sub?

I am working with visual basic 2008 and I want to create a timelog within excel but have the clock in and clock out within the same worksheet. I got it to work but the times are in different worksheets or should i say different books. I just need to know how to put them together here is the code:
Thanks

Public Class Form1


Private Sub Timer1_Tick(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Timer1.Tick
Label1.Text = TimeOfDay

End Sub


Private Sub ClockIn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ClockIn.Click

Dim Time As String = Now()

Dim Name As String = TextBox1.Text()


'writes time to label on form

Label2.Text = Time

Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object

'Start a new workbook in Excel
oExcel = CreateObject("Excel.Application")
oBook = oExcel.worksheet.Add


'Add data to cells of the first worksheet in the new workbook
oSheet = oBook.Worksheets(1)
oSheet.Range("A1").Value = Name
oSheet.Range("A1").Font.Bold = True
oSheet.Range("B1").Value = "Clock In"
oSheet.Range("B1").Font.Bold = True

'Adding the time
oSheet.Range("B2").Value = Time


End Sub


Private Sub ClockOut_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ClockOut.Click
Dim Time As String = Label2.Text

Dim Time2 As String = Now()

Dim Name As String = TextBox1.Text()

'writes time to label on form

Label3.Text = Time2


Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object

'Start a new workbook in Excel
oExcel = CreateObject("Excel.Application")
oBook = oExcel.Worksheet.Add


'Add data to cells of the first worksheet in the new workbook
oSheet = oBook.Worksheets(1)

oSheet.Range("C1").Value = "Clock Out"
oSheet.Range("C1").Font.Bold = True

'Adding the time
oSheet.Range("C2").Value = Time2
'Save the Workbook and Quit Excel
oBook.SaveAs("C:\Book1.xls")
oExcel.Quit()
End Sub


End Class


Answer

First of all, your question is confusing

You can not put sub inside another
Yes, you can call it from the other one, but not put it there

As far as I understood your question, here is my suggested answer

You need to Open that Excel file first, then modify it, then save it
What you are doing now is creating new workbook every ClockIn/ClockOut

I recommend putting these three in one workbook, call it main.xls for example
And open one workbook (already created) , add new rows for timein/timeout, save it and close, instead of creating new one every time

VBAXLMan

Friday 4/2/2010 5:44:51 PM
Sat
Apr 25
2009

Code in Sheets vs. Code in Modules; Excel?

I transferred all my module code into specific worksheets modifying Subs to Private Subs and replacing Form Controls with ActiveX controls. Am I running into any disadvantages doing so? Is it helping me any, besides the fact that pressing ActiveX buttons feel more like clicking buttons than Form Control buttons do?


Answer

I can think of one...
If you want to do a USER-DEFINED-FUNCTION, then you can not do it in a worksheet module, you would have to do it in a Module

Other than this, You still can call any sub/function in a worksheet form other worksheets, using the "Public" key before the sub/function name


One more thing, selecting ActiveX or Form controls are not affected by Module or sheet module


VBAXLMan

Saturday 4/25/2009 1:48:16 PM
Sat
Apr 25
2009

Excel 2003 VBA searching and editing data using a userform?

Hey!
I have a lot of data on an excel spreadsheet which has many columns for different required fields of pieces of data.
New data can be input using a user form, and this works well. However now i would like the user to be able to search for a row of data and update/edit the details of this data using a userform. How can i do this? (I have already figured out how to use the Search command, just cant do the updating!)


Answer

You would need some VBA work to be done

If you are not the one who did that userform, then contact that guy to do that for you

If you like, send me the file (or sample file) to
XL@VBAXLMan.com

And I would be very happy to do that for you, I live on these Excel things


Let me know your decision

VBAXLMan

Saturday 4/25/2009 7:32:35 AM
Fri
Apr 24
2009

How do I get Excel to do this?

Let's say I type a numerical value into cell A1. How do I set it to auto-calculate 56% of that value and plop it back into that same cell? In other words, set a cell to display a calculated value of a number a user inputs into the same cell.


Answer

You would need a macro to do that, try this

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
Target.Value = Val(Target.Value) * 65%
End If
End Sub

The 1 is for column A, change it to 2 if you have B, 3 if you have C, etc
Also change the 65% to whatever percentage you may have


To use that code, press ALT+F11, double click on the sheet name in the "Project Excplorer" window (Up left) and paste this code


Mail me here if you have problems


VBAXLMan

Friday 4/24/2009 7:08:35 PM
Thu
Apr 23
2009

[Excel/VBA] How to prevent excel to scroll up after typing ?

Hi

Each time I enter something at the bottom of a sheet, excel scrolls up the page (half of window height).
Is there a way to prevent (or reduce) this ?

Or some VBA to place a given row (last one) at the bottom of the window ?

Thanks in advance


Answer

First part of your question, no is the answer

For the second part, yes there is, it will depend on the Zoom percentage you are having and the height of your Excel screen

Try doing a macro to...
1- Scroll to the end of the end of your table (last row on your table)
2- Scroll back up to 10/20/30 rows
3- Activate any cell in the last row of your table

Something like this

Sub ShowLast()
CC=Worksheetfunction.Counta( Range("A:A"))
Range("A1").Offset( CC).Activate
Range("A1").Offset(CC-20).Activate
Range("A1").Offset( CC).Activate
End Sub


You need to try it and change the 20 to what ever suitable for you



Good luck



VBAXLMan

Thursday 4/23/2009 11:24:16 PM
Thu
Apr 23
2009

Microsoft Excel - Treeview 6.0 in User form?

I have treeview 6.0 in a user form. Does anyone know how to link charts or data to the tree view, to display in a window in the user form when clicking a node? Any help would be GREATLY appreciated. Thanks!


Answer

Since you are in the VBE of Excel, you can do this

- Draw a picture box that shows when the user clicks the node
- Copy the chart and paste it as picture in some temp sheet
- Then read that picture into that picture box

This is what I have in mind right now, I think there are other ways...

Let me know what you came up with, I liked your advanced question


VBAXLMan

Thursday 4/23/2009 9:05:09 AM
Wed
Apr 22
2009

How Do I Send Bulk Emails With Attachments Unique For Each Contact?

Hi,

With help in the past I've been able to grow my excel VBA knowledge widely.

I've been able to create new workbooks and save them out according to a company name in cell "E2" and a date in cell "G2".

So I now have a list of 40+ excel workbooks uniquely named by 'Company' and date and saved in the companies folder on a network drive that need to be emailed to each of the unique companies each month.

What I need to do to save me time is to automate the bulk emails and attachments.

I've found links and codes for sending an email for one attachment or email for the active workbook, what I can't seem to find are suggestions for sending out emails according to a list of addresses (from excel) then have a pop up saying:

"<company name> email ready, please select an attachment"
and have a dialogue box for you to get the attachment.

then loop to the next company name and address with a standard body text for each email.

Then Send all the emails out.

Any help would be GREATLY appreciated.

p.s. also, how would one set the 'from' part.


Answer

I know this one now
http://www.file1.net/project.asp?id=4S37…

This will send bulk emails with separate subject and body (No attachments)

I think I can (or you can) enhance it to add a column with the path to attachment file, then play with the code to do the attachment

Let me know if you can do this, I am sure can do that for you, mail me here



VBAXLMan

Wednesday 4/22/2009 12:26:28 AM
Tue
Apr 21
2009

How to disable save as option in excel & Word?

How to disable save as option in excel & Word, to protect the file from copying. But the Respective person should have the option available.


Answer

In Excel 2003, put this VBA code in "Workbook" object after you press ALT+F11
Private Sub Workbook_Open()
disablesaveas
End Sub

Then Add "Module" and paste this in
Sub disblesaveas()
CommandBars("File").Controls("Save As...").Enabled = False
End Sub

Then close the file and open it again

If you have Excel 2007, (You can also do this in Excel2003) do this
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If SaveAsUI Then Cancel = True
End Sub


Good luck


VBAXLMan

Tuesday 4/21/2009 8:50:19 AM
Sat
Apr 18
2009

Macro to check character count & give error if exceeds 500, and display d cell with error in msgbox.plz help?

Macro to check the character count in excel worksheet and give error if exceeds charatcers exceeds 500 including spaces and other special characters. Also display the cell number with more than the specified limit in a msgbox. Pls help


Answer

You can not read the number of chars in a specified cell until the user hits Enter or selects another cell

That including VBA or Validation

If that is OK with you, you can use the Data > Validation method to achieve that
Here is a lesson on how to do that in Data > Validation in Excel2007
http://www.file1.net/lesson.asp?id=FLn3D…

VBAXLMan

Saturday 4/18/2009 5:48:23 AM
Mon
Apr 13
2009

VBA to show excel path name with server path?

Currently I am trying to have VBA paste the file and path to cell A1 of the current file I am using. The problem I keep having is that I get which includes the mapped drive letter instead of the server path:

H:\FILES-Excel\testbook001.xls

but what I need to get is the server name path instead i.e.

\\ServerXYZ\FILES-Excel\testbook001.xl…

Does anyone have code to do this?


Answer

You will need to UNMAP the server path, open the file and run the command that will give you the full path as you required

OR try this command, if you are not using it anyway

Path1 = ActiveWorkbook.Path

Try it

VBAXLMan

Monday 4/13/2009 9:35:21 PM
Mon
Apr 13
2009

How to add Counter to an excel workbook?

How to add Counter to an excel workbook, such that we can track how many times the excel had been viewed. The countability can be tracked?


Answer

- Press ALT + F11 to open VB Editor
- Double click on the "Workbook" object in the Project Explorer window
- Paste this in the white code area
Private Sub Workbook_Open()
Sheets( "Sheet1").Range("A1") = val(Sheets( "Sheet1").Range("A1")) + 1
End Sub

- You need to make sure that your sheet is named "Sheet1", if not, just put the name of your sheet instead of Sheet1 in this line
Sheets( "Sheet1").Range("A1") = val(Sheets( "Sheet1").Range("A1")) + 1


Good luck

VBAXLMan

Monday 4/13/2009 7:45:11 AM
Tue
Apr 7
2009

Filtering data in Excel 2007 based on formatting?

I have two columns I need to filter. Some have text and some have text with strike-through (a line going through the text). Does anyone know how I can filter based on the strike-through - or if that's not an option can I filter based on anything that is not formatted like the regular text so it will separate the cells with the strike-through? Thank you.


Answer

There have been a lot of requests to read format through formulas

Since it is nit supported yet, here is a user definec function to do that

Function IsStrickeThrough(CellAddress As Range) As Boolean
If CellAddress.Rows.Count = 1 And CellAddress.Columns.Count = 1 Then
IsStrickeThrough = CellAddress.Font.Strikethrough
End If
End Function

To use it...
- Press ALT + F11
- Insert > Module
- Paste this
- Now go to any cell and use it like this
=IsStrickeThrough( A1)
Assuming the cell you want to check is A1



Enjoy it



VBAXLMan

Tuesday 4/7/2009 9:01:21 AM
Sat
Apr 4
2009

How to solve this problem with Excel conditional formatting?

I have a table on an Excel spreadsheet, which table contains only numbers. I want to color a "path" in this table using the following algorithm:

1) Make current the upper left cell and color it.

2) Examine the cell to the right of the current one and the cell beneath the current one. Make current the one that contains the smaller of the two numbers and color it. If the two numbers are equal, make current and color the cell beneath the current one.

3) Repeat 2) until the bottom or the right edge of the table is reached, whichever occurs first.

Can this be done using only the means of Excel's conditional formatting (i.e., no VBA) and how? That is, what formulas for conditional formatting to use?


Answer

Conditional formatting will "FORMAT" the cell and not change the cell content

To change cell content (or value) you will need a function

Based on your request, IF function would do the trick for you
Now, if your first cell is A1, then paste this into it
=IF( OR(A2="",B1=""),"", A2<B1, A1, IF( A2>B1, B1, A2))

Then fill cell A1 down

Now, Since the Conditional Formatting is to "FORMAT" cells, I don't see where you need it here

let me know

VBAXLMan

Saturday 4/4/2009 11:14:45 AM
Wed
Feb 4
2009

Excel 2003 - VB - Accessing abilities between modules for public functions?

I have one module with:
"Public Function Code128B(inpara As String) As String"
and then the function (i'm pretty sure it's copyrighted etc; it said Shareware on a few sites i was at and it's for barcodes)

I have another module with:
----------
...
Dim foot As String
foot = ActiveWorkbook.Name + " " + ActiveSheet.Name
ActiveSheet.PageSetup.CenterFooter = "&""MRV Code128bMA,Regular""&14 " & Code128B(foot)
...
----------

And the error is returning is that it is an "Ambiguous Code Name"

Is there something fishy going between modules with public functions? I'd think that a public function means it's always available to every module but I'm not sure.

If this is not enough data to solve the problem say so and I'll just let the whole thing drop (it's not my code or job to fix it :P )


Answer

You are right
"Public" will set this function public to every other module inside that workbook
And I quote "That Workbook"
If you are running this from another workbook, you need to call the function in another way
Workbook1.xls!Code128B(Foot)

If you are using the same workbook, then you may need to check two stuff:
1- You have a reference to the library that is used by that function
Some function requires you to add a reference (Toos > References) to a specified library to make it rub
2- You don't have the "Option Explicit" in any of your modules

If you still face the problem again, mail me some sample files, I can do it for you (May be for free)

VBAXLMan is here to feed your Excel needs

Wednesday 2/4/2009 9:05:51 AM
Wed
Jan 21
2009

How do you make a password field in excel so when you type in one of the cells, it comes up as asterisks *****?


Answer

Excel cell does not allow you to do this, you need to do an alternative ways, you got two:
1- You can use the TextBox from Forms toolbar (If you have Excel2003) or from Developer tab (If you have Excel2007)
2- Add a VBA form and add the text box to it.

Wether you do 1 or 2 you need to change the password property of it to make it show you * when you type in it

Again Excel cells can not allow you to do that, forget it

If you need help in doing this, or you don't know how to add a VBA form or Textbox to your Excel sheet, mail me here

VBAXLMan

Wednesday 1/21/2009 2:21:07 PM
Wed
Jan 21
2009

Multiple pivot tables controlled by a single set of page fields to automatically update all tables?

Hi,

Is it possible in Excel to update multiple pivot tables, all using the same page fields, by changing the page fields just once instead of having to change them for each table?

Thanks


Answer

Sure
When you build the pivot tables, make them reading the values from that source table

Once you update your fields, you may need to refresh each pivot table, or use the macro to make updation for all you pivot tables like this

ActiveSheet.PivotTables( "PivotTable1").PivotCache.Refresh

All you need to add is a loop through all your sheets with pivot tables and refresh each one

For i=1 to ThisWorkbook.Worksheets.Count
Worksheets( i).PivotTables( "PivotTable1").PivotCache.Refresh
Next i

Also you may need to make sure that each Pivot is named "PivotTable1" in each sheet

VBAXLMan is back to feed your Excel needs

Wednesday 1/21/2009 5:28:24 AM
Tue
Jan 20
2009

Debug error 3001 on VBA when I try a sequential search of the database.?

I am trying to subtract the quantity ordered by a customer by the Number In Stock of a particular product. To do this, I needed a sequential search, and I used a code which I thought of. However, it brings up a "Run-time error '3001': Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another."
The Debug error is this:
rst.Find "ProductID = " & Me!ProductID, 0, adSearchForward, 1
Here is the code:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
Dim con As New ADODB.Connection
Dim rst As ADODB.RecordSet

Set con = CurrentProject.Connection
Set rst = New ADODB.RecordSet

rst.Open "tblProduct", con, adOpenKeyset, adLockOptimistic

rst.Find "ProductID = " & Me!ProductID, 0, adSearchForward, 1
rst("NumberInStock") = rst("NumberInStock") - Me!QuantityOrdered
rst.Update
rst.Close

Set rst = Nothing
Set con = Nothing

End If
End Sub


Answer

- Is the name of field you want to search in named "ProductID" too?
- Did you gave all arguments of "Find"?
- What about the result of Find, shouldn't you assign the result to a variable?
- Are ProductID columns from both sides in the same type?

VBAXLMan

Tuesday 1/20/2009 2:05:12 PM
Sat
Jan 17
2009

I want to fix a pic in MS Excel 2007 cell and to use in an another sheet with Vlook up formula?

For each cell of employee I want to fix a pic and to use in forms with Vlookup formula


Answer

Excel functions are working with text and numbers
You can not do this with functions, instead you will need macros to do that

I would suggest to do a folder with employee pictures named with employee ids
Then insert a picturebox in that Excel file and do some macros to make it read the selected employee picture.

Something like this:
Sub ReadPic()
EmployeeID =Range("A1").value
PictureBox1.picture=LoadPicture( "D:\Pix\" & EmployeeID & ".jpg")
End Sub

This will read the picture in the picturebox assuming cell A1 had the employee id

I will be glad to help if you just send me the file you are talking about and more details to build the macro for you

VBAXLMan

Saturday 1/17/2009 10:34:54 AM
Fri
Jan 16
2009

Excel - How to get the frequency of the words in a para?

The para contains 1000 words. I need to find out the the top 50 words appearing most often in the para together with the number of appearances only using excel commands - can't use VBA or any other s/w!

any help ??


Answer

The only way to do it is to convert that para into cells (For temporary)

Now, if you have that cell in A1, paste this in B1
=LEFT( A1,SEARCH( " ",A1)-1)
And this in C1
=LEN( B1)
And this in D1
=COUNTIF( B:B,B1)
And this in B2
=MID( $A$1,SUM( $C$1:C1)+2,SEARCH( " ",$A$1,SUM( C1:C1)))
and copy C1 to C2, D1 to D2

Now copy cells B2, C2 and D2 down until you find all the text you have in that para in the cells of column B

Now, the D column will give you the number of any corresponding text found in cell A1

The interesting thing, is that when you change cell A1, all of these will be changed automatically


Enjoy it, Yes, I know I am good at this, I am the VBAXLMan here

Friday 1/16/2009 6:13:55 PM
Fri
Jan 16
2009

Excel e-mails me via a macro. Can Excel e-mail me as per my macro even when the workbook is closed?

I just added a macro to Excel that will e-mail me when a cell value changes from '1' to '0' from live data. Will it e-mail me even when the workbook is closed or does the spreadsheet need to be open in order for the macro to take effect?


Answer

If the workbook is not opened, the value of cell will not be changed
Meaning the email will not be send

Yes, the Excel file have to be opened in order to execute the macro and email to be send

VBAXLMan

Friday 1/16/2009 1:08:00 PM
Fri
Jan 16
2009

I want to convert 200 EXCEL files to TEXT files. Is there an easy way to do this?

I know I can do each one separately by opening it and saving it with a different format. But it would take a while to do this 300 times. There seems to be programs that can do this that cost money. Is there a free one. Any ideas? Thanks!


Answer

The free option is a macro inside Excel file, something like this

Sub ConvertAll()
ThisP= Thisworkbook.Path & "\"
For I=1 to 300
Workbooks.Open ThisP & "File" & i & ".xls"
ActiveWorkbook.SaveAs ThisP & "File" & i & ".txt" , xlUnicodeText
Next I
End Sub

Now this macro assumes:
1- You are having these 300 files in the same folder of the one having this macro
2- Your files are named as File1.xls, File2.xls, etc

You can change that macro as you like, or email me here to change it for you

VBAXLMan is back to feed your Excel needs

Friday 1/16/2009 10:07:55 AM
Fri
Jan 16
2009

Is it possible to add a calendar alert to Excel spreadsheet?

I've created a Excel '07 spreadsheet as a calendar. Is it possible to insert pop-up alerts/reminders in the spreadsheet?


Answer

Yes, using macros, for example:

Put this code in the Workbook module in the VBA (ALT+F11)
Private Sub Workbook_Open()
Application.OnTime Worksheets( "Sheet1").Range( "A1").Value, AlertSub
End Sub

And put this in a new Module after you insert it

Sub AlertSub()
MSGBOX "Alert", VBOkOnly
End Sub

Assuming you have the cell A1 in Sheet1 having the time that you want to alert in

Close the Excel file, and reopen it again with macros enabled, then it is done

VBAXLMan is back to feed your Excel needs

Friday 1/16/2009 9:14:43 AM
Fri
Jan 16
2009

Excel, Update sheets automatically?

Hi,

I have this sheet which i am currently working on - what i would like to do is every time a new record is added to Sheet1, it automatically updates Sheet2.

Sheet1 has 6 categories but every record is just added to the list. Sheet2 has records by categories. It has to be able to find the correct category in Sheet2 and update it with the new record.

Thanks.


Answer

This can not be done in Excel formulas
You will need to do one of the following:
1- Do a Pivot Table in Sheet2 to get the result of each category in certain columns (Might need to do more than pivot table)
2- Do a macro that is refreshed every time you change a cell in sheet1

I can not show you how to do the PivotTable here since it is different between Excel2007 and Excel2003

Also the macro thing cannot be done here, since I need more details from you, but it can be something like this:

- In Excel, Press ALT+F11
- Double click the sheet name (Sheet1 in your case) in the left tree list
- Paste this

Private Sub Worksheet_Change(ByVal Target As Range)
' Macro should be here
End Sub

The macro you need to put here is the macro that I will do it for you for free, if you send me your file to
XL@VBAXLMan.com
with some additional details

VBAXLMan

Friday 1/16/2009 8:13:22 AM
Fri
Jan 16
2009

How can retrieve the InstallDate for Windows and MS Office with an Excel Macro ?


Answer

The date of Windows installation is the date that Administrator account was created (or your User account)
And the date of Office istalled is the date of Microsoft Office folder was created
So
Depend on your Windows version (VISTA or XP) use this code to get the installation date/time of Windows

Sub WinDate()
Dim fs, fo
Set fs = Server.CreateObject("Scripting.FileSyste…
Set fo = fs.GetFolder("c:\Users\Administrator")
MsgBox fo.DateCreated
End Sub

Change the c:\Users\Administrator to c:\Documents and Settings\Administrator if you have WinXP
(Although the second one will run in VISTA as well)

Do the same for c:\Program Files\Microsoft Office

good luck

VBAXLMan is back

Friday 1/16/2009 3:59:00 AM
Thu
Jan 18
2007

Microsoft Excel: How to show picture if conditions met?

Hi there,

Is it possible to use some kind of formula to show a picture (which is located on another sheet) if specific conditions are met?
For example: I would like to show a green sign if a value is higher than 100 and a red sign if a value is below 100.

Thanks.

Stefan.


Answer

If Conditional Formatting is not enough for you, (It can color the cell based on condition)
Then, it is a VBA
You will need to create a picture image from
Tools > Customize > Control toolbox
and add some macro to load an image based on a cell value

I will be glad to help, if you mail me here in Y! Answers

Enjoy my profile, I am the VBAXLMan

Thursday 1/18/2007 11:01:39 AM
Thu
Jan 18
2007

How to create excel macro to fill in empty cells based on information in abolve cell?

I am looking to write macro that fills in the empty cells in certain range based on info of above info. Idea is to populate cells that have been left empty after pivot table results are copied as value in other worksheet.
It should execute starting from top and going down row by row in selected range.
Example: Row 1 has text "Formula", row two has empty cell. If macro detects empty cell it should populate it with info from cell above (e.g. Formula). It is kind of auto copy function


Answer

It is amacro, right?
So, you will need to create two loops and check if a cell is empty, if so, fill the cell from the above cell, then:

Sub FillFromAbove()
For i=1 to 4 ' column A, B, C and D
For j=2 to 7 ' rows 2, 3, 4, 5, 6 and 7 , so the range is A2:D7
If Worksheets( "Sheet1").Range( "A1").Offset( j-1, i-1).Value ="" then Worksheets( "Sheet1").Range( "A1").Offset( j-1, i-1).Value = Worksheets( "Sheet1").Range( "A1").Offset( j-2, i-1).Value ' to fill from the above cell
Next j
Next i
End Sub

Wow, I am realy the VBAXLMan

Thursday 1/18/2007 8:52:04 AM
Mon
Jan 8
2007

Excel setting??

in Excel work sheet, how do i create a drop down button that contains options to choose from?


Answer

You have three ways
1- Is as the above answerer is suggestion (Although list in another sheet will work by naming that range using (Insert > Name > Define)), any way..
2- You can create a dorp-down combo by selecting the drop-down object from (View > Toolbars > Forms) (also Called Forms toolbar), then right click to go to its properties and assign cells to be the list and a cell to give you the item seleted.
3- You can do it in VBA of Excel, by showing the Control Toolbox toolbar, but this one need to controlled by the VBA in Excel not in Excel itself

Personally, I recommand you to use the second option, it is joyfull and almost programming

mail me here in Y! Answers any other details you want to know

Enjoy my profile, I am the VBAXLMan

Monday 1/8/2007 5:51:56 AM
Sun
Jan 7
2007

Excel formula to make input number its inverse?

I want to be able to input a number in a cell and automatically have it convert to its inverse (in that same cell). Is there a forumla for that?


Answer

No formula can do this.
Formulas are have to be entered in a cell first to work.
So You will need a VBA to do this
put this VBA macro in the code area of any sheet you want to do this:

Private Sub Worksheet_Change(ByVal Target As Range)
ActiveCell.Value = 0 - Val(ActiveCell.Value)
End Sub

need more info? mail me here in Y! Answers

Enjoy my profile, I am the VBAXLMan

Sunday 1/7/2007 9:07:50 PM
Sun
Dec 17
2006

Is there any formula to convert numbers to words in "Excel Program" other than Add-ins. Pls answer me.


Answer

Microsoft do have a function like this, it is a macro, so you may need to copy it first to your VBE module window

Check this out
http://www.ozgrid.com/VBA/CurrencyToWord…

Enjoy my profile, I am the VBAXLMan

Sunday 12/17/2006 9:43:26 PM
Sat
Dec 16
2006

Where can I find an excel spreadsheet of question and answers?

I am looking for question and answers for students for basic tests and exams for things like chemistry, geology, science. Just basic stuff like:
What is oxygen? An element that makes up our atmosphere.

I have many text files of info but they are taking for ever to manually copy/ paste into Excel (which is what my test creation program imports from).


Answer

I made somthing like this (Based on text files database) that will read these files and make the 10 questions with the 3 random ordered choices in VBA of Powerpoint, check this link
http://shiekhali.com/anmar/projects.html
and go down until the "Ayad Project" in 2002 and see the screenshots for it.

It is VBA in PowerPoint that will calculate the result of points, I can mail it to you and show you how to customize it if you are interesting, mail me here in Y! Answers


Enjoy my profile, I am the VBAXLMan

Saturday 12/16/2006 11:47:30 AM
Thu
Dec 14
2006

How do I get file attributes such as picture size type etc into an excel database using Visual Basic 6.0?

I'm making and index of my CD's in Excel and Everytime I get a new CD I need to right click and get the properties and copy them in excel. Thats why I want to make an application in VB which will do the job for me.


Answer

I would rather do the program in VBA of Excel, it is close to plain VB, besides you don't need a setup, just open the Excel file and run the macro (VBA subroutine)
Now the answer is using a special DLL to get the picture properties, there are alot of libraries sites out there in the net like:
vbaccelerator.com
a1vbcode.com
and much much more, i don't remeber all now.
If you got the declerations part, you can easily use it in the VBA of Excel

mail me for more info, i would be glad to help

Enjoy my profile, I am the VBAXLMan

Thursday 12/14/2006 4:45:18 PM
Tue
Dec 5
2006

How do you generate a PO number in an excel spreadsheet?

I have a purchase order form in excel that I want to automatically generate a purchase order number every time it is opened. Is this at all possible and if so how would I do it?


Answer

It is in macro (Did you heared of VBA?)
You will need to create the macro that will run at each start and adding new value to the old one.
It is something like this

Paste these lines in the ThisWorkbook object in your VBA window (Open excel, then press ALT+F11)

Private Sub Workbook_Open()
Starting
End Sub
Sub Starting()
Sheet1.Range("B1").Value = VAL(Sheet1.Range("B1").Value) +1
End Sub

Assuming that the cell B1 in sheet "Sheet1 is the cell with PO number

mail me for more info

Enjoy my profile, I am the VBAXLMan

Tuesday 12/5/2006 1:56:20 PM
Sun
Dec 3
2006

How to find the largest non-empty row of an Excel column with VBA code?


Answer

I guess you mean the largest value in a range of cells, right?

If so, then you can do one of the following:
1- Use another cell with the function of
=Max( B:B)
then call that cell using the object [Range]
MaximumValue = Range("A1").Value

2- Use the powerfull feature WorksheetFunction that will enables you to use some of Excel built-in functions in VBA, example:
MaximumValue = WorksheetFunction.Max( "Sheet1$B:$B")

Or...
You may want to find which row have the largest no of non-empty cells.
If so, then you will need som fomrula combination, I will need you to mail me here if you are looking for that in particular.
I also will need you to answer some questions like (Do these rows are one after one? means they don't have empty rows in between?)



mail me here for any further info

Enjoy my profile, I am the VBAXLMan

Sunday 12/3/2006 3:19:22 AM
Sat
Apr 22
2006

How do i work whit files txt files in visual basic , i need some functions?


Answer

I still use VB6 and open it as sequential file using

-Open [filename] for [open_method] as [file_no]
-line input #[file_no],[variable]
-print #[file_no],[variable]
-close #[file_no]

see MSDN for all these functions

Saturday 4/22/2006 3:21:09 AM

Tags

History