Free Microsoft Excel 2013 Quick Reference

Fileformat XLText

I have some VBA code that saves a worksheet as XLText in Excel 9 but it
doesn't work in Excel 10. It appears that XLText is not a valid file format.
Instead I have the option of XLTextMSDOS, XLTextWindows etc. Does anyone
know which one of these will give me the same file format as the plain XLText
did in version 9?


Hi All,

I'm using Excel 2003.

My goal: Execute macro to save numerous spreadsheets to .tsv file format
My Problem: getting a fileformat to save in tsv format.

I can save a spreadsheet as a tsv manually. When I record the macro I see:

ActiveWorkbook.SaveAs Filename:= _
"C:testing.tsv" _
, FileFormat:=xlText, CreateBackup:=False

When I run this in my macro I trap the error: Application-defined or object-defined error

Set newWks = ActiveSheet
With newWks
.SaveAs Filename:="C:TSV" & newWks.Name & ".tsv", FileFormat:=xltext,
End With

I can successfully create a csv using:

Set newWks = ActiveSheet
With newWks
.SaveAs Filename:="C:TSV" & newWks.Name & ".tsv", FileFormat:=6,
End With

What value of FileFormat will create a tsv? Or, is there another way?

Regards,

-Brian

Hi All,

I have a requirement where i need to save my Activesheet as .txt file format.
The problem am facing is that whenever i have some special character in cell (,$,@) it adds up " mark when saved as Tab Delimited File.

IU cannot use xlTextPrinter Format to save the file as after this step am using this Text file as the input to my UI and i use TAB as delimiter to separate the columns.

When i use xlTextPrinter to save the sheet as .txt file all my Tab spacing are mis matched.

Please suggest how can i work on this problem,


	VB:
	
 
Set shapes = ws.shapes("Button 1") 
shapes.Delete 
ws.Copy 
ws.Visible = False 
If Dir(savepath)  "" Then 
    Kill savepath 
    ActiveSheet.Range("A1").Select 
    ActiveWorkbook.SaveAs filename:= _ 
    savepath _ 
    , FileFormat:=xlText, CreateBackup:=False 
    ActiveWorkbook.Close 
Else 
    ActiveSheet.Range("A1").Select 
    ActiveWorkbook.SaveAs filename:= _ 
    savepath _ 
    , FileFormat:=xlText, CreateBackup:=False 
    ActiveWorkbook.Close 
End If 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

Regards
Ashesh

I am trying to have an excel spreadsheet write edited SQL commands (that I have stored in an excel spreadsheet) to a text file. The code I have below allows me to do that, but ends up putting quotes around everything that appeared in the excel spreadsheet:


	VB:
	
 
ActiveSheet.Select 
ActiveWorkbook.SaveAs Filename:= _ 
FilePath & "" & Name & ".txt", FileFormat:=xlText, CreateBackup:=False 
ActiveWorkbook.Close savechanges:=False 
Application.DisplayAlerts = True 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I was wondering if it was possible to write to a text file without having quotes around everything?

this is what it currently creates:

