Name Regions Based On Headers

[NYFashionToGo]

Hello, Thanks for looking at this post. I managed to scrape together then change & alter, and then change much and alter even more and then execute some code. I got it to work. There is one thing I am stuck with and could use some assitance.

This code is specifically for Naming regions in Excel sheet. In an attempt to Parse csv files and Txt and write code that will Float. Be more flexible later on. I have a problem with the source of my file downloads constantly changing position of the fields and it is giving me issues. So I wanted to programicly name each region based on sheet name and header name. This way when i write code later I can call that region even if the position changed... There may be other ways to achieve this. This is only way I could think of to execute this.

Where I could use help is with the 2000 Rows it selects down the column. I would like to change that so that floats with the data as well. If it ends on row 58. It stops there. If it ends on row 3023 it stops there.... Can anyone help? Hopefully you understand what I mean.

Thanks

Here is the Code:

Sub CreateNames()

Rows("1:1").Select
Selection.Replace What:=" ", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False


Dim strRangeName As String, strWorksheetName As String
Dim intCounter As Integer, strRangeFormula As String
Dim strcolumn As String, strR1C1Formula As String
Dim strA1Formula As String

For intCounter = 1 To 50

strWorksheetName = ActiveSheet.Name

strR1C1Formula = "R1C" & Format(intCounter + 0, "#0")
strA1Formula = Application.ConvertFormula(Formula:=strR1C1Formula, FromReferenceStyle:=R1C1, ToReferenceStyle:=xlA1)
strRangeName = strWorksheetName & Format(Range(strA1Formula), "@")
strcolumn = "C" & Format(intCounter + 0, "#0")
strRangeFormula = "=" & strWorksheetName & "!R2" & strcolumn & ":R2000" & strcolumn

ActiveWorkbook.Names.Add Name:=strRangeName, RefersToR1C1:=strRangeFormula

Next intCounter

 

[NYFashionToGo]

forgot to put the

End Sub

 

[SkipVought]

Rather than naming all these rows, why not use Data/Get External Data/Import Data...

This inserts a QueryTable for each import AND the import range is NAMED in Data Range Parameters.

If your data source changes (I deal with this regularly), you simply macro record EDITING the import and chaning nothing continue to the end. Then modify the generated code, for the Connnection string, to be able to substitute a different filename and path at run time using the GetOpenFilename Method.

Ideally, each sheet contains ONE import query table.

Skip,

_[red][/red]

 

[NYFashionToGo]

Hi Skip thanks for the quick reply..... yes I would like to use the data>>Import external data as you had suggested that actually comes before this step in my sequence.... When you say the Import Range is Named. That is meaning the whole document not really each field named. Correct?

My biggest obstacle at this time. Is not being as experienced in writing code and altering things that I previously do. It seems to be a major process for me.....

I am trying very hard to grasp all this meaning coding and mdeveloping a program for myself.....

I need to do alot of manipulation to the data imported it is not entirely accurate. For alot of different aspects. Including shipping, Invoicing and marking as shipped in my database and creating additional fields....

I wish I was experienced in reading everything fluently as you are.... but I am not..... Im still in the infancy of my language..... Just looking for a way out of a problem. I just finished writing some code and I have to redo it now in march due to changes made... Grrrrrrrr.....

i really would like to know more about the queries... I understand that in the array (1) means import and (9) means skip that column all together.. Is there a way to (with the query) to import say the first three columns then....... actually skip the next 5 lines then pick up again with the same query.... on my finished sheet.... ( i tried substituting the array number with something different. Nothing taked on every value i put inside.....

Just curious.......

thanks for your post back.........

 

[SkipVought]

"When you say the Import Range is Named. That is meaning the whole document not really each field named. Correct?"

Whtever you import into the sheet, that range is given a name or you can assign one.

I could not tell you the array parameter values foe each type. You could macro record selecting each type and then you would know.

"I have a problem with the source of my file downloads constantly changing position of the fields and it is giving me issues."

If you were to do a DATABASE query rather than an IMPORT, the POSITION of the fields in the source data is immaterial. In fact, if you wanted ALL the fields, regardless of how many and what names, the CommandText could probably be as simple as...

Select * From [Sheet1$]

Database queries can be added to a sheet using Data/Get Exgternal Data/New Database Query...



Skip,

_[red][/red]