0

I have an order form in excel that needs to be exported to a tab delineated text file to be uploaded to our suppliers web page.

The excel workbook has three pages for three corresponding days, and we need to export only two columns of data from a single page at a time.

Currently we are using a convoluted method involving a hyperlink which exports the data to access, then saves it as another excel file, and then we manually convert over to the tab delineated text file.

Most of my coworkers are very tech illiterate, and many of them have issues with the multiple steps involved.

I am looking for a visual basic method to replace the multiple steps with a single button on each page that exports the data to the proper format, adding a step that opens a hyperlink to the suppliers webpage - one step click and done.

I have extremely limited experience with VB, and would appreciate some help.

This is what I have so far, but it keeps erroring out on the paste step:

Range("M1:N300").Select
Selection.Copy
Workbooks.Open Filename:="C:\Users\name\Desktop\upload.xlsx"
Windows("upload.xlsx").Activate
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
Application.CutCopyMode = True
ActiveWorkbook.Save
ActiveWorkbook.SaveAs Filename:="C:\Users\name\Desktop\upload.txt", _
    FileFormat:=xlText, CreateBackup:=False
Windows("1302 Supplier Order Form.xlsm").Activate
Range("A3").Select
Workbooks("upload.xlsx").Close SaveChanges:=True
Jeremy
  • 11
  • 3
  • Does this answer your question? [VBA Saving single sheet as CSV (not whole workbook)](https://stackoverflow.com/questions/34155718/vba-saving-single-sheet-as-csv-not-whole-workbook) – braX Aug 25 '21 at 00:40
  • It is only two columns of data on a single worksheet in a file, and it is not being exported as a csv file but a text file. – Jeremy Aug 25 '21 at 00:54
  • A CSV file is a text file. You can specify to use a comma or a tab. – braX Aug 25 '21 at 00:54
  • it has to be explicitly a .txt file. – Jeremy Aug 25 '21 at 01:50
  • Then you just specify that in the `SaveAs` arguments. – braX Aug 25 '21 at 01:52
  • `ActiveWorkbook.SaveAs Filename:="C:\Test\Book1.txt", FileFormat:=xlText` – braX Aug 25 '21 at 02:14
  • To do only 2 columns, have the macro copy the worksheet to a new worksheet, delete the columns you don't want, then save the active workbook using that code, then delete that temporary sheet you created. – braX Aug 25 '21 at 02:21

0 Answers0