"SELECT 2009Label as DB_Source, tPI.TimeStep, (tPI.TimeStep + 1989) as SimYear, "
" Sum(tPI.VFastAG) AS VFast_AG, "
" Sum(tPI.VFastBG) AS VFast_BG, "
" Sum(tPI.FastAG) AS Fast_AG, "
" Sum(tPI.FastBG) AS Fast_BG, "
" Sum(tPI.Medium) AS Medium, "
" Sum(tPI.SlowAG) AS Slow_AG, "
" Sum(tPI.SlowBG) AS Slow_BG, "
" Sum(tPI.SWStemSnag) AS SW_Stem_Snag, "
" Sum(tPI.SWBranchSnag) AS SW_Branch_Snag, "
" Sum(tPI.HWStemSnag) AS HW_Stem_Snag, "
" Sum(tPI.HWBranchSnag) AS HW_Branch_Snag, "
" Sum(tPI.SW_Merch) AS SW_Merch, "
" Sum(tPI.SW_Foliage) AS SW_Foliage, "
" Sum(tPI.SW_Other) AS SW_Other, "
" Sum(tPI.SW_Coarse) AS SW_Coarse_Roots, "
" Sum(tPI.SW_Fine) AS SW_Fine_Roots, "
" Sum(tPI.HW_Merch) AS HW_Merch, "
" Sum(tPI.HW_Foliage) AS HW_Foliage, "
" Sum(tPI.HW_Other) AS HW_Other, "
" Sum(tPI.HW_Coarse) AS HW_Coarse_Roots, "
" Sum(tPI.HW_Fine) AS HW_Fine_Roots,"
" Sum(tPI.SW_Merch + tPI.SW_Foliage + tPI.SW_Other + tPI.HW_Merch + tPI.HW_Foliage + tPI.HW_Other) as Total_AG_Biomass,"
" Sum(tPI.SW_Coarse + tPI.SW_Fine + tPI.HW_Coarse + tPI.HW_Fine) as Total_BG_Biomass,"
" Sum(tPI.SWStemSnag + tPI.SWBranchSnag + tPI.HWStemSnag + tPI.HWBranchSnag + tPI.Medium + tPI.FastBG) as Total_Deadwood,"
" Sum(tPI.VFastAG + tPI.FastAG + tPI.SlowAG) as Total_Litter,"
" Sum(tPI.VFastBG + tPI.SlowBG) as Total_Soil_OM,"
" Sum(tPI.VFastAG + tPI.FastAG + tPI.Medium + tPI.SlowAG + tPI.VFastBG + tPI.FastBG + tPI.SlowBG) as TotalSoil_JM, "
Sum(tPI.SWStemSnag + tPI.SWBranchSnag + tPI.HWStemSnag + tPI.HWBranchSnag) as TotalSnag_JM
FROM tblPoolIndicators as tPI
IN R:NIR_2009DataAfforestationAfforestation_SIT_NIR.mdb
WHERE tPI.LandClassID = 0
GROUP BY tPI.TimeStep
UNION
"SELECT 2010Label as DB_Source, tPI.TimeStep, (tPI.TimeStep + 1989) as SimYear,"
" Sum(tPI.VFastAG) AS VFast_AG, "
" Sum(tPI.VFastBG) AS VFast_BG, "
" Sum(tPI.FastAG) AS Fast_AG, "
" Sum(tPI.FastBG) AS Fast_BG, "
" Sum(tPI.Medium) AS Medium, "
" Sum(tPI.SlowAG) AS Slow_AG, "
" Sum(tPI.SlowBG) AS Slow_BG, "
" Sum(tPI.SWStemSnag) AS SW_Stem_Snag, "
" Sum(tPI.SWBranchSnag) AS SW_Branch_Snag, "
" Sum(tPI.HWStemSnag) AS HW_Stem_Snag, "
" Sum(tPI.HWBranchSnag) AS HW_Branch_Snag, "
" Sum(tPI.SW_Merch) AS SW_Merch, "
" Sum(tPI.SW_Foliage) AS SW_Foliage, "
" Sum(tPI.SW_Other) AS SW_Other, "
" Sum(tPI.SW_Coarse) AS SW_Coarse_Roots, "
" Sum(tPI.SW_Fine) AS SW_Fine_Roots, "
" Sum(tPI.HW_Merch) AS HW_Merch, "
" Sum(tPI.HW_Foliage) AS HW_Foliage, "
" Sum(tPI.HW_Other) AS HW_Other, "
" Sum(tPI.HW_Coarse) AS HW_Coarse_Roots, "
" Sum(tPI.HW_Fine) AS HW_Fine_Roots,"
" Sum(tPI.SW_Merch + tPI.SW_Foliage + tPI.SW_Other + tPI.HW_Merch + tPI.HW_Foliage + tPI.HW_Other) as Total_AG_Biomass,"
" Sum(tPI.SW_Coarse + tPI.SW_Fine + tPI.HW_Coarse + tPI.HW_Fine) as Total_BG_Biomass,"
" Sum(tPI.SWStemSnag + tPI.SWBranchSnag + tPI.HWStemSnag + tPI.HWBranchSnag + tPI.Medium + tPI.FastBG) as Total_Deadwood,"
" Sum(tPI.VFastAG + tPI.FastAG + tPI.SlowAG) as Total_Litter,"
" Sum(tPI.VFastBG + tPI.SlowBG) as Total_Soil_OM,"
" Sum(tPI.VFastAG + tPI.FastAG + tPI.Medium + tPI.SlowAG + tPI.VFastBG + tPI.FastBG + tPI.SlowBG) as TotalSoil_JM, "
Sum(tPI.SWStemSnag + tPI.SWBranchSnag + tPI.HWStemSnag + tPI.HWBranchSnag) as TotalSnag_JM
FROM tblPoolIndicators as tPI
IN R:NIR_2009Datadb3.mdb
WHERE tPI.LandClassID = 0
GROUP BY tPI.TimeStep;

I have a worksheet that I need some assistance on. I have a worksheet to extract some information from an ebay & paypal file of those who have paid me for my auctions. The worksheet was created to spit out 6 different text files to be later imported into access. The excel worksheet runs the macro perfectly, except for the fact that it creates a lot of blank spaces in the text files. Which is not good for Access. As far as I know there is an auto filter which is set to filter out any blanks. But the text files show otherwise. I'm particularly concerned with the Orders.txt, Ordersummary.txt, and the Paypaldownload.txt

I will lay out the code here. for all 3 plus the master code which spits out the files into my save directory.

This is for the ordersummary


	VB:
	
 ImportingStep1OrdersSummary() 
     
     '
     ' ImportingStep2FESoldSort Macro
     '
     '
     
     '
     
     
    Sheets("OrderSummary").Select 
    Cells.Select 
    Selection.ClearContents 
    Sheets("OrderSummarySort").Select 
    Selection.AutoFilter 
    Range("A1:v5000").Select 
    Selection.AutoFilter 
    Selection.AutoFilter Field:=1, Criteria1:="" 
    Selection.Copy 
    Sheets("OrderSummary").Select 
    Range("A1").Select 
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ 
    :=False, Transpose:=False 
    Application.CutCopyMode = False 
    Selection.Copy 
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ 
    :=False, Transpose:=False 
    ActiveSheet.Paste 
    Application.CutCopyMode = False 
    Range("A1").Select 
End Sub 
Sub ImportingStep3OrdersSort() 
     '
     
     
     ' ImportingStep3OrdersSort Macro
     
     '
     
     '
    Sheets("Orders").Select 
    Cells.Select 
    Selection.ClearContents 
    Range("A1").Select 
    Sheets("OrdersSort").Select 
    Selection.AutoFilter 
    Range("A1:o1000").Select 
    Selection.AutoFilter 
    Selection.AutoFilter Field:=1, Criteria1:="" 
    Selection.Copy 
    Sheets("Orders").Select 
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ 
    :=False, Transpose:=False 
    Application.CutCopyMode = False 
    Selection.Copy 
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ 
    :=False, Transpose:=False 
    Range("A1").Select 
    Application.CutCopyMode = False 
    Range("A1").Select 
     
     
     
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


