Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
A problem with Set (Range)
I want to read in 2 text-files which should be placed together side by side
from say C1 to F1740 so the first text file uses columns C and D and the second one uses E and F. I can then easily use 100 text files if I just get this sample right. The problem I have is when I want to use the Range(Cells (1, 3), Cells(1470,4) in the set line of the program it stops, while when I use C1:D1470 it works. I would like to use the Range(Cells) code so I can wander though the Excel sheet 100 times just by updating the column variable. I have had this problem before that when it comes to the paste line of the program Excel does not like it. Also when I have placed a clip on the clipboard Excel asks me if I want to use the clip for another program and I click no. How can I disable this function so I do not have to click no 100 times? This is my code Private Sub CommandButton2_Click() Dim TextTest As Variant For Each c In Range("A1:A2") Dim Path As String Dim filename As String Dim fileextension As String Path = "C:\Peter\Test\" fileextention = ".txt" filename = c.Value TextTest = Path & filename & fileextention Workbooks.OpenText filename:=TextTest, Origin:=xlWindows, DataType:=xlDelimited, Tab:=True Dim wb1 As Workbook Dim wb2 As Workbook Dim ws1 As Worksheet Dim rngformula3 As Range Dim rngpaste3 As Range Dim kolumn As Long kolumn = 3 Set wb1 = Workbooks("textinläsning.xls") Set wb2 = ActiveWorkbook Set rngformula3 = wb2.Worksheets(1).Range("A1:B1470") Set rngpaste3 = wb1.Sheets("Data").Range(Cells(1, kolumn), Cells(1470, kolumn + 1)) rngformula3.Copy rngpaste3.PasteSpecial (xlPasteValues) Set ws1 = Nothing Set wb1 = Nothing Set wb2 = Nothing Set rngformula3 = Nothing ActiveWorkbook.Close savechanges:=False kolumn = kolumn + 2 Next End Sub Thanks guys! /Peter |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
A problem with Set (Range)
Celss is not explicitly referenced so it will refer to the active sheet
(whatever that might be. Try something more like... with wb1.Sheets("Data") Set rngpaste3 = .Range(.Cells(1, kolumn), .Cells(1470, kolumn + 1)) end with -- HTH... Jim Thomlinson "Peter" wrote: I want to read in 2 text-files which should be placed together side by side from say C1 to F1740 so the first text file uses columns C and D and the second one uses E and F. I can then easily use 100 text files if I just get this sample right. The problem I have is when I want to use the Range(Cells (1, 3), Cells(1470,4) in the set line of the program it stops, while when I use C1:D1470 it works. I would like to use the Range(Cells) code so I can wander though the Excel sheet 100 times just by updating the column variable. I have had this problem before that when it comes to the paste line of the program Excel does not like it. Also when I have placed a clip on the clipboard Excel asks me if I want to use the clip for another program and I click no. How can I disable this function so I do not have to click no 100 times? This is my code Private Sub CommandButton2_Click() Dim TextTest As Variant For Each c In Range("A1:A2") Dim Path As String Dim filename As String Dim fileextension As String Path = "C:\Peter\Test\" fileextention = ".txt" filename = c.Value TextTest = Path & filename & fileextention Workbooks.OpenText filename:=TextTest, Origin:=xlWindows, DataType:=xlDelimited, Tab:=True Dim wb1 As Workbook Dim wb2 As Workbook Dim ws1 As Worksheet Dim rngformula3 As Range Dim rngpaste3 As Range Dim kolumn As Long kolumn = 3 Set wb1 = Workbooks("textinläsning.xls") Set wb2 = ActiveWorkbook Set rngformula3 = wb2.Worksheets(1).Range("A1:B1470") Set rngpaste3 = wb1.Sheets("Data").Range(Cells(1, kolumn), Cells(1470, kolumn + 1)) rngformula3.Copy rngpaste3.PasteSpecial (xlPasteValues) Set ws1 = Nothing Set wb1 = Nothing Set wb2 = Nothing Set rngformula3 = Nothing ActiveWorkbook.Close savechanges:=False kolumn = kolumn + 2 Next End Sub Thanks guys! /Peter |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Range problem | Excel Programming | |||
Range problem | Excel Programming | |||
Range problem | Excel Programming | |||
Used Range Problem | Excel Programming | |||
Used Range Problem | Excel Programming |