Saving to Excel in background

[terrypin]

Before I get going seriously on this macro I'd appreciate knowing whether

- it's impossible?

- It's possibe and someone's already done something similar and happy to share code?

I have a macro (with several submacros) working in a couple of mapping applications, operating on walks/hikes/trips etc, call them all 'tracks'. For each track I capture about 50 text strings. I could get each to the clipboard immediately after they are captured. Or I could generate a list as I proceed and save a file (TXT or CSV) at the end of all the captures.

I want to get them all into 50 specified (not consecutive) cells of a single Excel worksheet.

If I choose the 'copy one at a time' method, I'd have to repeatedly return to my macro after pasting each one into Excel.

If instead I go for the 'accumulate in text file' method I'd have to switch to Excel, import the text file and paste each of its 50 entries into their corresponding cells.

My preference is for the first method. 1. it's less extra code to add to my macro and 2. if I make a mistake in the capturing at some point, I don't lose everything.

But is that possible with Excel staying in the background, not flashing up 50 times, using Controls?

 

Terry, East Grinstead, UK

[Cory]

I don't have an answer. I started using simple .NET application using Interop for this a long time ago and have never looked back. So simple and efficient. If you would like me to make you a simple application like this and show you how it works, I can. But one thing I was thinking about but have never tried is simply linking to a CSV in Excel? Excel is great at linking to databases and all sorts of data sources. They simply refresh their data on command or on schedule. It might not work for what you need however. 

[terrypin]

Thanks Cory, appreciate the kind offer. But I already have a longish list of 'Must learn..' topics on the back burner and can't  risk adding .NET to it!

[terrypin]

I've ruled out using MX Pro Controls in Excel (Excel 365 here). I've tested using Get Control and Capture Control. The simple test below is typical. No matter which option of Capture Control  I choose, it captures only the name of the workbook. Ignores screen co-ordinates whether specified or using 'Beneath mouse', sheet - everything. No way to capture cell (address or content), worksheet name, etc.

// Testing Controls in Excel.
Capture Control from Specific Coordinates into %cCellA1% // Trackdate
Get Control Text from %cCellA1% into %tCell%
Text Box Display:  // Display cCellA1 and tCell

My grunt method below works reliably, but at about 1 sec per cell it's glacially slow.

// This pastes a variable (here a fixed string) into cell B1 of blank spreadsheet
**********************************************
Text Type (Simulate Keystrokes): <ALT>eg // Open the 'Go To' dialog
Text Type (Simulate Keystrokes): b1 // Select target cell
Text Type (Simulate Keystrokes): <ENTER> // Confirm
Text Type (Use Clipboard and Paste Text): abc defgh ijkl mnopqrst uvwxyz // Paste the clipboard contents
Text Type (Simulate Keystrokes): <ENTER> // Apply

 

 

 

[acantor]

A VBA macro might be the ticket. Try recording the procedure using Excel's macro recorder, and then modify the code to create a more general solution. The folks on various Excel VBA help forums might be able to provide assistance.

[terrypin]

Thanks Alan, I've been exploring that, and have posted to the Excel forum. In those few cases where I've been able to use a VBA macro, it's been enormously faster, so I certainly have motivation. But my preferred approach of pasting directly after every capture is proving difficult to implement with a VBA macro. Every paste goes to a unique cell. It also means 50 switches to and from my Excel workbook. So I think I'm going to focus on the text file method.

Wish I could put my current project on hold while I move up the VBA learning curve a bit, instead of being stuck mainly in copy/paste plus minor edits mode!

[rberq]

I have a macro to do a similar thing, but on a smaller scale -- a dozen or so values rather than your 50.  Rather than use a text file, I copy the whole screen to the clipboard, extract the fields I want into ME variables, switch once to Excel, Ctrl-Home to the first cell, and type arrow keys to get to the desired cells to type the variables.  It is very fast and reliable, but it doesn't do Excel in the background like you would prefer.  The logic to make sure there are no missing fields is in the extraction macro.   

[terrypin]

Thanks rberg. Is the assigning to variables, from the screen copy, manual or automated?

[rberq]

Automated.  The macro scans the clipboard text for identifiers, then finds a value after the third subsequent dollar sign for each identifier. 

[terrypin]

Pleased to report a successful solution. I chose the text file method for my MX Pro macro, which was tedious but simple to write. Here's the first part:

 

// Set up
*******
Variable Set to ASCII Char 13 to %CR%
Variable Set to ASCII Char 10 to %LF%
Variable Set to ASCII Char 9 to %TAB%
Variable Set String %CRLF% to "%CR%%LF%"
 
// Enable next command only when using a pre-prepared test set
****************************************************
Variable Restore: Restore All Variables
Text Box Display: Variables reported in  'GPX track - Display captured variables'
 
// Get all variables into a text file as a list
*********************************
// GPX filename =tTrackname.gpx
Variable Modify String %tAllVariables%: Append Text (GPX file name)
Variable Modify String %tAllVariables%: Append Text String Variable (%TAB%)
Variable Modify String %tAllVariables%: Append Text String Variable (%tTrackname%)
Variable Modify String %tAllVariables%: Append Text (.gpx)
Variable Modify String %tAllVariables%: Append Text String Variable (%CRLF%)
 
// tReportDate
Variable Modify String %tAllVariables%: Append Text (ReportDate)
Variable Modify String %tAllVariables%: Append Text String Variable (%TAB%)
Variable Modify String %tAllVariables%: Append Text String Variable (%tReportDate%)
Variable Modify String %tAllVariables%: Append Text String Variable (%CRLF%)
 
// Track Descr'n = tTrackDescr
Variable Modify String %tAllVariables%: Append Text (Track Descr'n)
Variable Modify String %tAllVariables%: Append Text String Variable (%TAB%)
Variable Modify String %tAllVariables%: Append Text String Variable (%tTrackDescr%)
Variable Modify String %tAllVariables%: Append Text String Variable (%CRLF%)
.
.
.
 
// Profile Link GE (DEM) = tProfileLinkGE(DEM)
Variable Modify String %tAllVariables%: Append Text (Profile Link GE(DEM))
Variable Modify String %tAllVariables%: Append Text String Variable (%TAB%)
Variable Modify String %tAllVariables%: Append Text String Variable (%tProfileLinkGE(DEM)%)
Variable Modify String %tAllVariables%: Append Text String Variable (%CRLF%)
 
// Now save that list as a text file
************************************
 
Variable Modify String: Save %tAllVariables% to "C:\Users\terry\Dropbox\Excel+VBA (Sundry)\TEMP-VariableList.txt"
 
// The reason for postponing the remaining entries for MM and Best Estimates (and any others) is to minimuse risk of havng to start afresh in the event of some problem or crash during those final stages.
// Now switch to Excel and paste the entries from the list
**********************************************
Macro Run: Open TrackData-New.xlsm
Macro Run: VBA - ImportFile_1

The VBA macro proved easier than I'd thought. Tedious (50 copy/pastes, mainly to non-contiguous cells) but worthwhile as running it took 5 secs, compared with about 60 for the previous MX macro.