This is the code for Paypal


	VB:
	
 ImportingStep2PayPalSort() 
     '
     ' PayPalSort Macro
     
     '
     
     '
     
     
    Sheets("PayPalDownLoad").Visible = True 
    Sheets("PayPalUpload").Visible = True 
    Sheets("PayPalDownloadsort").Visible = True 
    Sheets("PayPalUpload").Select 
    Cells.Select 
    Selection.ClearContents 
    Range("A1").Select 
    With ActiveSheet.QueryTables.Add(Connection:= _ 
        "TEXT;C:Documents and SettingsRony SosaMy DocumentsIncansunEbayPayPal DownloadsDownload.txt" _ 
        , Destination:=Range("A1")) 
        .Name = "Download" 
        .FieldNames = True 
        .RowNumbers = False 
        .FillAdjacentFormulas = False 
        .PreserveFormatting = True 
        .RefreshOnFileOpen = False 
        .RefreshStyle = xlInsertDeleteCells 
        .SavePassword = False 
        .SaveData = True 
        .AdjustColumnWidth = True 
        .RefreshPeriod = 0 
        .TextFilePromptOnRefresh = False 
        .TextFilePlatform = 437 
        .TextFileStartRow = 1 
        .TextFileParseType = xlDelimited 
        .TextFileTextQualifier = xlTextQualifierDoubleQuote 
        .TextFileConsecutiveDelimiter = False 
        .TextFileTabDelimiter = True 
        .TextFileSemicolonDelimiter = False 
        .TextFileCommaDelimiter = False 
        .TextFileSpaceDelimiter = False 
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _ 
        1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1) 
        .TextFileTrailingMinusNumbers = True 
        .Refresh BackgroundQuery:=False 
         
    End With 
     
     
     
     
     
     
    Sheets("PayPalDownloadSort").Select 
    Columns("A:AS").Select 
    Selection.ClearContents 
    Sheets("PayPalUpload").Select 
    Columns("A:AS").Select 
    Selection.Copy 
    Sheets("PayPalDownloadSort").Select 
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ 
    :=False, Transpose:=False 
    ActiveSheet.Paste 
    Application.CutCopyMode = False 
     
     
    Columns("AV:BA").Select 
    Selection.Copy 
     
    Columns("AL:AQ").Select 
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ 
    :=False, Transpose:=False 
    Application.CutCopyMode = False 
    Selection.Copy 
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ 
    :=False, Transpose:=False 
    ActiveSheet.Paste 
    Application.CutCopyMode = False 
     
    Columns("BD:BD").Select 
    Selection.Copy 
    Columns("BF:BF").Select 
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ 
    :=False, Transpose:=False 
    Application.CutCopyMode = False 
    Selection.Copy 
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ 
    :=False, Transpose:=False 
    ActiveSheet.Paste 
    Application.CutCopyMode = False 
    Columns("BG:DF").Select 
    Selection.ClearContents 
    Columns("BF:BF").Select 
    Selection.TextToColumns Destination:=Range("BF1"), DataType:=xlDelimited, _ 
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _ 
    Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _ 
    :=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True 
     
    ActiveCell.FormulaR1C1 = "Buyer First Name" 
    Columns("BF:BF").Select 
    Range("BF2").Activate 
     
     
     
     
    Columns("BC:BD").Select 
    Selection.Copy 
    Columns("C:D").Select 
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ 
    :=False, Transpose:=False 
    Application.CutCopyMode = False 
    Selection.Copy 
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ 
    :=False, Transpose:=False 
    ActiveSheet.Paste 
    Application.CutCopyMode = False 
     
     
     
    Sheets("PayPalDownload").Select 
    Cells.Select 
    Selection.ClearContents 
    Sheets("PayPalDownloadSort").Select 
    Range("A1:AS1000").Select 
    Selection.AutoFilter 
    Selection.AutoFilter Field:=1, Criteria1:="" 
    Selection.Copy 
    Sheets("PayPalDownload").Select 
    ActiveSheet.Paste 
    Range("A1").Select 
     
     
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
And finally, this spits out all those text files.


	VB:
	
 
