Running a macro in VB 1

[miller1975]

I had posted a thread yesterday to import a .txt file to excel and then move it to a new location. But for some reason the code was not working. So I made a macro in excel to import the txt file.
But I still cant automatic it in Vb. Can anyone please help me figure this out.
I need to import the txt to excel and then move the saved .xls file to a new folder.
My sample values are
target - c:\excel\data.txt
destination - c:\excel\import\data.xls

Sub export()
'
' export Macro

    Workbooks.OpenText FileName:="C:\excel\data.txt", Origin:=xlWindows, _
        StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
        ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, Comma:=True _
        , Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1), _
        Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1))
    ChDir "C:\excel\export"
    ActiveWorkbook.SaveAs FileName:="C:\excel\export\data.xls", FileFormat:= _
        xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
        , CreateBackup:=False
End Sub

 

[SkipVought]

Hi,

I take it you're NOT running this procedure from Excel.

did you set a reference to the Microsoft Excel n.m Object Library?

Then check out GetOBject in VB help to set an application and workbook object.

Skip,

_{[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken!}

 

[miller1975]

Hello Skip,
I am running this from VB. And the initial code I was using is

Sub OpenXL()

' Declare variable to hold the reference.
Dim xlObj As Object
   
'Create the object
Set xlObj = CreateObject("excel.application")

'make the object visible
xlObj.Visible = True

''This will bring the file into Excel in Tab delimited format
''Note, if you are dumping in CSV, this opens directly in Excel, also.  However,
''not all text files can be easily converted to Excel.  Those with missing columns, i.e.,
''fields, may not convert well.

    Dim FileName As String, NewFile As String
    
''Change filenames as needed

    FileName = YourFileName
    NewFile = YourNewFileName ''Different directory or different name
    
    'Open the text file
    xlObj.Workbooks.Open (FileName)
    
    ''Save as an Excel Workbook
    xlObj.ActiveWorkbook.SaveAs FileName & ".xls"
        
        
    'move file to new location
     Sub MoveAFile(FileName)
        Dim fso
        Set fso = CreateObject("Scripting.FileSystemObject")
        fso.MoveFile FileName, "c:\windows\desktop\"
     End Sub

    ' When you finish, use the Quit method to close
    xlObj.Quit
    
    'Release the object reference
    Set xlObj = Nothing

End Sub

 

[SkipVought]

Where does your Export procedure fit in?

In THAT procedure, you need to reference the Excel Application Object as well...

...
[b]xlObj[/b].Workbooks.OpenText...
...

Skip,

_{[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken!}

 

[fredericofonseca]

my version (tested).

' Declare variable to hold the reference.
Dim xlObj As Object

'Create the object
Set xlObj = CreateObject("excel.application")

'make the object visible
xlObj.Visible = True

''This will bring the file into Excel in Tab delimited format
''Note, if you are dumping in CSV, this opens directly in Excel, also.  However,
''not all text files can be easily converted to Excel.  Those with missing columns, i.e.,
''fields, may not convert well.

    Dim FileName As String
    Dim NewFile As String
    Dim origdir As String
    Dim destdir As String
    Dim file As String
    Dim oldextension As String
    Dim newextension As String
    
    oldextension = ".txt"
    newextension = ".xls"
    destdir = "C:\temp\demo\"
    origdir = "C:\temp\demo\"
    file = "f1"
    FileName = origdir & file & oldextension
    NewFile = destdir & file & newextension
    
    
    'Open the text file
    xlObj.Workbooks.Opentext FileName:=FileName, StartRow:=1, _
         DataType:=1, Tab:=True
    ''Save as an Excel Workbook
    xlObj.activeworkbook.SaveAs NewFile
    xlObj.activeworkbook.saved = True
        
    ' When you finish, use the Quit method to close
    xlObj.Quit
    
    'Release the object reference
    Set xlObj = Nothing

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[miller1975]

Hello Frederico,
Will this work with comma delimited files.
And what does file = "f1" do in the code..just curious.

This is Ramesh. I will be sending project details today. Please check it.

 

[miller1975]

For some reason the work doesnt do anything. The form just comes up but doesnt do a thing...
Am I missing any steps besides placing the code and adding the Excel reference.