Writing the contents of cells into a file

[MyHero]

Hey Folks,

I have a bunch of values in column A, and I need to write (export) the entire column into a file called "ColumnA.dat".

Can someone please help me create an Excel Macro Script that would accomplish that?

Thanks in advance.

 

[matthew230]

Try:

http://social.msdn.microsoft.com/Forums/en-US/isvvba/thread/9d292032-712d-49fa-8e83-6a2bd031477e/

I imagine you want to use a temporary workbook to achieve this.

 

[Oorang]

Option Explicit
Public Enum eWriteMode
    eNormal = 0
    eAppend = 1
    eOverwrite = 2
End Enum
Public Sub example()
    WriteRange Sheet1.Columns(1), "C:\test\ColumnA.dat", eOverwrite
End Sub
Public Sub WriteRange(ByVal inputRange As Excel.Range, ByVal outputPath As String, _
    Optional writeMode As eWriteMode = eWriteMode.eNormal)
    Dim lngFileNum As Long
    Dim rngCll As Excel.Range
    Dim blnFileExists As Boolean
    blnFileExists = LenB(Dir(outputPath))
    If blnFileExists Then
        If writeMode = eNormal Then
            Err.Raise vbObjectError, Description:="A file already exists in the location specified."
        ElseIf writeMode = eOverwrite Then
            Kill outputPath
        End If
    End If
    Set inputRange = Excel.Intersect(inputRange, inputRange.Parent.UsedRange) 'Make sure you notice this:)
    lngFileNum = FreeFile
    Open outputPath For Append Access Write Lock Read Write As lngFileNum
    For Each rngCll In inputRange.Cells
        Write #lngFileNum, rngCll.Value
    Next
    Close lngFileNum
End Sub

 

[matthew230]

I was suggesting that perhaps code for this procedure was overkill for what is a simple manual process.

 

[MyHero]

Thank you all very much for your contributions.

Oorang, when I run your code I get the error "Compile Error: User-defined type not defined"

It then highlights:

Public Sub WriteRange(ByVal inputRange As Excel.Range, ByVal outputPath As String, _
Optional writeMode As eWriteMode =

Any ideas on how to improve your code?

 

[MyHero]

If anyone else can help, that would be great too.

 

[ddadmin2009]

Simply save the excel file as .dat or .txt file. I think that will solve your problem.

 

[ddadmin2009]

The code works fine for me.. did you copy the code properly...

 

[MyHero]

Simply save the excel file as .dat or .txt file. I think that will solve your problem.

Unfortunately, when I do that it puts every line in quotes, which the .dat file I need doesn't support.

Thanks though. Any other ideas?

 

[MyHero]

The code works fine for me.. did you copy the code properly...

Yes, unfortunately I still get the:

"Compile Error: User-defined type not defined" error