Sub MasterImportAndSort() 
     '
     ' MasterUpload Macro
     
     '
     
     '
     
     
    Application.DisplayAlerts = False 
     
    Sheets("OrderSummary").Visible = True 
    Sheets("OrderSummarySort").Visible = True 
    Sheets("OrdersSort").Visible = True 
    Sheets("Orders").Visible = True 
    Sheets("CorrectionsFESOLDDL").Visible = True 
    Sheets("Customers").Visible = True 
    Sheets("customerssort").Visible = True 
     
    Sheets("ECheckUpdate").Visible = True 
    Sheets("PayPalEcheckUpdateSort").Visible = True 
     
     
    Sheets("UpdateFeedBack").Visible = True 
    Sheets("FeedBackSort").Visible = True 
     
     
     
     
     
     
    Application.Run _ 
    "'In A New York Minute Processing.xls'!ImportingStep1OrdersSummary" 
    Application.Run _ 
    "'In A New York Minute Processing.xls'!ImportingStep2PayPalSort" 
    Application.Run _ 
    "'In A New York Minute Processing.xls'!ImportingStep3OrdersSort" 
    Application.Run _ 
    "'In A New York Minute Processing.xls'!ImportingStep4CustomersSort" 
    Application.Run _ 
    "'In A New York Minute Processing.xls'!ImportingStep5PPECheckUpdate" 
    Application.Run _ 
    "'In A New York Minute Processing.xls'!ImportingStep6UpdateItemStatusSort" 
     
     
     
     
     
    Sheets("PayPalDownload").Select 
    ChDir _ 
    "C:Documents and SettingsRony SosaMy DocumentsIncansunProcessingFilesAccessImportProcessing" 
    ActiveWorkbook.SaveAs Filename:= _ 
    "C:Documents and SettingsRony SosaMy DocumentsIncansunProcessingFilesAccessImportProcessingPayPalDownload.txt" _ 
    , FileFormat:=xlText, CreateBackup:=False 
    Sheets("Customers").Select 
    ActiveWorkbook.SaveAs Filename:= _ 
    "C:Documents and SettingsRony SosaMy DocumentsIncansunProcessingFilesAccessImportProcessingCustomers.txt" _ 
    , FileFormat:=xlText, CreateBackup:=False 
    Sheets("Orders").Select 
    ActiveWorkbook.SaveAs Filename:= _ 
    "C:Documents and SettingsRony SosaMy DocumentsIncansunProcessingFilesAccessImportProcessingOrders.txt" _ 
    , FileFormat:=xlText, CreateBackup:=False 
    Sheets("OrderSummary").Select 
    ActiveWorkbook.SaveAs Filename:= _ 
    "C:Documents and SettingsRony SosaMy DocumentsIncansunProcessingFilesAccessImportProcessingOrderSummary.txt" _ 
    , FileFormat:=xlText, CreateBackup:=False 
    Sheets("UpdateFeedBack").Select 
    ActiveWorkbook.SaveAs Filename:= _ 
    "C:Documents and SettingsRony SosaMy DocumentsIncansunProcessingFilesAccessImportProcessingUpdateFeedBack.txt" _ 
    , FileFormat:=xlText, CreateBackup:=False 
    Sheets("ECheckUpdate").Select 
    ActiveWorkbook.SaveAs Filename:= _ 
    "C:Documents and SettingsRony SosaMy DocumentsIncansunProcessingFilesAccessImportProcessingEcheckUpdate.txt" _ 
    , FileFormat:=xlText, CreateBackup:=False 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


I'm not sure why its still allowing for blank spaces in my text files. I have included a sample of the text file. If you download my text file and select all, you will see a lot of blank spaces.

Hi and thank you for your time.

I was trying to save the current open workbook as it's own name...I get it to work if I type in the workbook name...but was hoping to use this variable and can't get it to work...any ideas?


	VB:
	
 Name 
wb = ActiveWorkbook.Name 
 
ActiveWorkbook.SaveAs Filename:= _ 
"F:Documents and SettingsTimMy Documentswb.cen", FileFormat:=xlText, _ 
CreateBackup:=False 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Thank you again.

Tim

Hello:

I have the following code to convert files in my folder to text. I have about 100 excel files in the folder and it is only converting /recognizing the first 4 files in the folder.

Can you tell what is wrong with the code?

Thanks much


	VB:
	
 create_txt() 
     '
     ' create_txt Macro
    Dim fname As String 
    Dim last_position As Long 
    Dim name_l As Long, Newname_l As Long 
    Dim Newfname As String 
    With Application.FileSearch 
        .FileType = msoFileTypeExcelWorkbooks 
        .LookIn = "M:My Documents  " ' 0 Then 
            MsgBox "There were " & .FoundFiles.Count & _ 
            " file(s) found." 
             'Exit Sub
            For i = 1 To .FoundFiles.Count 
                On Error Goto NextFile 
                fname = .FoundFiles(i) 
                Workbooks.Open Filename:=fname 
                name_l = Len(fname) 
                last_position = InStrRev(.FoundFiles(i), "", -1, vbBinaryCompare) 
                If (last_position > 0) Then 
                    Newname_l = name_l - last_position - 4 
                    Newfname = Mid(fname, last_position + 1, Newname_l) 
                Else 
                    Newfname = fname 
                End If 
                 'MsgBox Newfname
                ActiveWorkbook.SaveAs Filename:=Newfname & ".txt", ConflictResolution:=xlLocalSessionChanges, _ 
                FileFormat:=xlText, CreateBackup:=False 
                ActiveWorkbook.Close 
                 
NextFile: 
                 
            Next i 
        Else 
            MsgBox "There were no files found." 
        End If 
    End With 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines



	VB:
	
 UNIONE_FILE_SENZA_BLANK_OK() 
     
    Dim FolderPath As String 
     
    Shell "cmd /c copy E:MACROFASCIANO*.txt E:MACROFASCIANOPROVA.txt" 
     
    FolderPath = "E:MACROFASCIANO" 
     
    Application.Workbooks.Open FolderPath & "PROVA.TXT" 
    ActiveWorkbook.Sheets(1).Range("A:A").Sort Key1:=ActiveWorkbook.Sheets(1).Range("A1"), Order1:=xlAscending 
    ActiveWorkbook.SaveAs Filename:=FolderPath & "PROVA001.txt", FileFormat:=xlText 
    ActiveWorkbook.Close False 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Is possible, after this macro, make a commend to delete the " in the txt file in the init position and and position of the lines...

THe file runs great on a local directory but after I moved it to the networked location I ran into some issues.

I get and error that:

Microsoft Excel cannot access the file 'H:Supply OperationsApheresis Network ManagementAph Monitor ReportsMonitoring ReportsReports200_22552-01_10252010.xls'. There are several possible reasons:
The file name or path does not exist
The file is being used by another program. The issue is that the file in question is not even on that drive, the reports folder is empty! I even looked for hidden files etc.

Is it possible that Excel is cacheing the filename?


	VB:
	
 
