Exporting sequential files from excel using VBA

I have columns of data in excel and need to

Page 1 of 1

1 Replies - 4466 Views - Last Post: 10 February 2008 - 08:01 AM Rate Topic: -----

#1 maille  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 1
  • Joined: 07-February 08

Exporting sequential files from excel using VBA

Posted 07 February 2008 - 05:17 AM

I have been using visual basic to integrate Autodesk Civil 3D and a number of civil engineering packages, mainly pipe design. I can get my data into excel from most of the format that i use, however one of the programs does not use .csv/.mdb type storage, it is sequential as shown below. I was wondering if I could use VBA to select data row by row and transpose into a single column and export. I may need to this with up to 3000 rows, thus with 102 columns I can still fit the data into a single column. Mostly i have been using VBA in Civil 3D so my idea was to use a loop and increment the row value on paste however i can't seem to do this and i'm not sure how to increment cell refs in excel, also having trouble with the export. nothing has worked really so far, all th code i have really is;
Range.Address(1, 2, xlR1C1, e1, a1) = 1
Range(RC[-1]:R[101]C[0]).Select
Selection.Copy

Range("E3").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True

any guidance would be helpful



Pipe 1.015
1.5
1.015
20
250.000005587936
0
300
7.99999982118607E-02
0
0
0
0.875240445137024
61.8671037929106
7.875
0
0
0
0
1.71599984169006
0
0
300
5.69999980926514
0
0
1.6359998434782
5.59999990463257
F40
F41
3.68399995565415
3.66400006115437
1200
1200
0
0
0
144
0.60185386847825
72.3242530622367
0
0
3000
604.200000762939
100
624.200000762939
100
1
0
3
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
1.20000004768372
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0.433097127210991
Pipe 1.016
1.5
1.016
26
249.999994841906
0
300
0.104000002145767
0
0
0
0.875240445137024
61.8671037929106
7.875
0
0
0
0
1.63599979877472
0
0
300
5.59999990463257
0
0
1.53199979662895
5.6
F41
F42
3.66400009393692
3.76800020337105
1200
0
0
0
0
144
0.60185386847825
72.3242530622367
0
0
3000
624.200000762939
100
650.200000762939
100
1
0
3
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
1.20000004768372
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0.433097127210991

Is This A Good Question/Topic? 0
  • +

Replies To: Exporting sequential files from excel using VBA

#2 ahmad_511  Icon User is offline

  • MSX
  • member icon

Reputation: 131
  • View blog
  • Posts: 722
  • Joined: 28-April 07

Re: Exporting sequential files from excel using VBA

Posted 10 February 2008 - 08:01 AM

Hello and welcome…
I know this will be too slow but this is first thing I got in my mind
1- get the used range of the sheet, so that help to prevent entering the start and end cells addresses.
2- loop for each row in that range
3- inside rows loop, we need to loop for each column in row
4- read cell values
5- save it to an external file

Set ActiveSheet = ActiveSheet.UsedRange
RC = ActiveSheet.Rows.Count
CC = ActiveSheet.Columns.Count
Open ".\test.txt" For Output As #1
For r = 1 To RC
For c = 1 To CC
DoEvents
Print #1, ActiveSheet.Cells(r, c).Value
Next
Next



And you can download the attached file for more enhancements
Attached File  test.zip (26.33K)
Number of downloads: 146
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1