Extract data to excel and move to new location 1

[miller1975]

Hello all, I am trying to extract data to a excel spreadsheet and move the file to a new location.
I am getting data in text format from a query and the text file is getting saved in a folder. I am attempting to pick the text file from that location import it to excel and them move the excel to a new location on a different drive.

Can this be done. Please advice.
Thanks in advance.

 

[RonRepp]

Miller:

What "query" are you using? i.e., SQL Server, Access...

Next, what type of procedure are you using to extract data from the database? (ADO, DAO, RDO...)

Lastly, if you want the data in both Excel and Text formats, why not just use Excel for both? You can save Excel in text format and load text to Excel, and querying from an Access or SQL Server DB is easy to do through Excel's QueryTable function.

Let me see what you have thus far, and maybe I, or somebody else, can help.

Thanks,

Ron

 

[miller1975]

Hello Ron,
I am running a query from Mainframe to dump this data to a text file. DB2 doesnt allow data dump in excel as far as i know..maybe it does..
This part is not the problem. The data dump is working good.
Using the text file from the dump and taking it to excel and then moving the file to a new location is the issue.
Let me know if you need any further information.
Thanks.

 

[RonRepp]

Miller:

Try this:

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 = "C:\Documents and Settings\RonaldR\My Documents\Projects\PAT\BIONUTRIENTS.txt"
NewFile = "C:\Documents and Settings\RonaldR\My Documents\Projects\PAT\BIONUTRIENTS2.txt"

'Open the text file
xlObj.Workbooks.Open (FileName)

''Save as an Excel Workbook
xlObj.ActiveWorkbook.SaveAs "C:\Documents and Settings\RonaldR\My Documents\Projects\PAT\Reports\BIONUTRIENTS.xls"


'copy the text file to a different location/name
FileCopy FileName, NewFile

' When you finish, use the Quit method to close
xlObj.Quit

'Release the object reference
Set xlObj = Nothing

End Sub


I hope this helps.

Ron

 

[RonRepp]

Miller:

Sorry, in the example I provided, I used the CopyFile method instead of the MoveFile method. Here is the MoveFile (straight from VB6 Help)

Sub MoveAFile(FileName)
Dim fso
Set fso = CreateObject("Scripting.FileSystemObject")
fso.MoveFile FileName, "c:\windows\desktop\"
End Sub

 

[miller1975]

Thanks Ron, Let me test this one and I will keep u posted.

 

[miller1975]

Ron,
From your code I made this one.
Few things i didnt get is why is the newfile tag required
NewFile = "C:\Documents and Settings\RonaldR\My Documents\Projects\PAT\BIONUTRIENTS2.txt"
I want to keep the file name same.
Also my data will be comma delimited.
Please let me know what changes are required here.
Thanks in advance.

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 = "C:\Documents and Settings\RonaldR\My Documents\Projects\PAT\BIONUTRIENTS.txt"
    NewFile = "C:\Documents and Settings\RonaldR\My Documents\Projects\PAT\BIONUTRIENTS2.txt"
    
    'Open the text file
    xlObj.Workbooks.Open (FileName)
    
    ''Save as an Excel Workbook
    xlObj.ActiveWorkbook.SaveAs "C:\Documents and Settings\RonaldR\My Documents\Projects\PAT\Reports\BIONUTRIENTS.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

 

[RonRepp]

Miller:

Just replace my filenames with your filenames. The 2nd filename can be the same, but the directory would be different.

Like this:

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

You can SaveAs any file that you want in Excel.

I hope this helps,

Ron

 

[miller1975]

Just curious..why is the second file used for...

 

[miller1975]

For some reason it not create the excel document. I have my txt file in C:\excel and I have a new folder called "export" in the same directory to move the file and make it an excel. But its not coming up. And my text file is comma delimited not tab delimited. Is that the problem.

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 = "C:\excel\data.txt"
'Different directory or different name
   NewFile = "C:\excel\export\data.txt"
    
    '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:\excel\export\"
     End Sub

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

End Sub