Sub LoopDirLoopFiles3a() 
    Dim wbOpen As Workbook 
    Dim wbMaster As Workbook 
    Dim strDirSub As String 
    Dim strExtension As String 
    Dim oFS As New FileSystemObject 
    Dim oDir 
    Dim strDir 
    Dim oSub 
    Dim lngRowT As Long 
    Dim lngRowB As Long 
    Dim lngRowN As Long 
    Const lngColT As Long = 254 
    Dim wsTarget As Worksheet 
    Dim wsBatch As Worksheet 
    Dim wsNote As Worksheet 
    Dim strMasterName As String 
    Dim strTargetName As String 
    Dim intX1 As Integer 
    Dim intX2 As Integer 
    Dim intMatch As Integer 
    Dim CopiedSht As Collection 
    Dim Sht 
    Call ClearData 
     'Comment out the 4 lines below to debug
    Application.DisplayAlerts = False 
     'Application.ScreenUpdating = False
     'Application.Calculation = xlCalculationManual
     'On Error Resume Next
    Set wbMaster = ThisWorkbook 
    Set wsBatch = wbMaster.Worksheets("Batch") 
    Set wsNote = wbMaster.Worksheets("Note") 
    strMasterName = wbMaster.Name 
    strTargetName = wsNote.Range("A6").Value 
    lngRowB = wsBatch.Cells(Rows.Count, 1).End(xlUp).Row 
    strDir = wbMaster.Path 
    ChDir strDir 
    lngRowN = 10 
    Set CopiedSht = New Collection 
    Do While Len(wsNote.Cells(lngRowN, 1).Value) > 0 
        CopiedSht.Add Item:=Cells(lngRowN, 1).Value 
        lngRowN = lngRowN + 1 
    Loop 
     'MsgBox CopiedSht.Count
    Set oDir = oFS.GetFolder(strDir) 
    For Each oSub In oDir.SubFolders 
         'MsgBox oSub.Path
        ChDir oSub.Path 
        strExtension = Dir("*.xls*") 
        strDirSub = oSub.Path & "" 
        Do While strExtension  "" 
            intMatch = 0 
            For intX1 = 1 To CopiedSht.Count 
                If strExtension = CopiedSht.Item(intX1) Then 
                    intMatch = 1 
                End If 
            Next intX1 
             
            If intMatch = 0 Then 
                [B]      Set wbOpen = Workbooks.Open(strDirSub & strExtension) 'ERROR ON THIS LINE[/B]
                 
                For intX2 = 1 To wbOpen.Worksheets.Count 
                    wbOpen.Worksheets(intX2).Visible = True 
                Next intX2 
                 
                Set wsTarget = wbOpen.Worksheets(strTargetName) 
                lngRowT = wsTarget.Cells(Rows.Count, 1).End(xlUp).Row 
                 
                If lngRowB = 1 And lngRowT > 0 Then 
                    wbOpen.Activate 
                    wsTarget.Select 
                    Range(Cells(1, 1), Cells(lngRowT, lngColT)).Copy 
                    wbMaster.Activate 
                     'wsBatch.Select
                    wsBatch.Cells(lngRowB, 1).PasteSpecial Paste:=xlPasteValues 
                    Application.CutCopyMode = False 
                End If 
                 
                If lngRowB > 1 And lngRowT > 0 Then 
                    wbOpen.Activate 
                    wsTarget.Select 
                    Range(Cells(2, 1), Cells(lngRowT, lngColT)).Copy 
                    wbMaster.Activate 
                     'wsBatch.Select
                    wsBatch.Cells(lngRowB + 1, 1).PasteSpecial Paste:=xlPasteValues 
                    Application.CutCopyMode = False 
                End If 
                 
                lngRowB = wsBatch.Cells(Rows.Count, 1).End(xlUp).Row 
                 
                 'wbMaster.Activate
                lngRowN = 10 
                Do While Len(wsNote.Cells(lngRowN, 1).Value) > 0 
                    lngRowN = lngRowN + 1 
                Loop 
                wsNote.Cells(lngRowN, 1).Value = strExtension 
                 
                With wbOpen 
                     '.Sheets(2).Copy after:=wbMaster.Sheets(wbMaster.Sheets.Count)
                    .Close SaveChanges:=False 
                End With 
            End If 
            strExtension = Dir 
        Loop 
    Next oSub 
     'MsgBox wbMaster.Name
    wsBatch.Select 
    Cells(1, 1).Select 
    wbMaster.Save 
    wbMaster.SaveAs Filename:=strDir & "Master.txt", FileFormat:=xlText 
    Workbooks.Open (strDir & "" & strMasterName) 
    Windows(strMasterName).Activate 
    Worksheets(1).Select 
    Set wbMaster = Nothing 
    Set wbOpen = Nothing 
    Set wsBatch = Nothing 
    Set wsNote = Nothing 
    Set wsTarget = Nothing 
    Set CopiedSht = Nothing 
    Windows("Master.txt").Close 
    Application.DisplayAlerts = True 
    Application.ScreenUpdating = True 
    Application.Calculation = xlCalculationAutomatic 
    On Error Goto 0 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


I'm trying to create a sub that will save my worksheet to a tab delimited text file anytime there is a change in the worsheet data (all cells are linked to cells in other workbooks). I've figured out the command to save the file


	VB:
	
ActiveWorkbook.SaveAs Filename:= _ 
"C:Documents and SettingsChrisMy DocumentsBook1.txt", FileFormat:=xlText _ 
, CreateBackup:=False 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
but I'm not sure how to get a sub routine to start running when the file opens and to have it run continuously while open. I've found the command:


	VB:
	
Application.Volatile 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
that will flag when any cells in my range are recomputed and run a function, but functions don't allow me to save the file. Any ideas would be greatly appreciated!

Cheers
Chris

Hi

I'm a first time poster here, it's a great forum for newbees like me here although most of it just flies over my head... any way here is my problem.

