Results 1 to 8 of 8
  1. #1
    New Lounger
    Join Date
    Feb 2009
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have a fairly large "master" spreadsheet and I am trying to create a *.csv file (as a separate file) that captures data from one column of the "Master" spreadsheet. Each row in the *.csv file will be a concatenation of some constants and some variable (all separated by commas of course), but underlying the arrangement in the master spreadsheet are two indices I will refer to as "A" and "B". There are 6 "A" values (1 thru 6) and within each of these there are 53 "B" values (1 thru 53) for a total of 318 combinations. I was hoping to loop using two nested "for next" loops for each of these nested array indices, filling the array with data from the master worksheet as it progressed. Next, I would activate the new *.csv spreadsheet & increment to each row, creating each concatenated row text value from corresponding array and constant data items(row qty = "A" X "B"). My question is how do I set up a two dimensional array or would I be better off just switching the focus back & forth between the two workbooks as I constructed each row's text (kind of like using the master spreadsheet's rows and columns as my data container rather than loading an array)?

  2. #2
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post
    [quote name='JHWilkinson' post='797552' date='12-Oct-2009 14:03']I have a fairly large "master" spreadsheet and I am trying to create a *.csv file (as a separate file) that captures data from one column of the "Master" spreadsheet. Each row in the *.csv file will be a concatenation of some constants and some variable (all separated by commas of course), but underlying the arrangement in the master spreadsheet are two indices I will refer to as "A" and "B". There are 6 "A" values (1 thru 6) and within each of these there are 53 "B" values (1 thru 53) for a total of 318 combinations. I was hoping to loop using two nested "for next" loops for each of these nested array indices, filling the array with data from the master worksheet as it progressed. Next, I would activate the new *.csv spreadsheet & increment to each row, creating each concatenated row text value from corresponding array and constant data items(row qty = "A" X "B"). My question is how do I set up a two dimensional array or would I be better off just switching the focus back & forth between the two workbooks as I constructed each row's text (kind of like using the master spreadsheet's rows and columns as my data container rather than loading an array)?[/quote]

    What I believe you have described is a worksheet with a 6 cell by 53 cell table. Why not save that sheet as a csv file?
    Regards
    Don

  3. #3
    New Lounger
    Join Date
    Feb 2009
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='wdwells' post='797562' date='12-Oct-2009 19:30']What I believe you have described is a worksheet with a 6 cell by 53 cell table. Why not save that sheet as a csv file?[/quote]

    Don,

    Thanks for the input. Actually it's a little more involved than that. The structure of the input to the problem is header info (constants on first couple of rows, various columns) and then two columns of index data followed bay a column of descriptor data selected by the user with a resulting format something like:

    A1 B1 Description1
    A1 B2 Description2
    ...
    A1 B53 Description 53
    A2 B1 Description 54
    A2 B2 Description 55
    ...
    A2 B53 Description 106
    A3 B1 Description 107
    etc...

    And the csv output must look like this (it is an import file to a program I have no control over, so no flexibility there):

    (Constant 1), (Constant 2), (Constant 3), (A1 value), (B1 Value), (Description1), (Constant 4), (Constant 5)
    (Constant 1), (Constant 2), (Constant 3), (A1 value), (B2 Value), (Description2), (Constant 4), (Constant 5)
    ...
    (Constant 1), (Constant 2), (Constant 3), (A1 value), (B53 Value), (Description53), (Constant 4), (Constant 5)
    (Constant 1), (Constant 2), (Constant 3), (A2 value), (B1 Value), (Description54), (Constant 4), (Constant 5)
    (Constant 1), (Constant 2), (Constant 3), (A2 value), (B2 Value), (Description55), (Constant 4), (Constant 5)
    ...
    (Constant 1), (Constant 2), (Constant 3), (A2 value), (B53 Value), (Description106), (Constant 4), (Constant 5)
    (Constant 1), (Constant 2), (Constant 3), (A3 value), (B1 Value), (Description107), (Constant 4), (Constant 5)
    etc...

    But possibly I could just copy / paste vertical columns of each variable data type to form this structure (I will have to do this in segments as there are interruptions (unwanted rows) in the data every 53 rows. I could then just transfer the constant data into the appropriate columns and fill to the bottom of the active row range. Not the direction I first started in, but I'll give it a try.

  4. #4
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post
    [quote name='JHWilkinson' post='797617' date='12-Oct-2009 17:12']But possibly I could just copy / paste vertical columns of each variable data type to form this structure (I will have to do this in segments as there are interruptions (unwanted rows) in the data every 53 rows. I could then just transfer the constant data into the appropriate columns and fill to the bottom of the active row range. Not the direction I first started in, but I'll give it a try.[/quote]

    I would be inclined to add a new sheet that returns the table in the order you want for the csv file. This need only be done once and will be ready to be saved at any time.

    Can you post a copy of the file with sensitive data removed?
    Regards
    Don

  5. #5
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post
    [quote name='JHWilkinson' post='797617' date='12-Oct-2009 17:12'][/quote]
    The attached file provides a working example of what I believe you need. It contains the following code:
    Code:
    Option Explicit
       
       Public Sub Create_CSV()
       
       'Set the following constants to the required string
       Const C1 = "Constant 1"
       Const C2 = "Constant 2"
       Const C3 = "Constant 3"
       Const C4 = "Constant 4"
       Const C5 = "Constant 5"
       
       'Set the following constants to the appropriate path; filename; and sheetname
       Const Pathspec = "C:\Trash\"	'Path for the csv file
       Const Filespec = "MyFile.csv"   'csv filename
       Const Sheetspec = "Sheet2"	  'Name of the sourrce sheet
       
       Dim CtrA As Long	' The A index
       Dim CtrB As Long	' The B index
       
    	   With Sheets(Sheetspec)
    		   Open Pathspec & Filespec For Output As #1
    			   For CtrA = 1 To 6
    				   For CtrB = 1 To 53
    					   Write #1, "(" & C1 & ")", "(" & C2 & ")", "(" & C3 & ")", _
    						   "(" & .Range("A" & CtrA) & ")", _
    						   "(" & .Range("B" & CtrB) & ")", _
    						   "(" & .Range("C" & CtrB) & ")", _
    						   "(" & C4 & ")", "(" & C5 & ")"
    				   Next CtrB
    			   Next CtrA
    		   Close #1
    	   End With
       End Sub
    H.T.H.
    Attached Files Attached Files
    Regards
    Don

  6. #6
    New Lounger
    Join Date
    Feb 2009
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Mr Wells,

    Just got home to see your post. Thank you so much; that is incredibly efficient code. You have shown me some new techniques that I was totally unaware of & need to digest. My input code is a little different than your example, but I believe I can adjust to accommodate.

    Here is one last twist for you; A couple of the constants (Constant 4 & 5) are really not constants, but must be selected from a list on the "master spreadsheet" and selecting the correct one (interpretation) is actually visual (hard to explain). have you ever seen a VBA/macro technique that halts the code execution and allows the user to navigate the "master" worksheet (in this case), select a cell containing the correct input value, and then continue code execution; using the value of the cell selected?

  7. #7
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post
    [quote name='JHWilkinson' post='797643' date='12-Oct-2009 21:49']Mr Wells,

    Just got home to see your post. Thank you so much; that is incredibly efficient code. You have shown me some new techniques that I was totally unaware of & need to digest. My input code is a little different than your example, but I believe I can adjust to accommodate.

    Here is one last twist for you; A couple of the constants (Constant 4 & 5) are really not constants, but must be selected from a list on the "master spreadsheet" and selecting the correct one (interpretation) is actually visual (hard to explain). have you ever seen a VBA/macro technique that halts the code execution and allows the user to navigate the "master" worksheet (in this case), select a cell containing the correct input value, and then continue code execution; using the value of the cell selected?[/quote]

    Apologies; the file which I provided lost the form and its code. I will re-post with an updated version this evening. Sorry, but I must go now.
    Regards
    Don

  8. #8
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post
    [quote name='JHWilkinson' post='797643' date='12-Oct-2009 21:49']Mr Wells,

    Just got home to see your post. Thank you so much; that is incredibly efficient code. You have shown me some new techniques that I was totally unaware of & need to digest. My input code is a little different than your example, but I believe I can adjust to accommodate.

    Here is one last twist for you; A couple of the constants (Constant 4 & 5) are really not constants, but must be selected from a list on the "master spreadsheet" and selecting the correct one (interpretation) is actually visual (hard to explain). have you ever seen a VBA/macro technique that halts the code execution and allows the user to navigate the "master" worksheet (in this case), select a cell containing the correct input value, and then continue code execution; using the value of the cell selected?[/quote]
    The attached has four named ranges; two with the lists of constants, and two with the selection results. I have also added a form with some code.

    I recommend stepping through the code to gain an understanding.

    Note a similar post was deleted as the file did not contain the form.
    Attached Files Attached Files
    Regards
    Don

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •