Makro should save a textfile on desktop...

K

kalel09

Hi!
I have an excel makro which saves a text file from an marked column.
The Excel File is on a server in the network and so the makro saves
the text file in the same folder where the excel file is (on the
network server)

BUT i want the text file on MY local macintosh Desktop.
4 other Mac Users which read and write in the excel file want the
same.
When User "A" saves the text file, the file should appear on HIS local
mac desktop.
The same with user "B" and so on....

How can i make this?

Here is the script:
Option Explicit

Public Sub Datensatz_schreiben()
Dim strDatensatz1 As String
Dim strDatensatz As String
Dim intDatNum As Integer
Dim intSpalte As Integer
Dim strDatei As String
intSpalte = Cells(ActiveCell.Row,
Columns.Count).End(xlToLeft).Column
strDatei = Cells(ActiveCell.Row, 1) & "_" & Cells(ActiveCell.Row,
2) & "_" & Cells(ActiveCell.Row, 3) & "_" & Cells(ActiveCell.Row, 4) &
".txt"
intDatNum = FreeFile()
Open strDatei For Output Access Write As #intDatNum
strDatensatz1 = Cells(2, 1).Text
strDatensatz = Cells(ActiveCell.Row, 1).Text
For intSpalte = 2 To intSpalte
strDatensatz1 = strDatensatz1 & vbTab & Cells(2,
intSpalte).Text
strDatensatz = strDatensatz & vbTab & Cells(ActiveCell.Row,
intSpalte).Text
Next intSpalte
Print #intDatNum, strDatensatz1
Print #intDatNum, strDatensatz
Close #intDatNum
End Sub
 
B

Bob Greenblatt

Hi!
I have an excel makro which saves a text file from an marked column.
The Excel File is on a server in the network and so the makro saves
the text file in the same folder where the excel file is (on the
network server)

BUT i want the text file on MY local macintosh Desktop.
4 other Mac Users which read and write in the excel file want the
same.
When User "A" saves the text file, the file should appear on HIS local
mac desktop.
The same with user "B" and so on....

How can i make this?

Here is the script:
Option Explicit

Public Sub Datensatz_schreiben()
Dim strDatensatz1 As String
Dim strDatensatz As String
Dim intDatNum As Integer
Dim intSpalte As Integer
Dim strDatei As String
intSpalte = Cells(ActiveCell.Row,
Columns.Count).End(xlToLeft).Column
strDatei = Cells(ActiveCell.Row, 1) & "_" & Cells(ActiveCell.Row,
2) & "_" & Cells(ActiveCell.Row, 3) & "_" & Cells(ActiveCell.Row, 4) &
".txt"
intDatNum = FreeFile()
Open strDatei For Output Access Write As #intDatNum
strDatensatz1 = Cells(2, 1).Text
strDatensatz = Cells(ActiveCell.Row, 1).Text
For intSpalte = 2 To intSpalte
strDatensatz1 = strDatensatz1 & vbTab & Cells(2,
intSpalte).Text
strDatensatz = strDatensatz & vbTab & Cells(ActiveCell.Row,
intSpalte).Text
Next intSpalte
Print #intDatNum, strDatensatz1
Print #intDatNum, strDatensatz
Close #intDatNum
End Sub
Click to expand...
Why don't you just copy the column(s) you want into a blank worksheet, and
then save that sheet as text? The code is a lot simpler, and yu can specify
the file location easily.
 
B

Bob Greenblatt

Hi!

Yes, sure, but we want a good comfortable script :))
Click to expand...
Fine. But my suggestion is then the way to go. The code will be much shorter
and easier to understand.
 
K

kalel09

Well, i want a fast and easy workflow. So when i have to copy my rows
in an other sheet it won´t be comfortable.
An other problem is, the text file should have:
in the first row always the same text like Number - Date - Company -
Color and so on.....
in the second row i want to text which i copy.

Example for text file: (tab separated)
Number Date Company Color
1223 11.11.2007 XXX Blue

My Script works fine, but the only problem we have is the location
where the textfile appears.
It should not be on the network server, it should be on the users mac
desktop!
 
B

Bob Greenblatt

Well, i want a fast and easy workflow. So when i have to copy my rows
in an other sheet it won¥t be comfortable.
An other problem is, the text file should have:
in the first row always the same text like Number - Date - Company -
Color and so on.....
in the second row i want to text which i copy.

Example for text file: (tab separated)
Number Date Company Color
1223 11.11.2007 XXX Blue

My Script works fine, but the only problem we have is the location
where the textfile appears.
It should not be on the network server, it should be on the users mac
desktop!
Click to expand...
Then use the Dir function to set the default directory.
 
K

kalel09