I have recorded the following macro, it copies and paste special value the "upload" worksheet and then deletes the rest of the tab and saves the file as a text file.

Now how do I instruct in VB to open all the files in this directory and do the same.

Thanks in advance.


	VB:
	
Sheets("Upload").Select 
Cells.Select 
Selection.Copy 
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ 
:=False, Transpose:=False 
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst 
Sheets(Array("Instruct", "Cost Center", "CF004 ", "Smoothing", "Cnslt Bud", "Load06" _ 
, "Load07", "Feed", "Assume")).Select 
Sheets("Instruct").Activate 
 
Sheets(Array("Instruct", "Cost Center", "CF004 ", "Smoothing", "Cnslt Bud", "Load06" _ 
, "Load07", "Feed", "Assume", "FY06", "FY07", "SumByAcct", "SumP&L", "Upload 06")). _ 
Select 
Application.CutCopyMode = False 
ActiveWindow.SelectedSheets.Delete 
ChDir _ 
"S:AcctgCGITBudgetFY 2007Cost Center BudgetsUploadTestUploadTestUpload1" 
ActiveWorkbook.SaveAs Filename:= _ 
"S:AcctgCGITBudgetFY 2007Cost Center BudgetsUploadTestUploadTestUpload17705.txt" _ 
, FileFormat:=xlText, CreateBackup:=False 
 
ActiveWorkbook.Save 
ActiveWorkbook.Close 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


I am having two issues that are somewhat related.

I have a worksheet that I have a macro to save to a text delimited file. what happens is when i run it the first time it renames that worksheet to that saved file name so that the next time i want to run the macro, the worksheet is now named something different.
also when it does that the "whole workbook" becomes that text file, and i have to then manually go back to save as, and search for my workbook and save it again as that to go back to using the .xls file.

So basically that one macro is changing the worksheet name so that the macro no longer works, and also changing my workbook to the text file instead of staying the xls.


	VB:
	
 
ChDir ("C:Documents and Settings" & GetUserID & "desktop") 
ActiveWorkbook.SaveAs Filename:="C:Documents and Settings" & GetUserID & "desktoppartscount" & Format(Date, "mm-dd-yy"),
FileFormat:=xlText, CreateBackup:=False 
 
Sheets("partscount").Select 
Cells.Select 
Selection.Delete Shift:=xlUp 
Range("A1").Select 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
the "partscount" sheet is the sheet that when saved to the text file is partscount(&date).txt so the next step does not find "partscount" sheet

as well as my workbook referances "partscount" about 4 times before the save so next time i run the macro it cannot find partscount of course.

Hello,
I am looping a series of formatting commands (successfully) and then attempting to save the resulting worksheets as .txt files (unsuccessfully). The following code returns an error (Runtime error 1004) that I haven't been able to resolve:


	VB:
	
 ActiveWorkbook 
    .SaveAs Filename:="D:User_FoldersLong_TermChangKoreaWQWQStat" & strName & ", FileFormat:=xlText" 
     'strName I have defined earlier in the code.  It did not cause me any problems when saving the sheet in the .xls format
    Application.DisplayAlerts = True 
    .Saved = True 
    .Close 
End With 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
The following code also did not work:

	VB:
	
ActiveWorkbook.SaveAs Filename:= _ 
"D:User_FoldersLong_TermChangKoreaWQWQStat" & strName & ".txt", FileFormat:=xlText 
ActiveWorkbook.Save 
ActiveWindow.Close 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
But again, it did work with the appropriate changes to save the sheet in .xls format.

Any suggestions?

Thanks,
Regards,
mboeder

How can I bypass or select the appropriate choice for a prompt automatically in VBA. For example, the code below belongs to a procedure that saves a workbook as a text file:

Workbooks(File_Name).SaveAs Filename:= XYZ, FileFormat:= xlText, _
CreateBackup:= False
Workbooks(XYZ).Close

The code itself is fine, but whenever it's run, Excel will prompt me with "The file XYZ has changed. Do you want to save changes". Is there a procedure for either ignoring the prompt and close the file directly, or one that chooses "Yes" or "No" without the prompt popping out every time I run the code. Thanks.

E

I have a macro that sorts through, concatenates and so on.

The contents of this file changes every week and I have to create a tab delimited file from it. I have my current SaveAs snippet below, but unless there is already file there for it to overwrite it displays a dialog box stating that the file cannot be accessed, that it may be read only...

Additionally I need the file name to contain the current date (mmddyyyy).
Ex: 06102004XML.txt

I have been racking my brain on this for two days looking for the answer from other posts. I have seen many very similar, but I still don't have it.

ActiveWorkbook.SaveAs Filename:= _
"C:Documents and SettingsjdDesktopMen" & (Date) & "Doc_Write_FileCodeEx.txt", FileFormat:=xlText, CreateBackup:=False

Any help would be greatly appreciated.

Hi!

I am learning VBA and have a Q for the forum. When using this code

Sub SaveFileAs()
Dim stFpath As String
Dim stFname As String
'Folder to store file
stFpath = "C:JBStat"
'Name of workbook
stFname = Range("B1").Value & ".txt"
ActiveWorkbook.SaveAs stFpath & stFname, FileFormat:=xlText, CreateBackup:=False
End Sub

