Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Excel - Trying to create *.txt file from worksheet

Status
Not open for further replies.
Nilsen

Nilsen

MIS
Dec 31, 1999
36
0
0
US
I'm trying to create a text file for upload into another application from “sheet1” in Excel 97. If I have any blank lines on the text file, I get an error on the upload in the other application. I have a formula that is combining the data from three columns on &quot;sheet1&quot; to one column on &quot;sheet2&quot;. The problem is the number of rows will be different each time I run the copy macro. What I'd like to put in the macro is something that copies the formula in cell A1 on &quot;sheet1&quot; to cells A2:A(x) where x is 1 minus the number of rows in &quot;sheet2&quot;.<br>
<br>
What I've tried is having my formula in rows 1 through 1000 on “sheet1”, had the macro set the cell in &quot;sheet1&quot; to Null if the corresponding cell in &quot;sheet2&quot; is Null. Then had the macro do a copy paste special formulas as values. But when I create the text file, all the rows that are empty are on text file as blank lines.<br>

 
Sort by date Sort by votes
Create a macro that copies by sitting in cell A1 and hit Shift-Ctrl-End. This should select A1 through the bottom/right-most character in the spreadsheet. If it goes beyond the bottom/right-most character you may need to Edit-Clear-All on the remaining columns and rows. Columns: select first completely blank column, hit Shift-Ctrl-Right arrow key, then Edit-Clear-All. Rows: select first completely blank row, hit Shift-Ctrl-Down arrow key, then Edit-Clear-All.<br>
<br>
If you're continually using the same Sheet1, this may not work and you'll have to consider using a macro that uses the xlDown/xlUp features. If you need to do that, there are people who will probably write the darn thing for you in &quot;the lounge&quot; at <A HREF=" TARGET="_new">
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

ladyck3
  • Locked
  • Question
Excel 365 - Working with Tables - Calculating data from one worksheet to another. 2
2
Replies
30
Views
423
combo
combo
shytott
  • Question
Excel - Toggle Button Code 1
Replies
10
Views
734
shytott
shytott
kwbMitel
  • Locked
  • Question
I can't add sheets to my Excel file because it's protected.
Replies
4
Views
467
StilLearningAlot
StilLearningAlot
Bijugate
  • Question
Excel update question
Replies
0
Views
235
Bijugate
Bijugate
cneill
  • Locked
  • Question
Hi, I am trying to get my head rou
2 3 4
Replies
64
Views
918
combo
combo

Part and Inventory Search

Sponsor

Back
Top