Hi!
I am sorry, but i do not have any idea what is to do. .-)
Myself can´t write any makros.
My makro i have from an other excel forum, but they do not have any
idea for my MAC Problem.
 
B

Bob Greenblatt

Hi!
I am sorry, but i do not have any idea what is to do. .-)
Myself can¥t write any makros.
My makro i have from an other excel forum, but they do not have any
idea for my MAC Problem.
Click to expand...
It's difficult to help if you do not know how to write macro code. I have
modified your code to ask the user for a place to write the file. Have the
user select ANY file in the folder where you want your output. This file
name will be ignored. See if this works. Be careful about how the lines get
wrapped.

Public Sub Datensatz_schreiben()
Dim strDatensatz1 As String
Dim strDatensatz As String
Dim intDatNum As Integer
Dim intSpalte As Integer
Dim strDatei As String
Dim sPath As String
Dim i As Integer
sPath = Application.GetOpenFilename
If sPath = "false" Then Exit Sub
For i = Len(sPath) To 1 Step -1
If Mid(sPath, i, 1) = Application.PathSeparator Then Exit For
Next
sPath = Left(sPath, i)
intSpalte = Cells(ActiveCell.Row, Columns.Count).End(xlToLeft).Column
strDatei = Cells(ActiveCell.Row, 1) & "_" & Cells(ActiveCell.Row, 2) &
"_" & Cells(ActiveCell.Row, 3) & "_" & Cells(ActiveCell.Row, 4) & ".txt"
strDatei = sPath & strDatei
intDatNum = FreeFile()
Open strDatei For Output Access Write As #intDatNum
strDatensatz1 = Cells(2, 1).Text
strDatensatz = Cells(ActiveCell.Row, 1).Text
For intSpalte = 2 To intSpalte
strDatensatz1 = strDatensatz1 & vbTab & Cells(2, intSpalte).Text
strDatensatz = strDatensatz & vbTab & Cells(ActiveCell.Row,
intSpalte).Text
Next intSpalte
Print #intDatNum, strDatensatz1
Print #intDatNum, strDatensatz
Close #intDatNum
End Sub
 
K

kalel09

Hi Bob!

Thank you Bob for your help. The makro runs.
But won´t it better when i get a save-dialog, and i can save the .txt.
file directly on my desktop?
In your script i have to selct a .txt file in a folder on my desktop
to save my .txt file, thats a freaky way. :)

Your idea for a dialog is perfect, but can you change it for a normal
"save dialog" ?

thank you very much to spare no effort for me.
 
B

Bob Greenblatt

Hi Bob!

Thank you Bob for your help. The makro runs.
But won¥t it better when i get a save-dialog, and i can save the .txt.
file directly on my desktop?
In your script i have to selct a .txt file in a folder on my desktop
to save my .txt file, thats a freaky way. :)

Your idea for a dialog is perfect, but can you change it for a normal
"save dialog" ?

thank you very much to spare no effort for me.
Click to expand...
I did it hat way in case you wanted to save the file someplace else.
Unfortunately, excel requires, in this case, that a file be selected. If
only one person is using the macro, then the desktop (or any other location)
can be hard coded instead of using the dialog. It is also not easy to
identify the desktop. Is it called "desktop" in German? What appears in the
dialog?

You can change GetSaveAsFileName to GetOpenFileName.
 
K

kalel09

Hi Bob!

With "GetSaveAsFileName" it runs very good. Thank you very much.

In German the desktop is called "Schreibtisch".

Have a lot of thanks, i think your script is a good alternative for my
old one.

I think for a completly autosave .txt file on users desktop it´s a
very difficult and long script.

Thanks
best
Gerry
 
K

kalel09

Hi again! :)

One thing is funny in the save dialog.
When it appears, in the textfield on the top is the filename from the
excel file + ".xls" ! (Fileformat: "xls"
When i do nothing and click only on "save", the script saves a
corectly ".txt" file with the correct name.
That´s funny. ;-)

Gerry
 
B

Bob Greenblatt

Hi again! :)

One thing is funny in the save dialog.
When it appears, in the textfield on the top is the filename from the
excel file + ".xls" ! (Fileformat: "xls"
When i do nothing and click only on "save", the script saves a
corectly ".txt" file with the correct name.
That¥s funny. ;-)

Gerry
Click to expand...
The script saves it correctly because I remove the original file name and
replace it with the file name you wanted.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Word macro that can call cells out of Excel for a find and replace 0
Help needed! Extracting Heading 3 and Contents from a Word Doc to Excel 0
Documents Not Appearing (as it used to) On Desktop ? [on a Mac] 0
VBA Export to PDF 0
Code to Concat a row in loop 2
Making a macro only work on certain files 2
Extract Text in braquet and list of terms from 2 Docs 3
Makro 0

Top