Really simple as you can see...I get a different formating inside the file (the dates in column two gets 5/14/2004 instead of (the way I want) 2004-05-14 as I get when I use the macro recorder and get this code.

Sub Makro2()

ActiveWorkbook.SaveAs Filename:="C:JBStat20040514.txt", FileFormat:= _
xlText, CreateBackup:=False
End Sub

Examples of the outcome of the different macros.

Sub SaveFileAs() 1 5/14/2004 10:26 1 91-0010309-8

Sub Makro2()1 2004-05-14 10:26 1 91-0010309-8

BTW I use XP and Swedish version of XL 2003.

Any formatting experts out there? TIA

Have a great weekend all! /Mats

I am trying to export one sheet of my excel workbook as a text file with a name I have specified to a specific folder. Then I need to save the entire workbook as an Excel File but I want to be able to have the user choose the path via a dialog box. Below is the code so far. The problem I am facing is that the workbook still thinks it is a text file. What am I doing wrong? Again, this is what I want:
1. Save one sheet as a text file to a specific path (without user choosing)
2. Save the same workbook as an Excel File, so it will not think it is a text file.
3. Let the user decide where .xls file will be saved. Thanks a lot
-Todd

(note: missing slashes)
P.S. How do I make slashes appear in my posts?

Sub Export()
name = Sheets(1).Range("C16")
Sheets(11).Select
ChDir "C:XXXXXXXX"
ActiveWorkbook.SaveAs Filename:="C:XXXXXXXX" & name & ".txt", FileFormat:=xlText, CreateBackup:=False
ActiveWorkbook.Application.GetSaveAsFilename InitailFilename:="C:" & name, FileFilter:="Microsoft Office Excel Workbook (*.xls), *.xls"
End Sub

Hi all,

I've been trying to get some code to work for a number of days now and I've tried everything. I feel this should be very simple, yet I cannot get past it. I'm launching Excel from a VB module and doing a bunch of clean up so that I can later import it into my Access Database. I've trimmed down my code to a very simple Sub to eliminate as many variables as possible. The problem is I get:

Run-Time Error '91':
Object variable or With Block variable not set

When the code reaches this line:
If ActiveCell.Value > 0 Then

To make it more puzzling, sometimes I run it and it works (when I open the DB and run it for the first time) and some times it does not.


	VB:
	
 Active_Cell_Problem() 
     
    Path = "C:documents and settings" 
    MyName = Environ("username") & "My Documents" 
     
    Dim db As Database 
     
    Dim xApp As Excel.Application 
    Dim wb As Excel.Workbook 
    Dim ws As Excel.Worksheet 
     
    Set db = CurrentDb 
    Set xApp = New Excel.Application 
     
    xApp.Visible = True 
    xApp.DisplayAlerts = False 
     
    MyFile = Path & MyName & "ABC OH Inv.xls" 
    Set wb = xApp.Workbooks.Open(MyFile) 
     
    Set ws = wb.Worksheets("Sheet1") 
     
    ws.Range("a5").Select 
     
    If ActiveCell.Value > 0 Then 
        MsgBox ActiveCell.Value 
    Else 
        MsgBox ActiveCell.Value 
    End If 
     
    wb.SaveAs FileName:=Path & MyName & "ABC OH Inv.txt", FileFormat:=xlText, CreateBackup:=False 
    wb.Close 
     
    xApp.Application.Quit 
     
    Set ws = Nothing 
    Set wb = Nothing 
    Set db = Nothing 
    Set xApp = Nothing 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Simply trying to copy data from one worksheet to another. The source sheet is an excel file exported from an Access table. I recorded the macro using the recorder in Excel because I am no programmer, but when I try to run the code, I get this error:

"Code execution has been interrupted" ...and the Range I tried to select in the source file is highlighted by the Editor.

Is the source file protected somehow? My code:
______________________________________________________________________________


	VB:
	
 Workbook_Open() 
     
     
     
    ChDir "P:Databasesdownloads" 
    Workbooks.Open Filename:="P:Databasesdownloadsheadersflat.xls", Origin:= _ 
    xlWindows 
    [COLOR="Red"]Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select[/COLOR] 
    Selection.Copy 
    Windows("ExcelTool.xls").Activate 
    Range("A1").Select 
    ActiveSheet.Paste 
    Rows("1:1").Select 
    Application.CutCopyMode = False 
    Selection.Delete Shift:=xlUp 
    Range("A2").Select 
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select 
    Selection.Delete Shift:=xlUp 
    Range("A2").Select 
    Workbooks.Open Filename:="P:Databasesdownloadsactivityflattened.xls" 
    Range("A2").Select 
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select 
    Selection.Copy 
    Windows("ExcelTool.xls").Activate 
    Range("A2").Select 
    ActiveSheet.Paste 
     
    Windows("headersflat.xls").Activate 
    ActiveWorkbook.Close 
     
    Windows("activityflattened.xls").Activate 
    ActiveWorkbook.Close 
     
     
    ChDir "P:Databasesdownloads" 
    ActiveWorkbook.SaveAs Filename:="P:Databasesdownloadsactsheaders.txt", _ 
    FileFormat:=xlText, CreateBackup:=False 
     
    Application.Quit 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Want to do something *almost* exactly like a previous post but that post is now closed.
http://www.ozgrid.com/forum/showthread.php?t=70097

I want to extract data from individual worksheets from a workbook in excel to individual text files with the **same name as the original excel file** plus incrementing suffix.

Example: File_Name.xls with worksheets: Sheet1, Sheet2, Sheet3
==> Result...... File_Name_01.txt, File_Name_02.txt, File_Name_03.txt

Please don't delete - this is not a duplicate post.
Previous code 1) creates text files with "sheet name" as was the original post's intent (not "file name") and 2) it does not iterate through each sheet (only processes Sheet1 regardless of selected sheet in the workbook)

It works great for what it was intending to do. Thanks for the head start xlite!!

