how to open an excel template (xlt)?
in [Visual Studio]
|
Prev: ComboBox
Next: Need for Speed
From: Gregory on 19 May 2008 09:58 Hi, I'm using visual basic 6 , i would like to write code that do the following : 1 - open an excel template (.xlt ) with text data to import 2 - specify the path to the .txt file Actually, I have just found examples that: - open a new or existing .xls - modify cells using 'microsoft excel object library' Where can i find the complete 'microsoft excel object library' documentation ? Thanks for any help..
From: Auric__ on 20 May 2008 13:08 On Mon, 19 May 2008 13:58:33 GMT, Gregory wrote: > Where can i find the complete 'microsoft excel object library' > documentation ? Have you tried searching MSDN? -- Hey, and a happy "Good morning" to you, too, loser.
From: Gregory on 21 May 2008 03:31 Hi, I haven't found on MSDN, but it seems the documentation of 'microsoft excel object library' can be found in Excel>Tool>Visuab Basic Editor>Help With different examples found on the web, i have written this code which doesn't make exactly what i wanted first -> it takes a .xsl as template and not a .xlt -> it makes a copy of the template and create a new workbooks when importing text data, then copy and paste the cells... Moreover it produces an error, because 2 visible excel processes are created ( the 2nd at line : xls.Workbooks.OpenTextFileName:="C:\data.txt",other:=True, otherchar:="#" ), and when i try : xls.ActiveSheet.Paste it returns that the Sheet is opened by another application... ---------------------------------------------------------------------------- --- Dim xls As Excel.Application Dim Wb As Excel.Workbook Set xls = New Excel.Application Set Wb = xls.Workbooks.Open("C:\model.xls") xls.Visible = True Wb.SaveAs ("C:\copy.xls") xls.Workbooks.OpenText FileName:="C:\data.txt", other:=True, otherchar:="#" xls.Workbooks("data.txt").Activate ActiveSheet.Range("a1", "i1500").Copy Wb.Activate Worksheets("Feuil1").Activate xls.ActiveSheet.Paste ActiveWorkbook.Save xls.Quit Set xls = Nothing ---------------------------------------------------------------------------- ---- "Auric__" <not.my.real(a)email.address> a �crit dans le message de news:g0v0hq$4rk$3(a)registered.motzarella.org... > On Mon, 19 May 2008 13:58:33 GMT, Gregory wrote: > > > Where can i find the complete 'microsoft excel object library' > > documentation ? > > Have you tried searching MSDN? > > -- > Hey, and a happy "Good morning" to you, too, loser.
From: Auric__ on 21 May 2008 13:39 On Wed, 21 May 2008 07:31:11 GMT, Gregory wrote: > Hi, > I haven't found on MSDN, but it seems the documentation of > 'microsoft excel object library' can be found in Excel>Tool>Visuab > Basic Editor>Help > > With different examples found on the web, i have written this code > which doesn't make exactly what i wanted first > -> it takes a .xsl as template and not a .xlt My (possibly incorrect) understanding of .xlt files is that they're just .xls files with a different extension. > -> it makes a copy of the template and create a new workbooks when > importing text data, then copy and paste the cells... IIRC that's the default behavior for templates. If you want to edit the template itself... uh... I dunno. (Not something I deal with.) > Moreover it produces an error, because 2 visible excel processes > are created ( the 2nd at line : > xls.Workbooks.OpenTextFileName:="C:\data.txt",other:=True, > otherchar:="#" ), and when i try : xls.ActiveSheet.Paste > it returns that the Sheet is opened by another application... Rather than opening the text file as a workbook, try (via code): 1. open the text file 2. read the data in 3. parse as needed 4. paste the parsed data into wherever it needs to go It will avoid having to switch workbooks in the middle of your code, with the added advantage that it should be faster. This works for me (warning: 2-minute hack): Sub DWIM() Dim tmp As String, L0 As Long, lineNum As Long ReDim dat(0) As String Open "C:\data.txt" For Input As 1 Do Until EOF(1) lineNum = lineNum + 1 Line Input #1, tmp dat = Split(tmp, "#") For L0 = 0 To UBound(dat) Cells(lineNum, L0 + 1).Formula = dat(L0) Next Loop End Sub BTW, for further Excel programming questions (and better answers) I suggest the group microsoft.public.excel.programming. They do this sort of thing all the time. -- I have no idea what *you* think *I* need. You need subtitles. Or some kind of instruction manual.
From: Gregory on 22 May 2008 03:57
thanks for your help. in fact, resolving the pb by reading and parsing the text with a loop like you do , was what i tried first. but with large files it takes a lot of time, whereas excel do it very fast. using the excel command 'record macro' i retrieved this code which almost works. the only bug is that after having exited the sub, there is still an invisible excel process runing ( despite xls.quit set xls = nothing ) this process prevent me from opening the created excel file, until i kill it. ( i think this point can be considered as a visual basic subject ) code: Dim xls As Excel.Application ' Objet : Processus excel Set xls = New Excel.Application xls.Visible = True Workbooks.Open FileName:= _ "C:\template.xls" Sheets("Feuil1").Select With ActiveSheet.QueryTables.Add(Connection:="TEXT; C:\data.txt" , Destination _ :=Range("A1")) .Name = "data.txt" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .TextFilePromptOnRefresh = False .TextFilePlatform = xlWindows .TextFileStartRow = 1 .TextFileParseType = xlDelimited .TextFileTextQualifier = xlTextQualifierDoubleQuote .TextFileConsecutiveDelimiter = False .TextFileTabDelimiter = False .TextFileSemicolonDelimiter = False .TextFileCommaDelimiter = False .TextFileSpaceDelimiter = False .TextFileOtherDelimiter = "#" .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1) .Refresh BackgroundQuery:=False End With ActiveWorkbook.SaveAs FileName:="C:\data.xls", FileFormat:=xlNormal, _ Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _ CreateBackup:=False ActiveWorkbook.Close xls.Quit Set xls = Nothing "Auric__" <not.my.real(a)email.address> a �crit dans le message de news:g11mnq$ros$1(a)registered.motzarella.org... > On Wed, 21 May 2008 07:31:11 GMT, Gregory wrote: > > > Hi, > > I haven't found on MSDN, but it seems the documentation of > > 'microsoft excel object library' can be found in Excel>Tool>Visuab > > Basic Editor>Help > > > > With different examples found on the web, i have written this code > > which doesn't make exactly what i wanted first > > -> it takes a .xsl as template and not a .xlt > > My (possibly incorrect) understanding of .xlt files is that they're > just .xls files with a different extension. > > > -> it makes a copy of the template and create a new workbooks when > > importing text data, then copy and paste the cells... > > IIRC that's the default behavior for templates. If you want to edit > the template itself... uh... I dunno. (Not something I deal with.) > > > Moreover it produces an error, because 2 visible excel processes > > are created ( the 2nd at line : > > xls.Workbooks.OpenTextFileName:="C:\data.txt",other:=True, > > otherchar:="#" ), and when i try : xls.ActiveSheet.Paste > > it returns that the Sheet is opened by another application... > > Rather than opening the text file as a workbook, try (via code): > 1. open the text file > 2. read the data in > 3. parse as needed > 4. paste the parsed data into wherever it needs to go > > It will avoid having to switch workbooks in the middle of your code, > with the added advantage that it should be faster. This works for me > (warning: 2-minute hack): > > Sub DWIM() > Dim tmp As String, L0 As Long, lineNum As Long > ReDim dat(0) As String > Open "C:\data.txt" For Input As 1 > Do Until EOF(1) > lineNum = lineNum + 1 > Line Input #1, tmp > dat = Split(tmp, "#") > For L0 = 0 To UBound(dat) > Cells(lineNum, L0 + 1).Formula = dat(L0) > Next > Loop > End Sub > > BTW, for further Excel programming questions (and better answers) I > suggest the group microsoft.public.excel.programming. They do this > sort of thing all the time. > > -- > I have no idea what *you* think *I* need. You need subtitles. > Or some kind of instruction manual. |