1st VBA example in VBA and Macros

[dpeer]

Haven't gotten a response from Mr.Excel about this so...the first code example in the new book from Mr. Excel doesn't produce the error that it is said it will. This is the one that uses the sample data "invoice.txt, invoice2.txt" and more. You're supposed to record a macro using absolute reference to import these text files to Excel. The first file is "import.txt", so you start the recorder and navigate to that file and bring it into the spreadsheet with this line: Workbooks.OpenText Filename:="C:\somepath\invoice.txt".

So then you're to import another file, "invoice2.txt" and run the macro, which produces poor results. It positions the Total row in the middle of this file (roughly) because "invoice2.txt" has several more rows than "invoice.txt", this because you have used absolute reference. The thing is, of course, when you run the macro it doesn't bring the 2d file because the first line calls for the first file again, "invoice.txt". So how would one write the code to be flexible in calling for different files?

 

[dpeer]

first macro example in new book

So I'll try again.

In the new book, VBA and Macros Microsoft Excel 2013, the first example on page 22, "Case Study, Preparing to Record the Macro" is recorded in Excel using absolute references. For those who don't have the book, it goes like this:

1. Click the File menu and select Open
2. Navigate to the folder where Invoice.txt is stored
3. Select "All Files (*.*) from the Files of Type drop down list
4. Select Invoice.txt
5. Click Open
6. In the Text Import Wizard--step one of three--select Delimited from the Original Data Type section
7. Click Next
8. In the Text Import Wizard--step 2 of 3, select General in the Column Data Format section and change it to Date
9. Click Next

And so on for another several lines of recording. The idea to import the text file "Invoice.txt" and add a total line, do some summing of columns, format and autowidth, etc. Then, in the scenario of the lesson, the next day you get sent another invoice text file, "Invoice2.txt". You run the macro to import this into Excel, but this new file has more rows of invoices, and when you see the result, the Total row is in the wrong place. This is supposed to show the problem that can occur when using absolute reference in recording your macro. Problem is, when you run the code (macro), it imports the same file named in the operation in lines one through four above. The example doesn't work the way it's represented to work. Does anyone know how to write code that would work properly in this situation? My last attempt to post this issue was apparently taken down by a moderator. Let's see it this one hits the forum.


Edit: Threads being posted in Excel Questions forum, moved to MrExcel's Products: Books, CDs, Podcasts forum and combined both threads into one.

 

[esato]

Macros and Codes for VBA and Macro 2004 book

I bought the Book VBA and Macros for Microsoft Excel (2004 by Sans Publishing) in Brazil and tried to download code and excercises files from removed by Admin. This link returned a 404 error.
Are there any other link to get these files?
Thank you!

 

[starl]

Please see: http://www.mrexcel.com/forum/powerp...-pivot-table-data-crunching-excel-2013-a.html

 

[gimilzor]

Re: Macros and Codes for VBA and Macro 2004 book

I do not understand. the book references this site for the data files, pivotbookdata2013 //edited by Admin//
However , when i click on this link there are no datafiles.
how do i access the data files.
I pulled up my book in Amazon Cloud so i could click on the link directly. again no files. this is frustrating.