Here is that code again for reference:

	VB:
	
 wsToText() 
    Dim ws As Worksheet 
     
    Application. ScreenUpdating = False 
    Application.DisplayAlerts = False 
    For Each ws In  ThisWorkbook.Worksheets 
         
        Sheets(ws. Name).Select 
        Sheets(ws.Name).Copy 
        ActiveWorkbook. SaveAs Filename:= _ 
        "C:Documents and SettingsHP_AdministratorMy DocumentsMy Excel" & ws.Name & ".txt", _ 
        FileFormat:=xlText, CreateBackup:=False 
        ActiveWorkbook.Close 
        ThisWorkbook.Activate 
    Next 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Thanks!

Hi,

I'm trying to create a excel sheet with macros that will generate a tab delimitd file which is a journal voucher and I want to upload that .txt file to our financial system (SAP). When I create a tab delimited file manually, that is I enter all the values in excel and save as .txt it works great. But I have created a spreadsheet where the useres (all employees on the financial departement) enter the info required. I have a macro validating that all fields are correct and so on.. When they are finished they sedt the spreadsheet to me (an excelfile) via email, this is done by a submit buttom(macro) and the I have a hidden sheet in the worksheet which gets all the values that are necessery and then I create my tab delimited file using the well known save as fileformat=xltext macro. But SAP will not accept this file. I get error messages as convertion erros in line 4 column 6 and so on. The sheet which is saved as .txt is 100% with formulas, it gets all the value from another sheet(sheet1). I use the formula =IF(Sheet1!$E17=0;"";Sheet1!E17) in the cells with E17 changing to whatever cell is required. What can be wrong here? May it be that all the formulas (columns A to H and row 2 to 1001) somehow mix it all up and even to the cell is empty is the formula is somehow hidden in the .txt file and that messes it all up?? Please help me. If my explanation is diffult to understand please let me know and I'll try ro explain.

Thanks

Hi there,

I've go a shared Workbook, which will be distributed among several users and stored in different places. The workbook uses the following code:


	VB:
	
 
Sub savemeas() 
     
    Worksheets("data").Visible = True 
     
    Sheets("data").Copy 
    Application.DisplayAlerts = False 
    ActiveWorkbook.SaveAs Filename:="d:" & Range("a1").Value, _ 
    FileFormat:=xlText, CreateBackup:=False 
    Application.DisplayAlerts = True 
    ActiveWorkbook.Close SaveChanges:=True 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

to save the sheet "data" as a text file with a name based on the value of cell a1. All I need is to modify the code so that the target path would not be


	VB:
	
 
ActiveWorkbook.SaveAs Filename:="d:" & Range("a1").Value 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
but be the same as the source Workbook's - so that I wouldn't have to modify the code for each user separatly, because the sheet would alwayas be saved in the same folder as the current path of the source workbook.

Any ideas how to this?

Thanks for your help.

Regards

Luke

Hello all,

I've been searching all morning through various posts on this subject, but I can't find anything that I can adapt to my needs. Here is the code I have:


	VB:
	
ChDir "C:Documents and SettingsmynameDesktop" 
ActiveWorkbook.SaveAs Filename:= _ 
"C:Documents and SettingsmynameDesktopBook1.txt", FileFormat:=xlText, _ 
CreateBackup:=False 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
This file will be used accross multiple computers and therefore the directory will always change. What I need to hapen, is for this code to save the workbook in the same directory as the origional file. I also want the file name to refer to a cells value, as I will be having the user define the name through a userform. Any help is always appreciated

thanks

Hi,

am using excel to go to a web site and download a series of pages

www.abc.com/1
www.abc.com/2
etc

However, if the requested page doesn't exist I just want excel to ignore that and carry on.

So I have...


	VB:
	
 100000 
    Workbooks.Add 
     
     
    mtch = f 
    gp = Left(f, 2) 
    With ActiveSheet.QueryTables.Add(Connection:= _ 
        "URL;http://www.cricketarchive.com/Archive/Scorecards/" & gp & "/" & mtch & ".html", _ 
        Destination:=Range("A1")) 
        .Name = f 
        .FieldNames = True 
        .RowNumbers = False 
        .FillAdjacentFormulas = False 
        .PreserveFormatting = True 
        .RefreshOnFileOpen = False 
        .BackgroundQuery = True 
        .RefreshStyle = xlInsertDeleteCells 
        .SavePassword = False 
        .SaveData = True 
        .AdjustColumnWidth = True 
        .RefreshPeriod = 0 
        .WebSelectionType = xlAllTables 
        .WebFormatting = xlWebFormattingNone 
        .WebPreFormattedTextToColumns = True 
        .WebConsecutiveDelimitersAsOne = True 
        .WebSingleBlockTextImport = False 
        .WebDisableDateRecognition = False 
        Application.Wait 2000 
        .Refresh BackgroundQuery:=False 
    End With 
     
     
    ChDir "C:JohnCricketArchiveAll" 
    flnme = f & " " & Cells(6, 1).value & ".xls" 
    ActiveWorkbook.SaveAs Filename:=flnme, _ 
    FileFormat:=xlText, CreateBackup:=False 
Err: 
    ActiveWindow.Close (False) 
Next 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
but excel still returns a message box saying it couldn't open the requested page when i just want it to skip to close workbook and loop. i have to hit 'debug' and move next line to where i want.

Any thoughts?

Cheers

Hi

This piece of code saves a sheet:


	VB:
	
Sheets("Sheet1").SaveAs Filename:= _ 
"C:TEMPSheet1.txt", _ 
FileFormat:=xlText, Password:="", WriteResPassword:="", _ 
ReadOnlyRecommended:=False, CreateBackup:=False 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
but it also renames automatically the workbook (the new name is "Sheet1").
How to save a sheet without renaming the workbook?